現(xiàn)象
插入時(shí)間報(bào)錯(cuò)：
- mysql 中
ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 00:01:00' for column 'agent_start_time' at row 1
- TIDB 中
ERROR 1292 (22007): invalid time format: '{1970 1 1 0 1 0 0}'
原因
TIMESTAMP 包含了日期和時(shí)間部分伏伯，值的范圍是UTC時(shí)間 '1970-01-01 00:00:01' 到 '2038-01-19 03:14:07'
- 當(dāng)插入時(shí)間
1970-01-01 00:01:00報(bào)錯(cuò)的原因：
1.sql_mode 包涵了STRICT_TRANS_TABLES：嚴(yán)格模式，非法數(shù)據(jù)值被拒絕
2.系統(tǒng)時(shí)區(qū)非 UTC 捌袜，time_zone 為 system或者為 '+8:00'
解決方案
- 1.修改 sql_mode ,刪除 STRICT_TRANS_TABLES 模式说搅，但是會(huì)改變數(shù)據(jù)為默認(rèn)
- 2.修改 linux 系統(tǒng)時(shí)區(qū)或者設(shè)置 time_zone 為'+0:00'，可以保留原數(shù)據(jù)
測(cè)試
- MySQL 中: 修改 sql_mode || time_zone
(root@localhost) [test1]>select @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.00 sec)
(root@localhost) [test1]>CREATE TABLE `b` (
-> `id` int(11) DEFAULT NULL,
-> `agent_start_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected (0.01 sec)
(root@localhost) [test1]>show variables like '%time_zone%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | CST |
| time_zone | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)
(root@localhost) [test1]>insert into b values(2,"1970-01-01 00:01:00");
ERROR 1292 (22007): Incorrect datetime value: '1970-01-01 00:01:00' for column 'agent_start_time' at row 1
(root@localhost) [test1]>set sql_mode='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [test1]>insert into b values(2,"1970-01-01 00:01:00");
Query OK, 1 row affected, 1 warning (0.00 sec)
(root@localhost) [test1]>select * from b;
+------+---------------------+
| id | agent_start_time |
+------+---------------------+
| 2 | 0000-00-00 00:00:00 |
+------+---------------------+
1 row in set (0.00 sec)
(root@localhost) [test1]>set sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected, 1 warning (0.00 sec)
(root@localhost) [test1]>set time_zone='+0:00';
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [test1]>insert into b values(2,"1970-01-01 00:01:00");
Query OK, 1 row affected (0.00 sec)
(root@localhost) [test1]>select * from b;
+------+---------------------+
| id | agent_start_time |
+------+---------------------+
| 2 | 0000-00-00 00:00:00 |
| 2 | 1970-01-01 00:01:00 |
+------+---------------------+
2 rows in set (0.00 sec)
- TiDB: 修改 sql_mode || time_zone
(root@10.0.1.8) [test1]>select @@sql_mode;
+--------------------------------------------+
| @@sql_mode |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+
1 row in set (0.00 sec)
(root@10.0.1.8) [test1]>CREATE TABLE `b` (
-> `id` int(11) DEFAULT NULL,
-> `agent_start_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected (0.15 sec)
(root@10.0.1.8) [test1]>insert into b values(2,"1970-01-01 00:01:00");
ERROR 1292 (22007): invalid time format: '{1970 1 1 0 1 0 0}'
(root@10.0.1.8) [test1]>set sql_mode='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)
(root@10.0.1.8) [test1]>insert into b values(2,"1970-01-01 00:01:00");
Query OK, 1 row affected, 1 warning (0.14 sec)
(root@10.0.1.8) [test1]>select * from b;
+------+---------------------+
| id | agent_start_time |
+------+---------------------+
| 2 | 0000-00-00 00:00:00 |
+------+---------------------+
1 row in set (0.01 sec)
(root@10.0.1.8) [test1]>set sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)
(root@10.0.1.8) [test1]>set time_zone='+0:00';
Query OK, 0 rows affected (0.00 sec)
(root@10.0.1.8) [test1]>insert into b values(2,"1970-01-01 00:01:00");
Query OK, 1 row affected (0.01 sec)
(root@10.0.1.8) [test1]>select * from b;
+------+---------------------+
| id | agent_start_time |
+------+---------------------+
| 2 | 0000-00-00 00:00:00 |
| 2 | 1970-01-01 00:01:00 |
+------+---------------------+
2 rows in set (0.00 sec)
