之前一直有過疑惑為什么MySQL數(shù)據(jù)庫存timestamp可以無視時區(qū)問題.
在業(yè)務中也是一直使用Laravel框架,內置的Migration也是使用的timestamp類型字段, 也沒太關心.
開始
查看當前數(shù)據(jù)庫時區(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)
查看表結構
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)
這個時間看起來是沒問題的, 那么我們嘗試修改時區(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ù)據(jù), 兩條插入的SQL是一樣的,但是發(fā)現(xiàn)查詢的結果是不一樣的
這兩條數(shù)據(jù)created_at的相差正好是時區(qū)的時間差
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)
再看一下實際存儲的時間戳, 然后我們變化時區(qū), 發(fā)現(xiàn)字段時間變化了,但是原始的時間戳數(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)
因為這一切是MySQL隱式的幫我們轉換了, 讓我們不用關心時區(qū)的問題
就是數(shù)據(jù)庫實際上會保存 UTC 時間戳，寫入的時候先按 Session 時區(qū)轉成 UTC 時間，讀出的時候再按 Session 時區(qū)轉成當前時區(qū)的時間，這些轉換都是透明的
- 假如我們在正八區(qū)存儲了
2020-12-09 08:00:00時間的一條數(shù)據(jù)
- 我們在正八區(qū)取出這一條數(shù)據(jù), 時間依然是
2020-12-09 08:00:00
- 這時候我們有一臺在零時區(qū)的服務器,連接
MySQL,并且把當前連接的時區(qū)設置為+00:00,再去查數(shù)據(jù)庫這條記錄,查到的數(shù)據(jù)是:2020-12-09 00:00:00, 正好對應零時區(qū)的時間,這樣子我們就不用考慮時區(qū)的問題.
以上就是為什么MySQL timestamp可以無視時區(qū)問題.的詳細內容，更多關于MySQL timestamp無視時區(qū)的資料請關注腳本之家其它相關文章！
您可能感興趣的文章:- MySQL timestamp的類型與時區(qū)實例詳解
- 通過實例解析MySql CURRENT_TIMESTAMP函數(shù)
- MySQL的時間差函數(shù)TIMESTAMPDIFF、DATEDIFF的用法
- MySQL的時間差函數(shù)(TIMESTAMPDIFF、DATEDIFF)、日期轉換計算函數(shù)(date_add、day、date_format、str_to_date)
- MySQL中datetime和timestamp的區(qū)別及使用詳解
- MySQL 5.6 中 TIMESTAMP有那些變化
- MySQL 5.6 中的 TIMESTAMP 和 explicit_defaults_for_timestamp 參數(shù)
- Mysql中的Datetime和Timestamp比較
- mysql 數(shù)據(jù)類型TIMESTAMP
