MySQL timestamp NOT NULL插入NULL的问题

超、凢脫俗 2022-01-15 22:57 309阅读 0赞

[2019独角兽企业重金招聘Python工程师标准>>> ][2019_Python_] ![hot3.png][]

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 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

<table> 
 <tbody> 
 <tr> 
 <td> 1 2 3 4 5 6 7 8 </td> 
 <td> <code>CREATETABLE&nbsp;`helei`&nbsp;(</code> <code>`id`&nbsp;&nbsp;</code><code>int</code><code>(10)&nbsp;UNSIGNED&nbsp;</code><code>NOT</code>&nbsp;<code>NULL</code>&nbsp;<code>AUTO_INCREMENT&nbsp;,</code> <code>`t1`&nbsp;&nbsp;</code><code>timestamp</code>&nbsp;<code>NULL</code>&nbsp;<code>DEFAULT</code>&nbsp;<code>NULL</code>&nbsp;<code>COMMENT&nbsp;</code><code>'null'</code>&nbsp;<code>,</code> <code>`t2`&nbsp;&nbsp;</code><code>timestamp</code>&nbsp;<code>NOT</code>&nbsp;<code>NULL</code>&nbsp;<code>COMMENT&nbsp;</code><code>'not&nbsp;null'</code>&nbsp;<code>,</code> <code>`t3`&nbsp;&nbsp;</code><code>timestamp</code>&nbsp;<code>NOT</code>&nbsp;<code>NULL</code>&nbsp;<code>ON</code>&nbsp;<code>UPDATECURRENT_TIMESTAMP&nbsp;COMMENT&nbsp;</code><code>'not&nbsp;null&nbsp;update'</code>&nbsp;<code>,</code> <code>PRIMARYKEY&nbsp;(`id`)</code> <code>)</code> <code>;</code> </td> 
 </tr> 
 </tbody> 
</table>

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

这才是我想要的

转载于:https://my.oschina.net/yq0128/blog/1475346

[2019_Python_]: https://my.oschina.net/u/2663968/blog/3061667
[hot3.png]: /images/20220115/bb2f172cafd74cd7b2addd2319c29c6f.png