explicit_defaults_for_timestamp参数使用详解
所有文档

          云数据库 RDS

          explicit_defaults_for_timestamp参数使用详解

          背景

          MySQL中有这样的一个默认行为:一行数据中的某些列被更新了,如果这一行中有timestamp类型的列,那么这个timestamp列会自动更新时间戳。这个行为是由explicit_defaults_for_timestamp参数控制的。在实际使用过程中经常会遇到一些非预期的异常情况,这给数据库使用过程带来诸多不便,本文重点介绍参数explicit_defaults_for_timestamp使用过程中的常见问题和注意事项。

          问题现象

          • 问题1:从MySQL5.5版本升级到MySQL5.6版本后,为什么同样的建表语句,但是创建成功后显示的表结构信息变了
          • 问题2:从MySQL5.5版本升级到MySQL5.6版本后,timestamp字段不能自动更新时间戳了
          • 问题3:从MySQL5.5版本升级到MySQL5.6版本后,为什么什么插入timestamp字段,之前是显示当前时间戳,现在变成了'0000-00-00 00:00:00'
          • 问题4:一张表中同时存在多个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时间戳的显示结果符合业务的期望要求。

          案例复现

          案例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:插入数据观察timestamp字段是否自动更新

          插入数据:

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

          设置explicit_defaults_for_timestamp不同取值,观察数据插入后的差异

          取值ON 取值OFF
          image.png image.png

          说明:取值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
          image.png image.png

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

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

          需要加上ignore关键字,这样可以插入成功,但是会报如下warning信息: image.png

          案例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;

          显示结果如下: image.png

          说明:多个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字段是否自动更新时间戳,规避使用隐式默认值的兼容性风险,例如:
          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;
          1. 建议由应用程序来控制给数据打时间戳,避免版本升级过程带来的timestamp字段行为差异的影响。
          上一篇
          lower_case_table_names参数使用详解
          下一篇
          sql_mode参数使用详解之NO_ENGINE_SUBSTITUTION