📢📢📢📣📣📣
哈喽!大家好,我是「奇点」,江湖人称 singularity。刚工作几年,想和大家一同进步🤝🤝
一位上进心十足的【Java ToB端大厂领域博主】!😜😜😜
喜欢java和python,平时比较懒,能用程序解决的坚决不手动解决😜😜😜✨ 如果有对【java】【python】和【前端】感兴趣的【小可爱】,欢迎关注我
❤️❤️❤️感谢各位大可爱小可爱!❤️❤️❤️
————————————————如果觉得本文对你有帮助,欢迎点赞,欢迎关注我,如果有补充欢迎评论交流,我将努力创作更多更好的文章。
目录
我们在使用Mysql数据库的时候,由于版本的不同,数据库配置的默认值是不同的。这就导致原来的环境下,我的代码没有问题,但是今天部署在一个新的环境下,相同的代码在这里就报错了,这种水土不服的情况经常发生,今天我们就来聊一聊 explicit_defaults_for_timestamp 这个配置的问题,导致时间戳插入为NUll时报错的问题。
👩🎓:老师我昨天的作业项目中遇到一个问题,在数据库中插入数据的时候报错,显示时间戳插入的时候不能为null,别的同学都没这个问题啊!!!我之前在学校机器上练习的时候还是好好的,今天在自己的机器上就突然报错了显示Column 'ts' cannot be null ??? 相同的代码咋偏偏在我机器上就报错,室友都没事!!!!!
👨🎓:你这个问题我之前也遇到过,我网上查着是时间戳在更新和插入的时候不能为NULL导致的。
👩🏫:你这么说很笼统,严格上来说是不正确的,这种报错只有在一定的条件上发生的,今天就这个问题,老师来好好讲讲这个问题,以防大家以后工作的时候,遇到这个问题,而无从下手。
👩🏫:首先我们来说说这个explicit_defaults_for_timestamp参数会带来的影响:
👩🏫:造成以上问题的根本原因是explicit_defaults_for_timestamp参数。
该参数是在MySQL5.6.6版本被引入的,更早之前版本的行为等价于取值OFF。在5.6、5.7系列版本的默认值都是OFF,从MySQL8.0.2版本开始,默认值是ON。该参数类型是布尔型,取值含义说明如下:
在版本升级和数据迁移过程中,对于存在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;
👩🏫:下面我们来重现一下这个问题:
- 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; |
👨🎓:当打开这个设置的时候,不会在表的结构中设置时间戳的生成方式。
👩🏫:是这样子的,所以这里不会自动更新时间戳
- 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
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'。
👩🎓:
ALTER TABLE tb_02 modify column w timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_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的系统参数,并讲解了一下他的前世今生,也复现了一下这个参数会导致的问题,详细同学以后遇到这个问题就知道怎么解决了。下课,祝大家国庆愉快!!