之前一直有過(guò)疑惑為什么MySQL數(shù)據(jù)庫(kù)存timestamp可以無(wú)視時(shí)區(qū)問(wèn)題.在業(yè)務(wù)中也是一直使用Laravel框架,內(nèi)置的Migration也是使用的timestamp類(lèi)型字段, 也沒(méi)太關(guān)心.

開(kāi)始

查看當(dāng)前數(shù)據(jù)庫(kù)時(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í)間看起來(lái)是沒(méi)問(wèn)題的, 那么我們嘗試修改時(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)查詢(xú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í)際存儲(chǔ)的時(shí)間戳, 然后我們變化時(shí)區(qū), 發(fā)現(xiàn)字段時(shí)間變化了,但是原始的時(shí)間戳數(shù)據(jù)沒(méi)變

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ū)的問(wèn)題

就是數(shù)據(jù)庫(kù)實(shí)際上會(huì)保存 UTC 時(shí)間戳，寫(xiě)入的時(shí)候先按 Session 時(shí)區(qū)轉(zhuǎn)成 UTC 時(shí)間，讀出的時(shí)候再按 Session 時(shí)區(qū)轉(zhuǎn)成當(dāng)前時(shí)區(qū)的時(shí)間，這些轉(zhuǎn)換都是透明的

假如我們?cè)谡藚^(qū)存儲(chǔ)了2020-12-09 08:00:00時(shí)間的一條數(shù)據(jù) 我們?cè)谡藚^(qū)取出這一條數(shù)據(jù), 時(shí)間依然是2020-12-09 08:00:00 這時(shí)候我們有一臺(tái)在零時(shí)區(qū)的服務(wù)器,連接MySQL,并且把當(dāng)前連接的時(shí)區(qū)設(shè)置為+00:00,再去查數(shù)據(jù)庫(kù)這條記錄,查到的數(shù)據(jù)是:2020-12-09 00:00:00, 正好對(duì)應(yīng)零時(shí)區(qū)的時(shí)間,這樣子我們就不用考慮時(shí)區(qū)的問(wèn)題.

以上就是為什么MySQL timestamp可以無(wú)視時(shí)區(qū)問(wèn)題.的詳細(xì)內(nèi)容，更多關(guān)于MySQL timestamp無(wú)視時(shí)區(qū)的資料請(qǐng)關(guān)注好吧啦網(wǎng)其它相關(guān)文章！