MySQL timestamp NOT NULL插入NULL的问题

📢📢📢📣📣📣
哈喽！大家好，我是「奇点」，江湖人称 singularity。刚工作几年，想和大家一同进步🤝🤝
一位上进心十足的【Java ToB端大厂领域博主】！😜😜😜
喜欢java和python，平时比较懒，能用程序解决的坚决不手动解决😜😜😜

✨ 如果有对【java】【python】和【前端】感兴趣的【小可爱】，欢迎关注我

❤️❤️❤️感谢各位大可爱小可爱！❤️❤️❤️
————————————————

如果觉得本文对你有帮助，欢迎点赞，欢迎关注我，如果有补充欢迎评论交流，我将努力创作更多更好的文章。

目录

问题现象

原因：

注意：

查询这个参数的方法

更改方法：

1.使用同样SQL查看该参数对建表后表结构的差异，建表语句

2.插入数据时间戳是非自动更新

3.timestamp字段插入NULL值的结果差异

结论建议

结论

👩‍🏫：建议

---

我们在使用Mysql数据库的时候，由于版本的不同，数据库配置的默认值是不同的。这就导致原来的环境下，我的代码没有问题，但是今天部署在一个新的环境下，相同的代码在这里就报错了，这种水土不服的情况经常发生，今天我们就来聊一聊 explicit_defaults_for_timestamp 这个配置的问题，导致时间戳插入为NUll时报错的问题。

👩‍🎓：老师我昨天的作业项目中遇到一个问题，在数据库中插入数据的时候报错，显示时间戳插入的时候不能为null，别的同学都没这个问题啊！！！我之前在学校机器上练习的时候还是好好的，今天在自己的机器上就突然报错了显示Column 'ts' cannot be null ??? 相同的代码咋偏偏在我机器上就报错，室友都没事！！！！！

👨‍🎓：你这个问题我之前也遇到过，我网上查着是时间戳在更新和插入的时候不能为NULL导致的。

👩‍🏫：你这么说很笼统，严格上来说是不正确的，这种报错只有在一定的条件上发生的，今天就这个问题，老师来好好讲讲这个问题，以防大家以后工作的时候，遇到这个问题，而无从下手。

👩‍🏫：首先我们来说说这个explicit_defaults_for_timestamp参数会带来的影响：

问题现象

• 问题1：插入和更新的时候提示column [时间戳这列] cannot be null 
• 问题2：从MySQL5.5版本升级到MySQL5.6版本后，为什么同样的建表语句，但是创建成功后显示的表结构信息变了
• 问题3：从MySQL5.5版本升级到MySQL5.6版本后，timestamp字段不能自动更新时间戳了
• 问题4：从MySQL5.5版本升级到MySQL5.6版本后，为什么插入timestamp字段，之前是显示当前时间戳，现在变成了'0000-00-00 00:00:00'
• 问题5：一张表中同时存在多个timestamp字段，字段默认值的显示结果不一样

原因：

👩‍🏫：造成以上问题的根本原因是explicit_defaults_for_timestamp参数。

该参数是在MySQL5.6.6版本被引入的，更早之前版本的行为等价于取值OFF。在5.6、5.7系列版本的默认值都是OFF，从MySQL8.0.2版本开始，默认值是ON。该参数类型是布尔型，取值含义说明如下：

注意：

• • ON：关闭timestamp default的特性，不会自动更新时间戳，除非表结构定义时显式的指定；
  • OFF：开启timestamp default的特性，会自动更新时间戳，会在建表时自动给timestamp字段类型的表结构定义添加如下内容：DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

在版本升级和数据迁移过程中，对于存在timestamp字段的数据，需要对齐explicit_defaults_for_timestamp参数的取值，确保timestamp时间戳的显示结果符合业务的期望要求。

查询这个参数的方法

👩‍🏫： show variables like '%explicit_defaults_for_timestamp%';

mysql> show variables like '%explicit_defaults_for_timestamp%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | OFF   |
+---------------------------------+-------+
1 row in set (0.00 sec)

