动态

详情 返回 返回

MySQL遷移至GreatSQL後，timestamp字段插入報錯解析 - 动态 详情

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 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)

在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: 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)

可以看到，在 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_binlog, old_passwords, query cache相關參數等。
  13. 部分功能、函數不再支持，例如：query cache, PASSWORD(), 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

Enjoy GreatSQL 😃

關於 GreatSQL

GreatSQL是適用於金融級應用的國內自主開源數據庫，具備高性能、高可靠、高易用性、高安全等多個核心特性，可以作為MySQL或Percona Server的可選替換，用於線上生產環境，且完全免費併兼容MySQL或Percona Server。

相關鏈接： GreatSQL社區 Gitee GitHub Bilibili

GreatSQL社區：

社區博客有獎徵稿詳情：https://greatsql.cn/thread-100-1-1.html

image-20230105161905827

技術交流羣：

微信：掃碼添加GreatSQL社區助手微信好友，發送驗證信息加羣。

image-20221030163217640

MySQL
点赞

Add a new 评论

Some HTML is okay.