mysql explicit_defaults_for_timestamp参数

在mysql中：
– timestamp列如果没有显式定义为null，默认会被设置为not null属性。(其它的数据类型如果没有显式定义为not null，默认是可以为null的)。设置timestamp的列值为null，会自动存储为当前timestamp
– 表中的第一个timestamp列，如果没有定义为null、定义default值或者on update，会自动分配default current_timestamp和on update current_timestamp属性
– 表中第一个timestamp列之后的所有timestamp列，如果没有被定义为null、定义default值，会自动被指定默认值’0000-00-00 00:00:00’。在插入时，如果没有指定这些列的值，会自动指定为’0000-00-00 00:00:00’，且不会产生警告

mysql> create table timestamp_eg( -> id int not null auto_increment, -> time1 timestamp, -> time2 timestamp, -> time3 timestamp NOT NULL DEFAULT '2010-01-01 00:00:00', -> time4 timestamp, -> primary key(id)); Query OK, 0 rows affected (0.01 sec) mysql> insert into timestamp_eg(id) values(1); Query OK, 1 row affected (0.00 sec) mysql> select * from timestamp_eg; +----+---------------------+---------------------+---------------------+---------------------+ | id | time1 | time2 | time3 | time4 | +----+---------------------+---------------------+---------------------+---------------------+ | 1 | 2015-12-16 09:23:33 | 0000-00-00 00:00:00 | 2010-01-01 00:00:00 | 0000-00-00 00:00:00 | +----+---------------------+---------------------+---------------------+---------------------+ 1 row in set (0.01 sec) mysql> update timestamp_eg set id=2 where id=1; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from timestamp_eg; +----+---------------------+---------------------+---------------------+---------------------+ | id | time1 | time2 | time3 | time4 | +----+---------------------+---------------------+---------------------+---------------------+ | 2 | 2015-12-16 09:25:01 | 0000-00-00 00:00:00 | 2010-01-01 00:00:00 | 0000-00-00 00:00:00 | +----+---------------------+---------------------+---------------------+---------------------+ 1 row in set (0.00 sec) mysql> insert into timestamp_eg(id,time4) values(3,'2011-01-01 00:00:00'); Query OK, 1 row affected (0.00 sec) mysql> select * from timestamp_eg; +----+---------------------+---------------------+---------------------+---------------------+ | id | time1 | time2 | time3 | time4 | +----+---------------------+---------------------+---------------------+---------------------+ | 2 | 2015-12-16 09:25:01 | 0000-00-00 00:00:00 | 2010-01-01 00:00:00 | 0000-00-00 00:00:00 | | 3 | 2015-12-16 09:28:04 | 0000-00-00 00:00:00 | 2010-01-01 00:00:00 | 2011-01-01 00:00:00 | +----+---------------------+---------------------+---------------------+---------------------+ 2 rows in set (0.00 sec) mysql> update timestamp_eg set id=4 where id=3; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from timestamp_eg; +----+---------------------+---------------------+---------------------+---------------------+ | id | time1 | time2 | time3 | time4 | +----+---------------------+---------------------+---------------------+---------------------+ | 2 | 2015-12-16 09:25:01 | 0000-00-00 00:00:00 | 2010-01-01 00:00:00 | 0000-00-00 00:00:00 | | 4 | 2015-12-16 09:28:24 | 0000-00-00 00:00:00 | 2010-01-01 00:00:00 | 2011-01-01 00:00:00 | +----+---------------------+---------------------+---------------------+---------------------+ 2 rows in set (0.00 sec) mysql>

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58

