詳解MySQL中timestamp和datetime時(shí)區(qū)問題導(dǎo)致做DTS遇到的坑

更新時(shí)間：2021年12月06日 09:38:45 作者：KEEP_RUN

datetime和timestamp這兩種類型都是用于表示年月日時(shí)分秒格式的數(shù)據(jù)，那么他們有那么區(qū)別，本文就詳細(xì)的介紹一下MySQL timestamp和datetime坑，文中通過示例代碼介紹的非常詳細(xì)，感興趣的小伙伴們可以參考一下

MySQL中如何表示當(dāng)前時(shí)間？

其實(shí)，表達(dá)方式還是蠻多的，匯總?cè)缦拢?/p>

Data Type	“Zero” Value
`DATE`	`'0000-00-00'`
`TIME`	`'00:00:00'`
`DATETIME`	`'0000-00-00 00:00:00'`
`TIMESTAMP`	`'0000-00-00 00:00:00'`
`YEAR`	`0000`

datetime和timestamp這兩種類型都是用于表示YYYY-MM-DD HH:MM:SS 這種年月日時(shí)分秒格式的數(shù)據(jù)，但兩者還是有些許不同之處的。

結(jié)論

timestamp實(shí)際存儲(chǔ)的是1970-01-01 00:00:00 UTC到目前的秒數(shù)占4字節(jié)(時(shí)間精度為毫秒和納秒時(shí)會(huì)占用更多字節(jié))，故相當(dāng)于是帶時(shí)區(qū)的時(shí)間，通過設(shè)置會(huì)話的時(shí)區(qū)，會(huì)自動(dòng)轉(zhuǎn)換為設(shè)置的時(shí)區(qū)的時(shí)間
datetime存儲(chǔ)的就是格式化后的字符串類似'2021-12-05 13:27:53.957033'，不攜帶時(shí)區(qū)信息，在UTC和CST時(shí)區(qū)查詢到的結(jié)果是一致的，例如在CST時(shí)區(qū)寫入的'2021-12-05 13:27:53.957033'，但是在UTC時(shí)區(qū)查詢到的還是'2021-12-05 13:27:53.957033'，如果沒做時(shí)區(qū)轉(zhuǎn)換，就相當(dāng)于是直接將CST時(shí)間映射為UTC時(shí)間，但是實(shí)際上UTC時(shí)間比CST時(shí)間慢8個(gè)小時(shí)

驗(yàn)證

環(huán)境準(zhǔn)備，簡而言之就是存在一張表有timestamp字段和datetime字段，且當(dāng)前服務(wù)端為CST時(shí)區(qū)

mysql> show create table test_time\G;
*************************** 1. row ***************************
Table: test_time
Create Table: CREATE TABLE `test_time` (
  `id` int NOT NULL AUTO_INCREMENT,
  `ts` timestamp(6) NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `dt` datetime(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.01 sec)

插入一條數(shù)據(jù)，當(dāng)前CST時(shí)區(qū)下ts和dt結(jié)果相同

mysql> select * from test_time;
Empty set (0.00 sec)

mysql> insert into test_time() values();
Query OK, 1 row affected (0.00 sec)

mysql> select * from test_time;
+----+----------------------------+----------------------------+
| id | ts                         | dt                         |
+----+----------------------------+----------------------------+
|  3 | 2021-12-05 15:04:13.293949 | 2021-12-05 15:04:13.293949 |
+----+----------------------------+----------------------------+
1 row in set (0.00 sec)

將會(huì)話的時(shí)區(qū)設(shè)置為UTC時(shí)區(qū)再次查詢，ts由于從CST時(shí)區(qū)變?yōu)閁TC時(shí)區(qū)查詢到的結(jié)果比之前慢8個(gè)小時(shí)，由于dt不帶時(shí)區(qū)信息，結(jié)果不變

mysql> set time_zone='+00:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_time;
+----+----------------------------+----------------------------+
| id | ts                         | dt                         |
+----+----------------------------+----------------------------+
|  3 | 2021-12-05 07:04:13.293949 | 2021-12-05 15:04:13.293949 |
+----+----------------------------+----------------------------+
1 row in set (0.01 sec)

從剛剛insert產(chǎn)生的binlog中也有體現(xiàn)，ts在binlog中存儲(chǔ)為時(shí)間戳(從1970-01-01 00:00:00 UTC到目前的秒數(shù))相當(dāng)于帶UTC時(shí)區(qū)信息，dt為不帶時(shí)區(qū)信息，結(jié)果為格式化后的字符串2021-12-05 15:04:13.293949，主要關(guān)注倒數(shù)第4第5行，@2=1638687853.293949表示ts字段的值， @3='2021-12-05 15:04:13.293949'表示dt字段的值

[mysql %] mysqlbinlog -v --base64-output=decode-rows ./mysqlbin.000012
... ...
SET @@SESSION.GTID_NEXT= '1cf4493a-dafd-11eb-944c-4016af29c14c:1416767'/*!*/;
# at 14220
#211205 15:04:13 server id 1  end_log_pos 14308 CRC32 0x1fd913a3 Query thread_id=137 exec_time=0 error_code=0
SET TIMESTAMP=1638687853.293949/*!*/;
BEGIN
/*!*/;
# at 14308
#211205 15:04:13 server id 1  end_log_pos 14368 CRC32 0xbb8937fb Table_map: `testa`.`test_time` mapped to number 121
# at 14368
#211205 15:04:13 server id 1  end_log_pos 14423 CRC32 0x2e0a3baa Write_rows: table id 121 flags: STMT_END_F
### INSERT INTO `testa`.`test_time`
### SET
###   @1=3
###   @2=1638687853.293949
###   @3='2021-12-05 15:04:13.293949'
# at 14423
#211205 15:04:13 server id 1  end_log_pos 14454 CRC32 0x68cee280 Xid = 1416
COMMIT/*!*/;

坑

如果在做DTS相關(guān)項(xiàng)目時(shí)，使用解析MySQL binlog的開源工具，例如github.com/go-mysql-org/go-mysql，如果配置了parseTime=true會(huì)將timestamp類型字段解析為Local時(shí)間，將datetime類型解析為UTC時(shí)間，也可配置為false獲取到的就是字符串(timestamp已轉(zhuǎn)換為會(huì)話時(shí)區(qū)的時(shí)間，datetime就是binlog中原生的字符串)自己解析，如果parseTime=true且不是使用的UTC時(shí)間插入的datetime字段，理論上拿到的時(shí)間已經(jīng)不正確了，相當(dāng)于直接將CST的2021-12-05 15:04:13.293949轉(zhuǎn)換為了UTC的2021-12-05 15:04:13.293949，實(shí)際上應(yīng)該轉(zhuǎn)換為UTC的2021-12-05 07:04:13.293949才正確
如果剛好業(yè)務(wù)需求中有time.Now()獲取的Local時(shí)間和datetime類型字段比較的場景，需要注意時(shí)區(qū)問題，或者將時(shí)間都去掉時(shí)區(qū)，轉(zhuǎn)換為格式化后的字符串相比較
由于datetime本身就不帶時(shí)區(qū)信息，除了轉(zhuǎn)換UTC時(shí)間，也沒有更好的選擇，所以很坑！

到此這篇關(guān)于詳解MySQL中timestamp和datetime時(shí)區(qū)問題導(dǎo)致做DTS遇到的坑的文章就介紹到這了,更多相關(guān)MySQL timestamp和datetime坑內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: