1 问题描述

• 问题背景

执行 从 MYSQL 5.7 导出，并在 MYSQL 8.0.33 的数据库中执行 DDL 建表语句时，报Invalid default value for 'create_at'

...
  `create_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间/created time',
...

2 原因分析

• 表面原因：导出的DDL版本不是 MYSQL8 ， timestamp类型的字段DDL中存在 DEFAULT '0000-00-00 00:00:00'

2.1 根本原因1：explicit_defaults_for_timestamp

• explicit_defaults_for_timestamp 系统变量决定MySQL服务端对timestamp列中的默认值和NULL值的不同处理方法。
• 此变量自MySQL 5.6.6 版本引入，分为全局级别和会话级别，可动态更新，默认值为OFF。
• 在 MYSQL 8.0 之中默认值改为了on

• explicit_defaults_for_timestamp=OFF,表示使用默认的timestamp默认格式；

• timestamp类型的默认格式是什么样的呢？

1.和其它字段类型不一样，这个字段默认为not null.而且不允许设置default null.
2.第一列timestamp字段，如果不强制指定默认值或on update属性的话，就会默认设为DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP。
3.非第一列timestamp字段，如果不强制指定默认值，DEFAULT ‘0000-00-00 00:00:00’
4.往该列中插入null值，会自动转化为默认值；

• explicit_defaults_for_timestamp=ON，则关闭timestamp default的特性：

1. 如果没有被显示指定not null，则默认为null； 
2. 默认值也会是null而非CURRENT_TIMESTAMP; 
3. 如果指定了not null属性,inset式不指定该字段的值，且 strict sql_mode(SQL_MODE=STRICT_TRANS_TABLES) 下，会报错。非strict sql_mode下允许插入’0000-00-00 00:00:00’；

• 解决思路：

执行以下sql语句,重启mysql服务

SHOW GLOBAL VARIABLES LIKE "explicit_defaults_for_timestamp";
SET persist explicit_defaults_for_timestamp=OFF;

-- mysql8.0 新增了持久化修改全局变量，
-- 使用 set persist 和修改配置文件的效果一致，重启之后无需再改一遍了

2.2 根本原因2：SQL_MODE = STRICT_TRANS_TABLES

• explicit_defaults_for_timestamp=ON，则关闭timestamp default的特性:

如果指定了not null属性,inset式不指定该字段的值，且 strict sql_mode(SQL_MODE=STRICT_TRANS_TABLES) 下，会报错。非strict sql_mode下允许插入’0000-00-00 00:00:00’

3 解决方法

• 方法1：换用datetime类型

• 方法2：保持严格约束表的SQL模式，但修改timestamp字段的默认值的策略

修改timestamp字段的DDL定义（不修改timestamp的类型）

...
  -- `create_at` timestamp DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间/created time',
  `create_at` timestamp NULL DEFAULT current_timestamp COMMENT '创建时间/created time', -- current_timestamp  也可换成 NULL
  `create_by` varchar(15) NOT NULL DEFAULT '-1' COMMENT '创建人/creater. default: -1(super administrator)',
  -- `update_at` timestamp DEFAULT '0000-00-00 00:00:00' COMMENT '更新时间/modified time',
  `update_at` timestamp DEFAULT current_timestamp ON UPDATE current_timestamp COMMENT '更新时间/modified time',
  `update_by` varchar(15) NOT NULL DEFAULT '-1' COMMENT '更新人/updater. default: -1(super administrator)',
...

• 方法2：关闭explicit_defaults_for_timestamp ，取消严格约束表的SQL模式 (未亲测)

• Step1 设置explicit_defaults_for_timestamp = OFF

SHOW GLOBAL VARIABLES LIKE "explicit_defaults_for_timestamp";
SET persist explicit_defaults_for_timestamp=OFF;

-- mysql 8.0 新增了持久化修改全局变量，
-- 使用 set persist 和修改配置文件的效果一致，重启之后无需再改一遍了

• Step2 改为非严格约束表的SQL模式(STRICT_TRANS_TABLES)

• step2.1 在mysql的配置文件my.ini中 [mysqld]下面添加如下列；
• step2.2 保存后重启

SHOW GLOBAL VARIABLES LIKE "sql_mode";

-- SET persist sql_mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"
SET persist sql_mode="ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

X 参考文献

• explicit_defaults_for_timestamp引发的狗血剧情 - 博客园
• mysql8中timestamp时间戳设置默认值 CURRENT_TIMESTAMP ，Error:1048 - Column ‘createTime‘ cannot be null - CSDN
• MySQL模式设置方式介绍(严格模式:STRICT_TRANS_TABLES,无引擎提交:NO_ENGINE_SUBSTITUTION,填补字符长度:PAD_CHAR_TO_FULL_LENGTH) - CSDN
• MySQL 里 timestamp 的默认值设置 - CSDN
• mysql各版本timestamp默认值 - CSDN