explicit_defaults_for_timestamp
MySQL 5.6版本引入
explicit_defaults_for_timestamp
来控制对timestamp NULL值的处理
如果该参数不开启,则对timestamp NOT NULL插入NULL值,不报错,无warning,插入后的值为当前时间
如果在my.cnf中explicit_defaults_for_timestamp=1
那么插入该值的时候会报错提示该列can not be null
建议开启该值
mysql> show variables like '%explicit_defaults_for_timestamp%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | explicit_defaults_for_timestamp | OFF | +---------------------------------+-------+ 1 row in set (0.00 sec)
mysql> show create table helei\G *************************** 1. row *************************** Table: helei Create Table: CREATE TABLE `helei` ( `a` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.08 sec)
mysql> select * from helei; Empty set (0.03 sec)
mysql> insert into helei values(NULL); Query OK, 1 row affected (0.39 sec)
mysql> select * from helei; +---------------------+ | a | +---------------------+ | 2016-05-26 11:44:24 | +---------------------+ 1 row in set (0.03 sec)
可以看到
explicit_defaults_for_timestamp
插入的NULL值变为当前时间,并没有被NOT NULL所限制
且该值是无法动态修改的,必须重启库才可以变更
mysql> set global explicit_defaults_for_timestamp=0;
ERROR 1238 (HY000): Variable 'explicit_defaults_for_timestamp' is a read only variable
我们在my.cnf修改该参数后并重启库后,可以看到null值已经不被允许插入了
mysql> select * from helei; +---------------------+ | a | +---------------------+ | 2016-05-26 11:44:24 | | 2016-05-26 11:45:46 | +---------------------+ 2 rows in set (0.00 sec)
mysql> insert into helei values(null); ERROR 1048 (23000): Column 'a' cannot be null
mysql> insert into helei values(NULL); ERROR 1048 (23000): Column 'a' cannot be null
explicit_defaults_for_timestamp = 0
CREATETABLE `helei` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
`t1` timestamp NULL DEFAULT NULL COMMENT 'null' ,
`t2` timestamp NOT NULL COMMENT 'not null' ,
`t3` timestamp NOT NULL ON UPDATECURRENT_TIMESTAMP COMMENT 'not null update' ,
PRIMARYKEY (`id`)
)
;
insert into helei(t1,t2,t3) values(null,null,null);
mysql> select * from helei; +------+------+---------------------+---------------------+ | id | t1 | t2 | t3 | +------+------+---------------------+---------------------+ | 2 | NULL | 2016-06-27 09:33:00 | 2016-06-27 09:33:00 |
t2虽然没有ON UPDATECURRENT_TIMESTAMP ,但由于explicit_defaults_for_timestamp没有开启,插入NULL不报错,且也插入了当前的时间
explicit_defaults_for_timestamp = 1
insert into helei(t1,t2,t3) values(null,null,null);
[SQL]insert into helei(t1,t2,t3) values(null,null,null)
[Err] 1048 - Column 't2' cannot be null
这才是我想要的