共计 6154 个字符，预计需要花费 16 分钟才能阅读完成。
作者：秦福朗
爱可生 DBA 团队成员，负责我的项目日常问题解决及公司平台问题排查。酷爱互联网，会摄影、懂厨艺，不会厨艺的 DBA 不是好司机，didi~
本文起源：原创投稿
* 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明来 > 源。
背景
一个业务零碎刚迁徙完，笔者刚回到家，开发那边就遇到了业务报错”Column‘create_time’cannot be null”，从字面意思能够了解为表字段’create_time’想插入 null 值，但报错该字段不能为 null。由此引发了对 explicit_defaults_for_timestamp 这个无关工夫参数的思考。
概念概述
1. TIMESTAMP 和 DATETIME
提 explicit_defaults_for_timestamp 参数，首先就要简略解释下工夫数据类型 TIMESTAMP 和 DATETIME：
- TIMESTAMP 是一个工夫戳，范畴是 ’1970-01-01 00:00:01.000000’UTC 到 ’2038-01-19 03:14:07.999999’UTC。
- DATETIME 是日期和工夫的组合，范畴是 ’1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’。
TIMESTAMP 和 DATETIME 列都能够主动初始化并且能够更新为以后的日期和工夫，列还能够将以后的工夫戳指定为默认值、自动更新的值或者两个同时应用都能够。
2. explicit_defaults_for_timestamp
这个零碎变量决定了 MySQL 是否为 TIMESTAMP 列的默认值和 NULL 值的解决启用某些非标准的行为。在 MySQL5.7 的默认状况下，explicit_defaults_for_timestamp 是禁用的，这将启用非标准的行为。在 MySQL8.0 的默认值是开启的。本文默认在 MySQL5.7 场景下。
看场景
业务报错”Column‘create_time’cannot be null”，该列不能插入 null 值，查看一下表构造：
# 只展现局部工夫相干列
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创立工夫',
`update_time` timestamp NULL DEFAULT NULL COMMENT '更新工夫',能够看到 create_time 列的属性是 not null，依照惯性思维想，此列不应该插入 null，为何之前的环境是没有问题的呢？经查看参数发现问题出在 explicit_defaults_for_timestamp 参数上，在迁徙前零碎没有独自设置该参数值，从 MySQL5.7 的官网文档可知，此时应用默认值为 OFF，在迁徙后的新零碎应用的爱可生的 DMP 数据库运维平台的默认 MySQL5.7 配置文件，此时配置文件是配置了该参数值为 ON。
现场进行参数敞开，改为 OFF，测试插入失常。那么参数值具体为何能操纵 TIMESTAMP 列的默认值和 null 值呢？持续测试剖析。
测试剖析
1. 首先是看一下官网对 explicit_defaults_for_timestamp 具体解释：
（1）如果 explicit_defaults_for_timestamp=OFF，服务器会启用非标准行为，并按以下形式解决 TIMESTAMP 列：
- 没有明确应用 NULL 属性申明的 TIMESTAMP 列会主动应用 NOT NULL 属性申明。给这样的列调配一个 NULL 的值是容许的，并将该列设置为 current timestamp。
- 表中的第一个 TIMESTAMP 列，如果没有明确地用 NULL 属性，DEFAULT 属性或 ON UPDATE 属性申明，将主动用 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 属性申明。
- 在第一个列之后的 TIMESTAMP 列，如果没有明确地用 NULL 属性或明确的 DEFAULT 属性来申明，就会主动申明为 DEFAULT ‘0000-00-00 00:00:00’。对于插入的行，如果没有为该列指定明确的值，那么该列将被调配为 ’0000-00-00 00:00:00’，并且不会产生正告。依据是否启用了严格的 SQL mode 或蕴含 NO_ZERO_DATE 的 SQL mode，默认值 ’0000-00-00 00:00:00’ 可能是不被容许的。
另外须要晓得的是这种非标准行为已被废除；预计将在 MySQL 的将来版本中被删除。
（2）如果 explicit_defaults_for_timestamp=ON，服务器将禁用非标准行为并按如下形式解决 TIMESTAMP 列：
- 不能实现给 TIMESTAMP 列插入一个 NULL 的值，而后主动设置为以后的工夫戳。想要插入以后的工夫戳，须要将该列设置为 CURRENT_TIMESTAMP 或一个同义词，比方 NOW()。
- 没有明确地用 NOT NULL 属性申明的 TIMESTAMP 列会主动用 NULL 属性申明，并容许 NULL 值。给这样的列插入一个 NULL 值，会把它设置为 NULL 值，而不是以后的工夫戳。
- 用 NOT NULL 属性申明的 TIMESTAMP 列不容许 NULL 值。对于列指定插入 NULL，如果启用严格的 SQL mode，其后果是单行插入报错，或者在禁用严格的 SQL 模式下，多行插入的后果是 ’0000-00-00 00:00:00’。在任何状况下，给该列赋值为 NULL 都不会将其设置为以后的工夫戳。
- 用 NOT NULL 属性明确申明的 TIMESTAMP 列，如果没有明确的 DEFAULT 属性，将被视为没有默认值。对于插入的行，如果没有为这样的列指定明确的值，其后果取决于 SQL mode。如果启用了严格的 SQL mode，会报错。如果没有启用严格的 SQL mode，该列则被申明为隐含的默认值 “0000-00-00 00:00:00″，并收回 warning。这与 MySQL 解决其余工夫类型（如 DATETIME）的形式类似。
2. 做个测试就可以看进去：
（1）explicit_defaults_for_timestamp=OFF：
mysql> show variables like "%explicit_defaults_for_timestamp%";
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | OFF |
+---------------------------------+-------+
1 row in set (0.00 sec)创立一个带有 timestamp 列的表：
mysql> create table time_off(id int,time timestamp);
Query OK, 0 rows affected (0.02 sec)
mysql> show create table time_off;
+----------+------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------------+
| Table | Create Table |
+----------+------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------------+
| time_off | CREATE TABLE `time_off` (`id` int(11) DEFAULT NULL,
`time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+----------+------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------------+
1 row in set (0.00 sec)能够看到此时 timestamp 列会有默认属性‘NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP’。
向该表插入 NULL 值试试看：
mysql> insert into time_off values (1,null);
Query OK, 1 row affected (0.00 sec)
mysql> select * from time_off;
+------+---------------------+
| id | time |
+------+---------------------+
| 1 | 2021-10-12 01:05:28 |
+------+---------------------+
1 row in set (0.00 sec)
mysql> update time_off set id=2 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from time_off;
+------+---------------------+
| id | time |
+------+---------------------+
| 2 | 2021-10-12 01:06:30 |
+------+---------------------+
1 row in set (0.00 sec)发现当 timestamp 列插入 null 值时会失常插入，并主动转换为以后工夫戳。更新其余列时也会根据‘ON UPDATE CURRENT_TIMESTAMP’来更新为以后的工夫戳。
（2）explicit_defaults_for_timestamp=ON：
mysql> show variables like "%explicit_defaults_for_timestamp%";
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | ON |
+---------------------------------+-------+
1 row in set (0.01 sec)创立一个带有 timestamp 列的表：
mysql> create table time_on(id int,time timestamp);
Query OK, 0 rows affected (0.01 sec)
mysql> show create table time_on;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| time_on | CREATE TABLE `time_on` (`id` int(11) DEFAULT NULL,
`time` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec) 能够看到此时 timestamp 列会有默认属性‘NULL DEFAULT NULL’。
向该表插入 NULL 值试试看：
mysql> insert into time_on values (1,null);
Query OK, 1 row affected (0.00 sec)
mysql> select * from time_on;
+------+------+
| id | time |
+------+------+
| 1 | NULL |
+------+------+
1 row in set (0.00 sec)会发现可能胜利插入，插入的为 NULL 值，而非以后的工夫戳。
那么在该参数下，向参数值为 OFF 时创立的表 time_off 里插入 null 值会有什么状况呢：
mysql> insert into time_off values (3,null);
ERROR 1048 (23000): Column 'time' cannot be null会发现此时插入报错’Column ‘time’ cannot be null’，合乎官网文档对该参数的阐明，也证实了业务测试报错的起因是 explicit_defaults_for_timestamp 的参数值设置为 ON，导致业务插入数据失败。
结语
对于该参数，实际上是标准了 MySQL 工夫相干的操作，使之更加严格，是有助于 MySQL 的规范化应用的，所以 MySQL 后续也废除掉该参数。
细节决定成败，很多同学对迁徙工作感觉是驾轻就熟，然而没有正当的迁徙布局，没有通过谨严的业务测试，的确不太好说迁徙的整个过程会是顺顺利利的，有时候坑就在小的细节点上。
