之前一直有過疑惑為什么MySQL數據庫存timestamp可以無視時區問題.在業務中也是一直使用Laravel框架,內置的Migration也是使用的timestamp類型字段, 也沒太關心.

開始

查看當前數據庫時區

mysql> show variables like '%time_zone%';+------------------+--------+| Variable_name | Value |+------------------+--------+| system_time_zone | CST || time_zone | +08:00 |+------------------+--------+2 rows in set (0.30 sec)

查看表結構

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)

插入數據

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)

這個時間看起來是沒問題的, 那么我們嘗試修改時區再插入數據

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)

這時候再查看數據, 兩條插入的SQL是一樣的,但是發現查詢的結果是不一樣的這兩條數據created_at的相差正好是時區的時間差

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)

再看一下實際存儲的時間戳, 然后我們變化時區, 發現字段時間變化了,但是原始的時間戳數據沒變

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)

因為這一切是MySQL隱式的幫我們轉換了, 讓我們不用關心時區的問題

就是數據庫實際上會保存 UTC 時間戳，寫入的時候先按 Session 時區轉成 UTC 時間，讀出的時候再按 Session 時區轉成當前時區的時間，這些轉換都是透明的

假如我們在正八區存儲了2020-12-09 08:00:00時間的一條數據 我們在正八區取出這一條數據, 時間依然是2020-12-09 08:00:00 這時候我們有一臺在零時區的服務器,連接MySQL,并且把當前連接的時區設置為+00:00,再去查數據庫這條記錄,查到的數據是:2020-12-09 00:00:00, 正好對應零時區的時間,這樣子我們就不用考慮時區的問題.

以上就是為什么MySQL timestamp可以無視時區問題.的詳細內容，更多關于MySQL timestamp無視時區的資料請關注好吧啦網其它相關文章！