MySQL迁移至GreatSQL后，timestamp字段插入报错解析
<h1 id="mysql迁移至greatsql后timestamp字段插入报错解析">MySQL迁移至GreatSQL后，timestamp字段插入报错解析</h1><h2 id="背景描述">背景描述</h2>
<p>某业务系统进行国产化适配，将MySQL<code>的数据迁移到</code> GreatSQL 后，执行 INSERT INTO ,update_time传参为空时报错，报错信息为:ERROR 1048 (23000): Column 'update_time' cannot be null ，而原来旧的MySQL环境中没有这个问题。</p>
<pre><code class="language-JavaScript">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
</code></pre>
<h2 id="问题分析">问题分析</h2>
<h3 id="1在-greatsql-进行复现">1、在 GreatSQL 进行复现</h3>
<pre><code class="language-javascript">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
</code></pre>
<p>在 GreatSQL 中，INSERT 语句确实报错了。</p>
<h3 id="2在mysql-8032中进行复现">2、在MySQL 8.0.32中进行复现</h3>
<pre><code class="language-SQL">mysql> SELECTversion();
+-----------+
| 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
</code></pre>
<p>在MySQL 8.0.32中，INSERT 语句也报错了。</p>
<h3 id="3在mysql-5730中进行复现">3、在MySQL 5.7.30中进行复现</h3>
<pre><code class="language-SQL">mysql> SELECTversion();
+------------+
| 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)
</code></pre>
<p>在 MySQL 5.7.30 中，INSERT 语句可以正常执行。</p>
<h3 id="4问题排查">4、问题排查</h3>
<p>查看表的字段定义：</p>
<p><code>update_time</code>：timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'</p>
<p>建表语句里update_time字段类型为timestamp，NOT NULL的限制，默认值为 CURRENT_TIMESTAMP。</p>
<p>字段有 NOT NULL 的限制，不让插入NULL值，理论上是正确的。但 MySQL 5.7 为什么能插入成功呢？</p>
<p>查询系统timestamp相关的系统参数</p>
<pre><code class="language-SQL">mysql> SELECTversion();
+------------+
| version()|
+------------+
| 5.7.30-log |
+------------+
1 row in set (0.00 sec)
mysql>SHOWvariables 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>SELECTversion();
+-----------+
| 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)
</code></pre>
<p>可以看到在MySQL 5.7中 explicit_defaults_for_timestamp =OFF，</p>
<p>在MySQL 8.0.32和 GreatSQL 中 explicit_defaults_for_timestamp =ON</p>
<pre><code class="language-SQL">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>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)
</code></pre>
<p>在greatsql中，设置explicit_defaults_for_timestamp =OFF后，INSERT 语句可以正常执行。</p>
<h3 id="5参数说明">5、参数说明</h3>
<p>explicit_defaults_for_timestamp 这个系统变量决定服务器是否为TIMESTAMP列中的默认值和 空值处理启用某些非标准行为。 默认情况下，MySQL5.7禁用explicit_defaults_for_timestamp， 它启用非标准行为。MySQL8.启用explicit_defaults_for_timestamp，禁用非标准行为。</p>
<p>如果explicit_defaults_for_timestamp被禁用，服务器将<strong>启用</strong>非标准行为并按如下方式处理TIMESTAMP列：</p>
<p>1、未显式声明NULL属性的TIMESTAMP列将自动声明not NULL属性。允许将这样的列赋值为NULL，并将列设置为当前时间戳。</p>
<p>2、表中的第一个TIMESTAMP列，如果没有显式地使用NULL属性或显式地使用DEFAULT或ON UPDATE属性声明，则会自动使用DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP属性声明。</p>
<p>3、第一个之后的TIMESTAMP列，如果没有显式地使用NULL属性或显式的DEFAULT属性声明，将自动声明为DEFAULT '0000-00-00 00:00:00'（“零”时间戳）。对于没有为这样的列指定显式值的插入行，将为该列分配‘0000-00-00 00:00:00’，并且不会出现警告。</p>
<p>4、根据是否启用了strict SQL模式或NO_ZERO_DATE SQL模式，默认值'0000-00-00 00:00:00'可能无效。请注意，TRADITIONAL SQL模式包括严格模式和NO_ZERO_DATE。</p>
<p>非标准行为在MySQL的未来版本中会被删除。</p>
<p>如果启用了explicit_defaults_for_timestamp，服务器将<strong>禁用</strong>非标准行为并按如下方式处理TIMESTAMP列：</p>
<p>1、不会将TIMESTAMP NULL列设置为当前时间戳。要分配当前时间戳，请将列设置为CURRENT_TIMESTAMP或NOW（）之类的同义词。</p>
<p>2、未显式声明not NULL属性的TIMESTAMP列将自动声明NULL属性并允许NULL值。将这样的列赋值为NULL将其设置为NULL，而不是当前的时间戳。</p>
<p>3、用NOT NULL属性声明的TIMESTAMP列不允许NULL值。对于为这样的列指定NULL的插入，如果启用了严格的SQL模式，则结果是单行插入错误，如果禁用了严格的SQL模式，则会插入'0000-00-00 00:00:00'。在任何情况下，将列赋值为NULL都不会将其设置为当前时间戳。</p>
<p>4、使用NOT NULL属性显式声明且没有显式DEFAULT属性的TIMESTAMP列被视为没有默认值。对于没有为这样的列指定显式值的插入行，结果取决于SQL模式。如果启用了严格SQL模式，则会出现错误。如果没有启用严格的SQL模式，则使用隐式默认值'0000-00-00 00:00:00'声明列，并出现警告。这类似于MySQL处理其他时间类型（如DATETIME）的方式。</p>
<p>5、没有时间戳列被自动声明为默认的CURRENT_TIMESTAMP或ON UPDATE CURRENT_TIMESTAMP属性。这些属性必须显式指定。</p>
<p>6、表中的第一个TIMESTAMP列与第一个后面的TIMESTAMP列的处理方式没有区别。</p>
<pre><code class="language-sql">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: t1
Create 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_ci
1 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: t2
Create 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_ci
1 row in set (0.00 sec)
</code></pre>
<p>可以看到，在 explicit_defaults_for_timestamp 等于OFF的时候，不仅影响写入,还会影响表结构。<code>time1</code> 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.</p>
<h2 id="解决方法">解决方法</h2>
<p><strong>问题原因</strong></p>
<p>1、<code>explicit_defaults_for_timestamp</code>参数在不同的数据库版本中，默认值不一样。该参数可能导致在低版本的 MySQL 中能执行的语句，在高版本的 MySQL 中不能执行。</p>
<p><strong>潜在影响</strong></p>
<p>MySQL 5.7升级到MySQL 8.0后，某些SQL语句执行可能会报错。</p>
<p><strong>解决方法</strong></p>
<p>1、设置<code>explicit_defaults_for_timestamp=OFF</code>，使该值和MySQL 5.7一致</p>
<p>该参数为全局变量，修改后会影响所有timestamp字段的处理逻辑（如自动添加NOT NULL和默认值），可能引发其他表的兼容性问题，建议仅在全面评估后临时使用，建议优先调整表结构或 SQL 语句。</p>
<p>2、修改表结构</p>
<p>调整字段定义为<code>timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP</code>，从根源避免NULL插入冲突；</p>
<p>3、修改SQL语句：将字段插入的null值改为CURRENT_TIMESTAMP。</p>
<pre><code class="language-SQL">INSERT INTO `t_interface`
(`user_id`, `department_id`, `update_time`, `is_deleted`) VALUES (9, 18, CURRENT_TIMESTAMP, 0);
</code></pre>
<h2 id="mysql-57升级到mysql-80xgreatsql-80x的一些关键注意事项">MySQL 5.7升级到MySQL 8.0.x/GreatSQL 8.0.x的一些关键注意事项</h2>
<p>从5.7版本升级到8.0，有以下相关注意事项，请认真核对是否产生冲突或不兼容：</p>
<ol>
<li>最好是先升级到5.7.x的最新版本，再升级到8.0.x的最新版本，不要从5.7的小版本直接升级到8.0，尤其是非GA的版本。</li>
<li>在8.0中，除了 <code>general_log</code> 和 <code>slow_log</code> 之外，其他所有元数据的字典数据都存储在InnoDB引擎表中，不再采用MyISAM引擎表存储。</li>
<li>在8.0中，默认采用 <code>caching_sha2_password</code> 密码插件，这可能导致部分版本较早的连接驱动、连接客户端无法连接8.0的服务端，也需要同步升级。</li>
<li>在8.0中，默认采用 <code>utf8mb4</code> 字符集，而5.7版本默认字符集是 <code>utf8</code>（也是 <code>utf8mb3</code>），在做数据迁移时要注意前后对照校验。</li>
<li>在8.0中，启动时务必先设定好 <code>lower_case_table_names</code> 选项值，且实例启动后不可再更改，在个别不区分大小写的旧系统中迁移时要特别谨慎。</li>
<li>在8.0中，参数<code>explicit_defaults_for_timestamp</code>默认值为 <code>ON</code>，这可能会影响 <code>timestamp</code> 类型字段的默认行为。</li>
<li>在8.0中，默认启用<code>event_scheduler</code>，建议在主从复制或MGR中，在所有从节点中都关闭它。</li>
<li>在8.0中，分组查询<code>GROUP BY</code>的结果不再默认进行排序，需要显式加上<code>ORDER BY</code>才行。</li>
<li>新增保留字、关键字，详情请见：2.6 保留字、关键字。</li>
<li>除InnoDB、NDB外，其他引擎不再支持表分区。</li>
<li>SQL Mode不再支持 <code>NO_AUTO_CREATE_USER</code>，也就是不能直接利用 <code>GRANT</code> 创建新用户并授权，需要先 <code>CREATE USER</code> 创建用户，再授权。</li>
<li>部分参数选项不再支持，例如：<code>innodb_locks_unsafe_for_binlog</code>, <code>old_passwords</code>, query cache相关参数等。</li>
<li>部分功能、函数不再支持，例如：<code>query cache</code>, <code>PASSWORD()</code>, <code>ENCODE()</code>, <code>DECODE()</code>, <code>ENCRYPT()</code>等。</li>
</ol>
<h2 id="参考文档">参考文档</h2>
<p>https://greatsql.cn/docs/8.0.32-26/7-migrate-and-upgrade/1-upgrade-to-greatsql8.html</p>
<p>https://dev.mysql.com/doc/refman/5.7/en/server-system-ariables.html#sysvar_explicit_defaults_for_timestamp</p>
<p>https://dev.mysql.com/doc/refman/8.0/en/server-system-ariables.html#sysvar_explicit_defaults_for_timestamp</p>
<hr>
<p>Enjoy GreatSQL 😃</p>
<h2 id="关于-greatsql">关于 GreatSQL</h2>
<p>GreatSQL是适用于金融级应用的国内自主开源数据库，具备高性能、高可靠、高易用性、高安全等多个核心特性，可以作为MySQL或Percona Server的可选替换，用于线上生产环境，且完全免费并兼容MySQL或Percona Server。</p>
<p>相关链接： GreatSQL社区 Gitee GitHub Bilibili</p>
<h2 id="greatsql社区">GreatSQL社区：</h2>
<blockquote>
<p>社区博客有奖征稿详情：https://greatsql.cn/thread-100-1-1.html</p>
</blockquote>
<p><img src="https://img2024.cnblogs.com/other/2630741/202509/2630741-20250919095339019-1675674258.png"></p>
<h2 id="技术交流群">技术交流群：</h2>
<blockquote>
<p>微信：扫码添加<code>GreatSQL社区助手</code>微信好友，发送验证信息<code>加群</code>。</p>
</blockquote>
<p><img src="https://img2024.cnblogs.com/other/2630741/202509/2630741-20250919095339440-1005998331.png"></p><br><br>
来源：https://www.cnblogs.com/greatsql/p/19100179
頁:
[1]