MySQL数据库TIMESTAMP默认值设置应用实例详解
之前我们介绍了：MySQL数据库TIMESTAMP设置默认值的灵活运用，本次我们接着上次的内容介绍几个MySQL数据库TIMESTAMP设置默认值的几个应用实例，希望能够对您有所帮助。
#1查看表定义，看到的是b列有个属性ON UPDATE CURRENT_TIMESTAMP，导致更新数据时，即便未涉及到该列，该列数据也被自动更新。另一方面，c列默认值是'0000-00-00 00:00:00'，实际插入已经被自动赋值为current_timestamp。
chinastor.com-root@localhost:test >show create table dj1G *************************** 1. row *************************** Table: dj1 Create Table: CREATE TABLE `dj1` ( `a` char(1) COLLATE utf8_bin DEFAULT NULL, `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `c` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', UNIQUE KEY `dj1_idx_u1` (`b`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin 1 row in set (0.00 sec)
#2创建表dj2，列b不带自动更新属性。
chinastor.com-root@localhost:test >CREATE TABLE `dj2` ( -> `a` char(1) COLLATE utf8_bin DEFAULT NULL, -> `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP , -> `c` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', -> UNIQUE KEY `dj1_idx_u1` (`b`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; Query OK, 0 rows affected (0.01 sec)
#3插入dj2测试数据
chinastor.com-root@localhost:test >insert into dj2 values (1,null,null); Query OK, 1 row affected (0.00 sec) chinastor.com-root@localhost:test >insert into dj2 values (2,null,null); Query OK, 1 row affected (0.00 sec)
#4查看dj2数据
chinastor.com-root@localhost:test >select * from dj2; +------+---------------------+---------------------+ | a | b | c | +------+---------------------+---------------------+ | 1 | 2009-09-09 14:02:55 | 2009-09-09 14:02:55 | | 2 | 2009-09-09 14:03:00 | 2009-09-09 14:03:00 | +------+---------------------+---------------------+ 2 rows in set (0.00 sec)
#5dj2上创建唯一索引
chinastor.com-root@localhost:test >create unique index dj2_idx_u1 on dj2(b); Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 #更新数据成功 chinastor.com-root@localhost:test >update dj2 set a=9; Query OK, 2 rows affected (0.00 sec) Rows matched: 2 Changed: 2 Warnings: 0 chinastor.com-root@localhost:test >select * from dj2; +------+---------------------+---------------------+ | a | b | c | +------+---------------------+---------------------+ | 9 | 2009-09-09 14:02:55 | 2009-09-09 14:02:55 | | 9 | 2009-09-09 14:03:00 | 2009-09-09 14:03:00 | +------+---------------------+---------------------+ 2 rows in set (0.00 sec)
#6创建表dj3，b列默认值为CURRENT_TIMESTAMP，c列默认值为CURRENT_TIMESTAMP带自动更新属性，报错，不允许行为。
chinastor.com-root@localhost:test >CREATE TABLE `dj3` ( -> `a` char(1) COLLATE utf8_bin DEFAULT NULL, -> `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP , -> `c` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -> UNIQUE KEY `dj1_idx_u1` (`b`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
#7创建表dj4，b列默认值为CURRENT_TIMESTAMP，c列默认值为'0000-00-00 00:00:00'带自动更新属性，报错，不允许行为。
chinastor.com-root@localhost:test >CREATE TABLE `dj4` ( -> `a` char(1) COLLATE utf8_bin DEFAULT NULL, -> `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP , -> `c` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP, -> UNIQUE KEY `dj1_idx_u1` (`b`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
#8创建表dj5，b列默认值为CURRENT_TIMESTAMP带自动更新属性，c列默认值为CURRENT_TIMESTAMP，报错，不允许行为。
chinastor.com-root@localhost:test >CREATE TABLE `dj5` ( -> `a` char(1) COLLATE utf8_bin DEFAULT NULL, -> `b` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -> `c` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP , -> UNIQUE KEY `dj1_idx_u1` (`b`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin; ERROR 1293 (HY000): Incorrect table definition; there can be only one TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause
以上就是MySQL数据库TIMESTAMP设置默认值的几个应用实例，该篇文章就介绍到这里，希望本次的介绍能够对您有所收获！