為什么MySQL 使用timestamp可以無視時(shí)區(qū)問題.
之前一直有過疑惑為什么MySQL數(shù)據(jù)庫存timestamp可以無視時(shí)區(qū)問題.
在業(yè)務(wù)中也是一直使用Laravel框架,內(nèi)置的Migration也是使用的timestamp類型字段, 也沒太關(guān)心.
開始
查看當(dāng)前數(shù)據(jù)庫時(shí)區(qū)
mysql> show variables like "%time_zone%"; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | system_time_zone | CST | | time_zone | +08:00 | +------------------+--------+ 2 rows in set (0.30 sec)
查看表結(jié)構(gòu)
mysql> desc timestamp_test; +--------------+-----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------+------+-----+---------+----------------+ | id | int | NO | PRI | NULL | auto_increment | | created_time | datetime | YES | | NULL | | | created_at | timestamp | YES | | NULL | | +--------------+-----------+------+-----+---------+----------------+ 3 rows in set (0.26 sec)
插入數(shù)據(jù)
mysql> insert into timestamp_test(created_time, created_at) values('2020-12-09 08:00:00', '2020-12-09 08:00:00');
Query OK, 1 row affected (0.22 sec)
mysql> select * from timestamp_test;
+----+---------------------+---------------------+
| id | created_time | created_at |
+----+---------------------+---------------------+
| 1 | 2020-12-09 08:00:00 | 2020-12-09 08:00:00 |
+----+---------------------+---------------------+
1 row in set (0.06 sec)
這個(gè)時(shí)間看起來是沒問題的, 那么我們嘗試修改時(shí)區(qū)再插入數(shù)據(jù)
mysql> SET time_zone = "+00:00";
Query OK, 0 rows affected (0.03 sec)
mysql> insert into timestamp_test(created_time, created_at) values('2020-12-09 08:00:00', '2020-12-09 08:00:00');
Query OK, 1 row affected (0.03 sec)
mysql> SET time_zone = "+08:00";
Query OK, 0 rows affected (0.04 sec)
這時(shí)候再查看數(shù)據(jù), 兩條插入的SQL是一樣的,但是發(fā)現(xiàn)查詢的結(jié)果是不一樣的
這兩條數(shù)據(jù)created_at的相差正好是時(shí)區(qū)的時(shí)間差
mysql> select * from timestamp_test; +----+---------------------+---------------------+ | id | created_time | created_at | +----+---------------------+---------------------+ | 1 | 2020-12-09 08:00:00 | 2020-12-09 08:00:00 | | 2 | 2020-12-09 08:00:00 | 2020-12-09 16:00:00 | +----+---------------------+---------------------+ 2 rows in set (0.06 sec)
再看一下實(shí)際存儲的時(shí)間戳, 然后我們變化時(shí)區(qū), 發(fā)現(xiàn)字段時(shí)間變化了,但是原始的時(shí)間戳數(shù)據(jù)沒變
mysql> select *, unix_timestamp(created_at) from timestamp_test; +----+---------------------+---------------------+----------------------------+ | id | created_time | created_at | unix_timestamp(created_at) | +----+---------------------+---------------------+----------------------------+ | 1 | 2020-12-09 08:00:00 | 2020-12-09 08:00:00 | 1607472000 | | 2 | 2020-12-09 08:00:00 | 2020-12-09 16:00:00 | 1607500800 | +----+---------------------+---------------------+----------------------------+ 2 rows in set (0.06 sec) mysql> SET time_zone = "+00:00"; Query OK, 0 rows affected (0.09 sec) mysql> show variables like "%time_zone%"; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | system_time_zone | CST | | time_zone | +00:00 | +------------------+--------+ 2 rows in set (0.08 sec) mysql> select *, unix_timestamp(created_at) from timestamp_test; +----+---------------------+---------------------+----------------------------+ | id | created_time | created_at | unix_timestamp(created_at) | +----+---------------------+---------------------+----------------------------+ | 1 | 2020-12-09 08:00:00 | 2020-12-09 00:00:00 | 1607472000 | | 2 | 2020-12-09 08:00:00 | 2020-12-09 08:00:00 | 1607500800 | +----+---------------------+---------------------+----------------------------+ 2 rows in set (0.18 sec)
因?yàn)檫@一切是MySQL隱式的幫我們轉(zhuǎn)換了, 讓我們不用關(guān)心時(shí)區(qū)的問題
就是數(shù)據(jù)庫實(shí)際上會保存 UTC 時(shí)間戳，寫入的時(shí)候先按 Session 時(shí)區(qū)轉(zhuǎn)成 UTC 時(shí)間，讀出的時(shí)候再按 Session 時(shí)區(qū)轉(zhuǎn)成當(dāng)前時(shí)區(qū)的時(shí)間，這些轉(zhuǎn)換都是透明的
- 假如我們在正八區(qū)存儲了
2020-12-09 08:00:00時(shí)間的一條數(shù)據(jù) - 我們在正八區(qū)取出這一條數(shù)據(jù), 時(shí)間依然是
2020-12-09 08:00:00 - 這時(shí)候我們有一臺在零時(shí)區(qū)的服務(wù)器,連接
MySQL,并且把當(dāng)前連接的時(shí)區(qū)設(shè)置為+00:00,再去查數(shù)據(jù)庫這條記錄,查到的數(shù)據(jù)是:2020-12-09 00:00:00, 正好對應(yīng)零時(shí)區(qū)的時(shí)間,這樣子我們就不用考慮時(shí)區(qū)的問題.
以上就是為什么MySQL timestamp可以無視時(shí)區(qū)問題.的詳細(xì)內(nèi)容，更多關(guān)于MySQL timestamp無視時(shí)區(qū)的資料請關(guān)注本站其它相關(guān)文章！
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。
關(guān)注官方微信