MySQL timestamp NOT NULL插入NULL的問題

標籤：explicit_defaults_for_timestamp

explicit_defaults_for_timestamp

MySQL5.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 globalexplicit_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

本文出自 “歲伏” 部落格，請務必保留此出處http://suifu.blog.51cto.com/9167728/1783400

MySQL timestamp NOT NULL插入NULL的問題