写点什么

MySQL 迁移至 GreatSQL 后,timestamp 字段插入报错解析

作者:GreatSQL
  • 2025-09-19
    福建
  • 本文字数:7225 字

    阅读完需:约 24 分钟

MySQL 迁移至 GreatSQL 后,timestamp 字段插入报错解析

背景描述

某业务系统进行国产化适配,将 MySQL的数据迁移到 GreatSQL 后,执行 INSERT INTO ,update_time 传参为空时报错,报错信息为:ERROR 1048 (23000): Column 'update_time' cannot be null ,而原来旧的 MySQL 环境中没有这个问题。

greatsql> INSERT INTO `t_interface` (`user_id`, `department_id`, `update_time`, `is_deleted`) VALUES (9, 18, null, 0);ERROR 1048 (23000): Column 'update_time' cannot be null 
复制代码

问题分析

1、在 GreatSQL 进行复现

CREATE TABLE `t_interface` (  `interfacer_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',  `user_id` int(11) NOT NULL COMMENT '用户id(部门接口人)',  `department_id` int(11) NOT NULL COMMENT '部门id',  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',  `remark` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,  `is_deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否删除(0未删除 1已删除)',  PRIMARY KEY (`interfacer_id`) USING BTREE,  KEY `user_id` (`user_id`) USING BTREE,  KEY `department_id` (`department_id`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=1162 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

INSERT INTO `t_interface` (`user_id`, `department_id`, `update_time`, `is_deleted`) VALUES (9, 18, null, 0);
greatsql> INSERT INTO `t_interface` (`user_id`, `department_id`, `update_time`, `is_deleted`) VALUES (9, 18, null, 0);ERROR 1048 (23000): Column 'update_time' cannot be null
复制代码

在 GreatSQL 中,INSERT 语句确实报错了。

2、在 MySQL 8.0.32 中进行复现

mysql> SELECT  version();+-----------+| version() |+-----------+| 8.0.32    |+-----------+1 row in set (0.00 sec)
mysql> CREATE TABLE `t_interface` ( -> `interfacer_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id', -> `user_id` int(11) NOT NULL COMMENT '用户id(部门接口人)', -> `department_id` int(11) NOT NULL COMMENT '部门id', -> `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', -> `remark` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL, -> `is_deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否删除(0未删除 1已删除)', -> PRIMARY KEY (`interfacer_id`) USING BTREE, -> KEY `user_id` (`user_id`) USING BTREE, -> KEY `department_id` (`department_id`) USING BTREE -> ) ENGINE=InnoDB AUTO_INCREMENT=1162 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;INSERT INTO `t_interface` Query OK, 0 rows affected, 7 warnings (0.04 sec)

mysql> INSERT INTO `t_interface` -> (`user_id`, `department_id`, `update_time`, `is_deleted`) VALUES (9, 18, null, 0);ERROR 1048 (23000): Column 'update_time' cannot be null
复制代码

在 MySQL 8.0.32 中,INSERT 语句也报错了。

3、在 MySQL 5.7.30 中进行复现

mysql> SELECT  version();+------------+| version()  |+------------+| 5.7.30-log |+------------+1 row in set (0.00 sec)mysql> CREATE TABLE `t_interface` (    ->   `interfacer_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',    ->   `user_id` int(11) NOT NULL COMMENT '用户id(部门接口人)',    ->   `department_id` int(11) NOT NULL COMMENT '部门id',    ->   `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',    ->   `remark` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,    ->   `is_deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否删除(0未删除 1已删除)',    ->   PRIMARY KEY (`interfacer_id`) USING BTREE,    ->   KEY `user_id` (`user_id`) USING BTREE,    ->   KEY `department_id` (`department_id`) USING BTREE    -> ) ENGINE=InnoDB AUTO_INCREMENT=1162 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO `t_interface` -> (`user_id`, `department_id`, `update_time`, `is_deleted`) VALUES (9, 18, null, 0);Query OK, 1 row affected (0.00 sec)
mysql> SELECT * from t_interface;+---------------+---------+---------------+---------------------+--------+------------+| interfacer_id | user_id | department_id | update_time | remark | is_deleted |+---------------+---------+---------------+---------------------+--------+------------+| 1162 | 9 | 18 | 2025-07-08 10:34:43 | NULL | 0 |+---------------+---------+---------------+---------------------+--------+------------+1 row in set (0.00 sec)
复制代码

在 MySQL 5.7.30 中,INSERT 语句可以正常执行。

4、问题排查

查看表的字段定义:

update_time:timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'

建表语句里 update_time 字段类型为 timestamp,NOT NULL 的限制,默认值为 CURRENT_TIMESTAMP。

字段有 NOT NULL 的限制,不让插入 NULL 值,理论上是正确的。但 MySQL 5.7 为什么能插入成功呢?

查询系统 timestamp 相关的系统参数

mysql> SELECT  version();+------------+| version()  |+------------+| 5.7.30-log |+------------+1 row in set (0.00 sec)
mysql>SHOW variables LIKE '%timestamp%';+---------------------------------+-------------------+| Variable_name | Value |+---------------------------------+-------------------+| explicit_defaults_for_timestamp | OFF || log_timestamps | SYSTEM || timestamp | 1751270610.230160 |+---------------------------------+-------------------+3 rows in set (0.01 sec)
mysql> SELECT VERSION();+-----------+| VERSION() |+-----------+| 8.0.32 |+-----------+1 row in set (0.00 sec)
mysql> SHOW variables like '%timestamp%';+---------------------------------+-------------------+| Variable_name | Value |+---------------------------------+-------------------+| explicit_defaults_for_timestamp | ON || log_timestamps | SYSTEM || original_commit_timestamp | 36028797018963968 || timestamp | 1751270143.113409 |+---------------------------------+-------------------+4 rows in set (0.01 sec)

greatsql>SELECT version();+-----------+| version() |+-----------+| 8.0.32-26 |+-----------+1 row in set (0.00 sec)
greatsql>SHOW variables like '%timestamp%';+---------------------------------+-------------------+| Variable_name | Value |+---------------------------------+-------------------+| explicit_defaults_for_timestamp | ON || log_timestamps | SYSTEM || original_commit_timestamp | 36028797018963968 || timestamp | 1751271661.160386 |+---------------------------------+-------------------+4 rows in set (0.02 sec)
复制代码

可以看到在 MySQL 5.7 中 explicit_defaults_for_timestamp =OFF,

在 MySQL 8.0.32 和 GreatSQL 中 explicit_defaults_for_timestamp =ON

greatsql>INSERT INTO `t_interface`     -> (`user_id`, `department_id`, `update_time`, `is_deleted`) VALUES (9, 18, null, 0);ERROR 1048 (23000): Column 'update_time' cannot be nullgreatsql>set explicit_defaults_for_timestamp=OFF;Query OK, 0 rows affected, 1 warning (0.00 sec)
greatsql>INSERT INTO `t_interface` (`user_id`, `department_id`, `update_time`, `is_deleted`) VALUES (9, 18, null, 0);Query OK, 1 row affected (0.00 sec)
复制代码

在 greatsql 中,设置 explicit_defaults_for_timestamp =OFF 后,INSERT 语句可以正常执行。

5、参数说明

explicit_defaults_for_timestamp 这个系统变量决定服务器是否为 TIMESTAMP 列中的默认值和 空值处理启用某些非标准行为。 默认情况下,MySQL5.7 禁用 explicit_defaults_for_timestamp, 它启用非标准行为。MySQL8.启用 explicit_defaults_for_timestamp,禁用非标准行为。

如果 explicit_defaults_for_timestamp 被禁用,服务器将启用非标准行为并按如下方式处理 TIMESTAMP 列:

1、未显式声明 NULL 属性的 TIMESTAMP 列将自动声明 not NULL 属性。允许将这样的列赋值为 NULL,并将列设置为当前时间戳。

2、表中的第一个 TIMESTAMP 列,如果没有显式地使用 NULL 属性或显式地使用 DEFAULT 或 ON UPDATE 属性声明,则会自动使用 DEFAULT CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP 属性声明。

3、第一个之后的 TIMESTAMP 列,如果没有显式地使用 NULL 属性或显式的 DEFAULT 属性声明,将自动声明为 DEFAULT '0000-00-00 00:00:00'(“零”时间戳)。对于没有为这样的列指定显式值的插入行,将为该列分配‘0000-00-00 00:00:00’,并且不会出现警告。

4、根据是否启用了 strict SQL 模式或 NO_ZERO_DATE SQL 模式,默认值'0000-00-00 00:00:00'可能无效。请注意,TRADITIONAL SQL 模式包括严格模式和 NO_ZERO_DATE。

非标准行为在 MySQL 的未来版本中会被删除。

如果启用了 explicit_defaults_for_timestamp,服务器将禁用非标准行为并按如下方式处理 TIMESTAMP 列:

1、不会将 TIMESTAMP NULL 列设置为当前时间戳。要分配当前时间戳,请将列设置为 CURRENT_TIMESTAMP 或 NOW()之类的同义词。

2、未显式声明 not NULL 属性的 TIMESTAMP 列将自动声明 NULL 属性并允许 NULL 值。将这样的列赋值为 NULL 将其设置为 NULL,而不是当前的时间戳。

3、用 NOT NULL 属性声明的 TIMESTAMP 列不允许 NULL 值。对于为这样的列指定 NULL 的插入,如果启用了严格的 SQL 模式,则结果是单行插入错误,如果禁用了严格的 SQL 模式,则会插入'0000-00-00 00:00:00'。在任何情况下,将列赋值为 NULL 都不会将其设置为当前时间戳。

4、使用 NOT NULL 属性显式声明且没有显式 DEFAULT 属性的 TIMESTAMP 列被视为没有默认值。对于没有为这样的列指定显式值的插入行,结果取决于 SQL 模式。如果启用了严格 SQL 模式,则会出现错误。如果没有启用严格的 SQL 模式,则使用隐式默认值'0000-00-00 00:00:00'声明列,并出现警告。这类似于 MySQL 处理其他时间类型(如 DATETIME)的方式。

5、没有时间戳列被自动声明为默认的 CURRENT_TIMESTAMP 或 ON UPDATE CURRENT_TIMESTAMP 属性。这些属性必须显式指定。

6、表中的第一个 TIMESTAMP 列与第一个后面的 TIMESTAMP 列的处理方式没有区别。

greatsql>SET explicit_defaults_for_timestamp=OFF;Query OK, 0 rows affected, 1 warning (0.00 sec)greatsql>CREATE TABLE t1( time1 timestamp, time2 timestamp null, time3 timestamp default '2025-01-01 00:00:00', name varchar(100) );Query OK, 0 rows affected (0.03 sec)greatsql>INSERT INTO t1  (`time1`, `time2`, `time3`, name) VALUES (null, null, null, 't1');Query OK, 1 row affected (0.02 sec)greatsql>SELECT  * FROM t1;+---------------------+-------+---------------------+------+| time1               | time2 | time3               | name |+---------------------+-------+---------------------+------+| 2025-07-08 13:53:58 | NULL  | 2025-07-08 13:53:58 | t1   |+---------------------+-------+---------------------+------+1 row in set (0.00 sec)greatsql>SHOW CREATE TABLE t1\G*************************** 1. row ***************************       Table: t1Create Table: CREATE TABLE `t1` (  `time1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  `time2` timestamp NULL DEFAULT NULL,  `time3` timestamp NOT NULL DEFAULT '2025-01-01 00:00:00',  `name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci1 row in set (0.00 sec)greatsql>SET explicit_defaults_for_timestamp=ON;Query OK, 0 rows affected (0.00 sec)
greatsql>CREATE TABLE t2( time1 timestamp, time2 timestamp null, time3 timestamp default '2025-01-01 00:00:00', name varchar(100) );Query OK, 0 rows affected (0.02 sec)
greatsql>INSERT INTO t2 (`time1`, `time2`, `time3`, name) VALUES (null, null, null, 't2');Query OK, 1 row affected (0.01 sec)
greatsql>SELECT * FROM t2;+-------+-------+-------+------+| time1 | time2 | time3 | name |+-------+-------+-------+------+| NULL | NULL | NULL | t2 |+-------+-------+-------+------+1 row in set (0.00 sec)
greatsql>SHOW CREATE TABLE t2\G*************************** 1. row *************************** Table: t2Create Table: CREATE TABLE `t2` ( `time1` timestamp NULL DEFAULT NULL, `time2` timestamp NULL DEFAULT NULL, `time3` timestamp NULL DEFAULT '2025-01-01 00:00:00', `name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci1 row in set (0.00 sec)
复制代码

可以看到,在 explicit_defaults_for_timestamp 等于 OFF 的时候,不仅影响写入,还会影响表结构。 time1 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 创建表时该字段自动增加了 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 虽然字段类型是 timestamp not nul ,但可以插入 null,数据写入后变为了 CURRENT TIMESTAMP.

解决方法

问题原因

1、explicit_defaults_for_timestamp参数在不同的数据库版本中,默认值不一样。该参数可能导致在低版本的 MySQL 中能执行的语句,在高版本的 MySQL 中不能执行。

潜在影响

MySQL 5.7 升级到 MySQL 8.0 后,某些 SQL 语句执行可能会报错。

解决方法

1、设置explicit_defaults_for_timestamp=OFF,使该值和 MySQL 5.7 一致

该参数为全局变量,修改后会影响所有 timestamp 字段的处理逻辑(如自动添加 NOT NULL 和默认值),可能引发其他表的兼容性问题,建议仅在全面评估后临时使用,建议优先调整表结构或 SQL 语句。

2、修改表结构

调整字段定义为timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,从根源避免 NULL 插入冲突;

3、修改 SQL 语句:将字段插入的 null 值改为 CURRENT_TIMESTAMP。

INSERT INTO `t_interface`       (`user_id`, `department_id`, `update_time`, `is_deleted`) VALUES (9, 18, CURRENT_TIMESTAMP, 0);
复制代码

MySQL 5.7 升级到 MySQL 8.0.x/GreatSQL 8.0.x 的一些关键注意事项

从 5.7 版本升级到 8.0,有以下相关注意事项,请认真核对是否产生冲突或不兼容:

  1. 最好是先升级到 5.7.x 的最新版本,再升级到 8.0.x 的最新版本,不要从 5.7 的小版本直接升级到 8.0,尤其是非 GA 的版本。

  2. 在 8.0 中,除了 general_log 和 slow_log 之外,其他所有元数据的字典数据都存储在 InnoDB 引擎表中,不再采用 MyISAM 引擎表存储。

  3. 在 8.0 中,默认采用 caching_sha2_password 密码插件,这可能导致部分版本较早的连接驱动、连接客户端无法连接 8.0 的服务端,也需要同步升级。

  4. 在 8.0 中,默认采用 utf8mb4 字符集,而 5.7 版本默认字符集是 utf8(也是 utf8mb3),在做数据迁移时要注意前后对照校验。

  5. 在 8.0 中,启动时务必先设定好 lower_case_table_names 选项值,且实例启动后不可再更改,在个别不区分大小写的旧系统中迁移时要特别谨慎。

  6. 在 8.0 中,参数explicit_defaults_for_timestamp默认值为 ON,这可能会影响 timestamp 类型字段的默认行为。

  7. 在 8.0 中,默认启用event_scheduler,建议在主从复制或 MGR 中,在所有从节点中都关闭它。

  8. 在 8.0 中,分组查询GROUP BY的结果不再默认进行排序,需要显式加上ORDER BY才行。

  9. 新增保留字、关键字,详情请见:2.6 保留字、关键字

  10. 除 InnoDB、NDB 外,其他引擎不再支持表分区。

  11. SQL Mode 不再支持 NO_AUTO_CREATE_USER,也就是不能直接利用 GRANT 创建新用户并授权,需要先 CREATE USER 创建用户,再授权。

  12. 部分参数选项不再支持,例如:innodb_locks_unsafe_for_binlogold_passwords, query cache 相关参数等。

  13. 部分功能、函数不再支持,例如:query cachePASSWORD()ENCODE()DECODE()ENCRYPT()等。

参考文档

https://greatsql.cn/docs/8.0.32-26/7-migrate-and-upgrade/1-upgrade-to-greatsql8.html

https://dev.mysql.com/doc/refman/5.7/en/server-system-ariables.html#sysvar_explicit_defaults_for_timestamp

https://dev.mysql.com/doc/refman/8.0/en/server-system-ariables.html#sysvar_explicit_defaults_for_timestamp

发布于: 刚刚阅读数: 2
用户头像

GreatSQL

关注

GreatSQL社区 2023-01-31 加入

GreatSQL是由万里数据库维护的MySQL分支,专注于提升MGR可靠性及性能,支持InnoDB并行查询特性,是适用于金融级应用的MySQL分支版本。 社区:https://greatsql.cn/ Gitee: https://gitee.com/GreatSQL/GreatSQL

评论

发布
暂无评论
MySQL迁移至GreatSQL后,timestamp字段插入报错解析_GreatSQL_InfoQ写作社区