MySQL 迁移中 explicit_defaults_for_timestamp 参数影响

前言

最近在做数据迁移的时候，使用的是云平台自带的同步工具，在预检查阶段，当时报错 explicit_defaults_for_timestamp 参数在目标端为 off 建议修改 on，有什么风险呢？在此记录下。

测试对比

MySQL 默认情况下 explicit_defaults_for_timestamp = 0 我们对比一下看看。

explicit_defaults_for_timestamp = 0

在 explicit_defaults_for_timestamp 参数等于 0 的状态下，如果第一个 timestamp 类型的字段如果没有设置 null 将会自动加上 not null 和默认值。如果设置了默认值，则会自动加上 not null，其他类型的字段是不会的。

create table test03 (a_time timestamp ,b_time timestamp null ,c_time timestamp default '2024-01-01 00:00:00',name varchar(2) default 'a');

show create table test03;

CREATE TABLE `test03` (`a_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,`b_time` timestamp NULL DEFAULT NULL,`c_time` timestamp NOT NULL DEFAULT '2024-01-01 00:00:00',`name` varchar(2) DEFAULT 'a'
) ENGINE=InnoDB DEFAULT CHARSET=latin1

然后，我们尝试插入一个记录：

insert into test03 values (null, null, null, null);

a_time	b_time	c_time	name
2024-09-05 14:37:12	NULL	2024-09-05 14:37:12	NULL

可以看到，在 explicit_defaults_for_timestamp 等于 0 的时候，不仅会影响表结构，还会影响写入。虽然 timestamp 是 not null 我们写入 null 后变为了 CURRENT_TIMESTAMP。

如果此时将参数设置为 1，执行相同的 SQL 语句则会报错：

# 设置参数为 1
set global explicit_defaults_for_timestamp = 1;# 插入相同的数据
insert into test03 values (null, null, null, null);

[23000][1048] Column ‘a_time’ cannot be null

explicit_defaults_for_timestamp = 1

此时在 explicit_defaults_for_timestamp 等于 1 的条件下，执行一个刚才的建表语句：

create table test04 (a_time timestamp ,b_time timestamp null ,c_time timestamp default '2024-01-01 00:00:00',d_time timestamp not null );

show create table test04;

CREATE TABLE `test04` (`a_time` timestamp NULL DEFAULT NULL,`b_time` timestamp NULL DEFAULT NULL,`c_time` timestamp NULL DEFAULT '2024-01-01 00:00:00',`d_time` timestamp NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

尝试插入数据：

insert into test04 values (null, null, null, null);

a_time	b_time	c_time	name
NULL	NULL	NULL	NULL

从上面可以看到，参数开启的情况下，MySQL 默认会为 timestamp 为 NULL 的字段添加 default null 属性。而且 MySQL 没有为第一个 timestamp 设置默认值，当我写入 null 后，则按照 null 来存储。

另外，如果 timestamp 设置为 not null 那么写入 null 时会报错，如果 sql_mode 中不包含 SQL_MODE 的话，则存储为 ‘0000-00-00 00:00:00’ 并抛出一个异常。

总结

生产环境 timestamp 字段一般都会设置为如下样式，而且需要 timestamp 字段为 null 的业务场景很少。

`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

如果生产环境，要修改这个参数，从 0 调整到 1 需要关注的就是 insert 语句，有没有直接图省事插入 null 表示当前时间，如果有这种不规范的语法，那么调整该参数后，可能会报错。

云平台一般不会给用户 super 账号，所以在迁移过程中，可能无法设置 session 级别的参数，此时建议跳过该校验，不修改全局级别的参数。