mysql> create table timestamp_eg(     ->     id int not null auto_increment,     ->     time1 timestamp,     ->     time2 timestamp,     ->     time3 timestamp NOT NULL DEFAULT '2010-01-01 00:00:00',     ->     time4 timestamp,     ->     primary key(id)); Query OK, 0 rows affected (0.01 sec)   mysql> insert into timestamp_eg(id) values(1); Query OK, 1 row affected (0.00 sec)   mysql> select * from timestamp_eg; +----+---------------------+---------------------+---------------------+---------------------+ | id | time1               | time2               | time3               | time4               | +----+---------------------+---------------------+---------------------+---------------------+ |  1 | 2015-12-16 09:23:33 | 0000-00-00 00:00:00 | 2010-01-01 00:00:00 | 0000-00-00 00:00:00 | +----+---------------------+---------------------+---------------------+---------------------+ 1 row in set (0.01 sec)   mysql> update timestamp_eg set id=2 where id=1; Query OK, 1 row affected (0.02 sec) Rows matched: 1  Changed: 1  Warnings: 0   mysql> select * from timestamp_eg; +----+---------------------+---------------------+---------------------+---------------------+ | id | time1               | time2               | time3               | time4               | +----+---------------------+---------------------+---------------------+---------------------+ |  2 | 2015-12-16 09:25:01 | 0000-00-00 00:00:00 | 2010-01-01 00:00:00 | 0000-00-00 00:00:00 | +----+---------------------+---------------------+---------------------+---------------------+ 1 row in set (0.00 sec)   mysql> insert into timestamp_eg(id,time4) values(3,'2011-01-01 00:00:00'); Query OK, 1 row affected (0.00 sec)   mysql> select * from timestamp_eg; +----+---------------------+---------------------+---------------------+---------------------+ | id | time1               | time2               | time3               | time4               | +----+---------------------+---------------------+---------------------+---------------------+ |  2 | 2015-12-16 09:25:01 | 0000-00-00 00:00:00 | 2010-01-01 00:00:00 | 0000-00-00 00:00:00 | |  3 | 2015-12-16 09:28:04 | 0000-00-00 00:00:00 | 2010-01-01 00:00:00 | 2011-01-01 00:00:00 | +----+---------------------+---------------------+---------------------+---------------------+ 2 rows in set (0.00 sec)   mysql> update timestamp_eg set id=4 where id=3; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0   mysql> select * from timestamp_eg; +----+---------------------+---------------------+---------------------+---------------------+ | id | time1               | time2               | time3               | time4               | +----+---------------------+---------------------+---------------------+---------------------+ |  2 | 2015-12-16 09:25:01 | 0000-00-00 00:00:00 | 2010-01-01 00:00:00 | 0000-00-00 00:00:00 | |  4 | 2015-12-16 09:28:24 | 0000-00-00 00:00:00 | 2010-01-01 00:00:00 | 2011-01-01 00:00:00 | +----+---------------------+---------------------+---------------------+---------------------+ 2 rows in set (0.00 sec)   mysql>

从MySQL5.6.6这种默认设置的方法被废弃了。在MySQL启动时会出现以下警告：

要想取消该警告，在启动mysql时，my.cnf中加入

修改该参数后，timestamp类型的列的处理方式也会发生变化：

– timestamp列如果没有显式定义为not null，则支持null属性。设置timestamp的列值为null，就不会被设置为current timestamp
– 不会自动分配default current_timestamp和on update current_timestamp属性，这些属性必须显式指定
– 声明为not null且没有显式指定默认值是没有默认值的。表中插入列，又没有给timestamp列赋值时，如果是严格sql模式，会抛出一个错误；如果严格sql模式没有启用，该列会赋值为’0000-00-00 00:00:00′，同时出现一个警告。（这和mysql处理其它时间类型数据一样，如datetime）

1 mysql> create table timestamp_02( 2 -> id int not null auto_increment, 3 -> time1 timestamp not null, 4 -> primary key(id) 5 -> ); 6 Query OK, 0 rows affected (0.03 sec) 7 8 mysql> insert into timestamp_02(id) values(1); 9 ERROR 1364 (HY000): Field 'time1' doesn't have a default value 10 mysql>

1 2 3 4 5 6 7 8 9 10

1 mysql> create table timestamp_02( 2     -> id int not null auto_increment, 3     -> time1 timestamp not null, 4     -> primary key(id) 5     -> ); 6 Query OK, 0 rows affected (0.03 sec) 7   8 mysql> insert into timestamp_02(id) values(1); 9 ERROR 1364 (HY000): Field 'time1' doesn't have a default value 10 mysql>