MySQL explicit_defaults_for_timestamp=ON 配置详解

李彦军

2025-12-02

mysql配置

explicit_defaults_for_timestamp 是 MySQL SQL 模式的关键选项，用于控制 TIMESTAMP 列的隐式行为，决定其是否自动应用默认值和自动更新特性。

一、核心作用

启用后（ON），TIMESTAMP 列不再自动获得以下魔法行为：

第一个 TIMESTAMP 列不再隐式添加 DEFAULT CURRENT_TIMESTAMP
TIMESTAMP 列不再隐式添加 ON UPDATE CURRENT_TIMESTAMP
TIMESTAMP 列允许赋值为 NULL（除非显式声明 NOT NULL）
行为与其他数据类型一致，遵循标准 SQL

二、行为对比

场景：创建表时不指定任何默认值

CREATE TABLE test (
    id INT PRIMARY KEY,
    ts1 TIMESTAMP,
    ts2 TIMESTAMP
);

配置	结果表结构	说明
`OFF`（默认 < 8.0）	`ts1` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP `ts2` TIMESTAMP DEFAULT '0000-00-00 00:00:00'	第一个 TIMESTAMP 自动获得当前时间默认值和自动更新，第二个获得零值
`ON`（默认 ≥ 8.0）	`ts1` TIMESTAMP NULL `ts2` TIMESTAMP NULL	两列都允许 NULL，无默认值，无自动更新

三、版本演进

MySQL 版本	默认值	推荐做法
5.6/5.7	`OFF`	必须显式启用，避免魔法行为
8.0+	`ON`	保持默认，符合标准 SQL

MySQL 8.0 启用此选项作为默认，标志着 TIMESTAMP 魔法时代的终结。

四、配置方法

1. 在 `my.cnf` 中永久配置

[mysqld]
# MySQL 5.7 及以下强烈建议开启
explicit_defaults_for_timestamp = ON

# 建议与其他 SQL 模式一起配置
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,ONLY_FULL_GROUP_BY"

2. 动态设置（无需重启，重启后失效）

-- 全局生效（需 SUPER 权限）
SET GLOBAL explicit_defaults_for_timestamp = ON;

-- 会话生效
SET SESSION explicit_defaults_for_timestamp = ON;

3. 验证当前设置

SHOW VARIABLES LIKE 'explicit_defaults_for_timestamp';

五、最佳实践

✅ 正确建表示例（`ON` 模式下）

-- 自动更新创建时间（推荐）
CREATE TABLE user (
    id INT PRIMARY KEY,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 可空时间戳
CREATE TABLE log (
    id INT PRIMARY KEY,
    deleted_at TIMESTAMP NULL  -- 显式允许 NULL
);

❌ 避免魔法行为（`OFF` 模式下的陷阱）

-- 在 OFF 模式下，ts1 会自动获得 ON UPDATE CURRENT_TIMESTAMP
-- 可能导致意料之外的数据更新
CREATE TABLE confusing (
    id INT PRIMARY KEY,
    ts1 TIMESTAMP,  -- 隐式：DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ts2 TIMESTAMP   -- 隐式：DEFAULT '0000-00-00 00:00:00'
);

六、迁移注意事项

关键警告：对已存在表无影响

-- 修改 SQL 模式后，已创建的表结构 不会改变 
-- 只有新建的表才会遵循新规则

迁移检查清单

审查现有建表语句：搜索所有未显式指定 DEFAULT 的 TIMESTAMP 列
代码生成时间戳：确保应用层或 SQL 显式提供时间值
测试环境验证：先在测试环境启用，观察是否有语法错误

常见问题

-- 错误示例：在 ON 模式下插入 NULL 到 NOT NULL 列
INSERT INTO test (id, ts1) VALUES (1, NULL); 
-- 报错：Column 'ts1' cannot be null

-- 解决方案：建表时显式指定默认值
ALTER TABLE test MODIFY ts1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

七、总结

项目	说明
推荐配置	所有版本都设置为 `ON`
核心收益	消除 TIMESTAMP 的隐式魔法，符合 SQL 标准，行为可预测
MySQL 8.0+	已默认启用，无需额外配置
MySQL 5.7-	必须手动启用，为升级到 8.0 做准备
风险评估	对已存在数据零风险，仅影响新建表