更改方法：

👩‍🏫：该值是无法动态修改的，必须重启库才可以变更

mysql> set global explicit_defaults_for_timestamp=0;

👩‍🏫:下面我们来重现一下这个问题：

1.使用同样SQL查看该参数对建表后表结构的差异，建表语句

CREATE TABLE tb_01(
   x int(11) NOT NULL,
   y timestamp NOT NULL,
   PRIMARY KEY (x)
) ENGINE=InnoDB;

设置explicit_defaults_for_timestamp不同取值，观察创建后的表结构差异：

取值ON	取值OFF
CREATE TABLE tb_01 ( x int(11) NOT NULL, y timestamp NOT NULL, PRIMARY KEY (x) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;	CREATE TABLE tb_01 ( x int(11) NOT NULL, y timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (x) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

👨‍🎓：当打开这个设置的时候，不会在表的结构中设置时间戳的生成方式。

 👩‍🏫：是这样子的，所以这里不会自动更新时间戳

2.插入数据时间戳是非自动更新

insert into tb_01(x) values(1);
insert into tb_01(x) values(2);
insert into tb_01(x) values(3);

ON	OFF

说明：取值ON的情况下，插入数据可能会报错如下，原因是由于当前实例的sql_mode设置了NO_ZERO_IN_DATE和NO_ZERO_DATE，修改sql_mode为宽松模式，即可插入成功，但是会产生warning信息。

ERROR 1364 (HY000): Field 'y' doesn't have a default value

3.timestamp字段插入NULL值的结果差异

insert ignore into tb_01 values(1,NULL);

取值ON	取值OFF

说明：取值为ON时直接insert into会报错如下：

ERROR 1048 (23000): Column 'y' cannot be null

这个问题就是小红同学之前遇到的这个问题，你的数据库开启了这个参数，有可能你现在用的Mysql是8.0.2y以上的版本

👩‍🎓：是的老师，我刚安装的8.x

👩‍🏫：需要加上ignore关键字，这样可以插入成功，但是会报如下warning信息：

4.当有多个timestamp字段

设置参数explicit_defaults_for_timestamp=OFF，创建表：

CREATE TABLE tb_02(
   x int(11) NOT NULL AUTO_INCREMENT,
   y timestamp NOT NULL,
   z timestamp NOT NULL,
   w timestamp NOT NULL,
   PRIMARY KEY (x)
) ENGINE=InnoDB;

结果如下

说明：多个timestamp类型字段同时存在的情况下，仅对一个字段的默认值是随时间戳更新的，其他的默认值都是'0000-00-00 00:00:00'。

结论建议

结论

👩‍🎓：

• 如果要利用timestamp字段自动更新时间戳，需要在创建表结构前设置explicit_defaults_for_timestamp=OFF，然后创建表后会自动给timestamp字段添加定义：DEFAULT CURRENT_TIMESTAMP ON UPDATE
• 如果表已经创建完成，需要执行ALTER TABLE命令，显式的给timestamp字段添加自动更新的表定义，例如：

ALTER TABLE tb_02 modify column  w  timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; 

👩‍🏫：建议

1. 数据迁移前，需要确保参数explicit_defaults_for_timestamp在源库和目标库的取值是一致的
2. 创建表时，建议显式的指定timestamp字段是否自动更新时间戳，规避使用隐式默认值的兼容性风险，例如：
3. 建议由应用程序来控制给数据打时间戳，避免版本升级过程带来的timestamp字段行为差异的影响。

CREATE TABLE tb_01 (
  x int(11) NOT NULL AUTO_INCREMENT,
  y timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  z timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (x)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

👩‍🏫：好了，今天我们讲了explicit_defaults_for_timestamp这个Mysql的系统参数，并讲解了一下他的前世今生，也复现了一下这个参数会导致的问题，详细同学以后遇到这个问题就知道怎么解决了。下课，祝大家国庆愉快！！