MySQL數(shù)據(jù)時區(qū)問題以及datetime和timestamp類型存儲的差異

更新時間：2023年11月07日 09:01:06 作者：世樹

這篇文章主要介紹了MySQL數(shù)據(jù)時區(qū)問題以及datetime和timestamp類型存儲的差異,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教

MySQL數(shù)據(jù)時區(qū)問題及datetime和timestamp類型存儲的差異

問題：

查詢不同數(shù)據(jù)庫上表中記錄時間差距8小時。

昨天協(xié)助其他地區(qū)同事解決客戶查詢到不同數(shù)據(jù)中心時間差距8小時的問題。原因就是時區(qū)不同。

解決方案：

設(shè)置服務(wù)器的時區(qū)都為北京時間，即修改數(shù)據(jù)庫服務(wù)器的time_zone值為“+8:00”解決。

這個參數(shù)，可以在通過mysqld命令啟動數(shù)據(jù)庫的時候加上參數(shù) --default-time-zone=timezone來設(shè)置時區(qū)，

也可以通過my.cnf配置文件的[mysqld]標(biāo)簽里增加 default-time-zone='timezone'這一行來設(shè)置。

如果希望即時生效，也可以通過命令修改全局或者會話級別的time_zone的值：

命令如下：

修改全局time_zone的值
set global time_zone='+8:00';
或
修改當(dāng)前會話的time_zone
set time_zone='+8:00';

其他：

因?yàn)槭褂玫脑茢?shù)據(jù)庫也有在海外地區(qū)的，所以針對我們有些表的字段是timestamp類型的情況和遇到的問題，也一起整理了一下。

測試環(huán)境為 Windows MySQL 5.7.22-log。

表結(jié)構(gòu)如下：

CREATE TABLE `ee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date1` datetime(6) DEFAULT NULL,
  `date2` time(2) DEFAULT NULL,
  `date3` timestamp(3) NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

當(dāng)前數(shù)據(jù)庫服務(wù)器的時區(qū)設(shè)置為（這是本次測試起始的系統(tǒng)時區(qū)情況）：

即：system_time_zone參數(shù)值為+2:00。

test1

測試目的：同一個會話的time_zone值變化時，插入記錄的時間顯示的“不同”；

當(dāng)前數(shù)據(jù)庫time_zone參數(shù)情況如下；

root@iris>show global variables like'%time_zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone |        |
| time_zone        | SYSTEM |# 數(shù)據(jù)庫使用時區(qū)跟system一致，即當(dāng)前系統(tǒng)時區(qū)，是耶路撒冷（+2:00），等同于set time_zone='+2:00'了。
+------------------+--------+

向表iris.ee中插入數(shù)據(jù)：

INSERT INTO iris.ee(date1,date2,date3) VALUES(NOW(),NOW(),NOW());

查詢表數(shù)據(jù)：

root@iris>select * from iris.ee;
+----+----------------------------+-------------+-------------------------+
| id | date1                      | date2       | date3                   |
+----+----------------------------+-------------+-------------------------+
|  1 | 2018-12-07 09:55:52.000000 | 09:55:52.00 | 2018-12-07 09:55:52.000 |
+----+----------------------------+-------------+-------------------------+
1 row in set (0.00 sec)

修改當(dāng)前會話的時區(qū)：

root@iris>set time_zone='+8:00';
Query OK, 0 rows affected (0.00 sec)

再次插入數(shù)據(jù)，并查看表數(shù)據(jù)：

root@iris>INSERT INTO iris.ee(date1,date2,date3) VALUES(NOW(),NOW(),NOW());
Query OK, 1 row affected (0.18 sec)
 
#查看表數(shù)據(jù)
root@iris>select * from iris.ee;
+----+----------------------------+-------------+-------------------------+
| id | date1                      | date2       | date3                   |
+----+----------------------------+-------------+-------------------------+
|  1 | 2018-12-07 09:55:52.000000 | 09:55:52.00 | 2018-12-07 17:55:52.000 |
|  2 | 2018-12-07 17:57:29.000000 | 17:57:29.00 | 2018-12-07 17:57:29.000 | # 看date1的值：原來時區(qū)是耶路撒冷（+2:00），改為“+8:00”后，時間變晚了6個小時，從九點(diǎn)變成17點(diǎn)。（分秒的差距是由于執(zhí)行間隔時間造成）
+----+----------------------------+-------------+-------------------------+
2 rows in set (0.00 sec)

注意：這里修改time_zone都是會話變量，同一個會話里進(jìn)行的。

test2

測試目的：修改會話級別和全局級別變量time_zone的差異；

當(dāng)前表記錄為：

root@iris>select * from iris.ee;
+----+----------------------------+-------------+-------------------------+
| id | date1                      | date2       | date3                   |
+----+----------------------------+-------------+-------------------------+
|  1 | 2018-12-07 09:55:52.000000 | 09:55:52.00 | 2018-12-07 22:55:52.000 |
|  2 | 2018-12-07 17:57:29.000000 | 17:57:29.00 | 2018-12-07 22:57:29.000 |
|  3 | 2018-12-07 23:52:08.000000 | 23:52:08.00 | 2018-12-07 23:52:08.000 |
|  4 | 2018-12-07 18:52:31.000000 | 18:52:31.00 | 2018-12-07 23:52:31.000 |
|  5 | 2018-12-07 19:04:08.000000 | 19:04:08.00 | 2018-12-08 00:04:08.000 |
|  6 | 2018-12-07 19:04:13.000000 | 19:04:13.00 | 2018-12-08 00:04:13.000 |
|  7 | 2018-12-07 19:04:34.000000 | 19:04:34.00 | 2018-12-08 00:04:34.000 |
+----+----------------------------+-------------+-------------------------+
7 rows in set (0.00 sec)

當(dāng)使用全局級別變量時：

會話1：

會話2：

?上面兩圖顯示兩個會話當(dāng)前global time_zone都是“+8:00”。

分別在會話1和會話2中插入記錄：

session1：
INSERT INTO iris.ee(id,date1,date2,date3) VALUES(11,NOW(),NOW(),NOW());
 
session2：
INSERT INTO iris.ee(id,date1,date2,date3) VALUES(22,NOW(),NOW(),NOW());

查詢表記錄：

會話1：

會話2：

?上面兩圖表示當(dāng)前插入的記錄在兩個會話中時間顯示是（相對）一樣的（都是同一個時區(qū)、當(dāng)前實(shí)際時間--即，我看到手表上顯示的時間。）

當(dāng)使用會話級參數(shù)設(shè)置時：

會話1：

會話2：

?上面兩圖表示，當(dāng)會話1時區(qū)為“+1:00”、會話2時區(qū)為“+11:00”，相差10小時。

注意

（1）插入的記錄：

datetime類型字段，保存的時間都是當(dāng)前會話所設(shè)置的時區(qū)相應(yīng)時間點(diǎn)（即，好比我兩個會話都是修改了計算機(jī)的系統(tǒng)時間的時區(qū)，然后執(zhí)行insert。執(zhí)行命令時看到計算機(jī)上顯示的時間）。

如下圖；

而，timestamp類型字段，id=33 和id=44 的記錄，是一樣的（即，雖然我設(shè)定兩個會話所在時區(qū)不同，但是我同時(假使是同時執(zhí)行實(shí)際，我切換會話執(zhí)行，時間也就隔了12秒，看上圖)在兩個會話里insert了，此時他們換算成我所在的時區(qū)的時間都是一樣的，都跟我現(xiàn)在看到我手表的時間是一樣的。），這也是我要說的第二個注意點(diǎn)，timestamp的時區(qū)性。

（2）時間類型不同導(dǎo)致數(shù)據(jù)顯示結(jié)果的不同：

這里涉及到一個問題，timestamp字段類型有時區(qū)性，上一個注意事項(xiàng)提到的。

datetime類型的字段的記錄，記錄的都是數(shù)據(jù)插入的時候當(dāng)前會話所獲取到的time_zone相應(yīng)的時區(qū)的即時時間。就算會話或者說服務(wù)器設(shè)置的時區(qū)改變了了，表里這個字段的值也不會發(fā)生變化。

但是timestamp類型的字段的值會隨著服務(wù)器時區(qū)的變化，自動換算成相應(yīng)的時間。即在不同時區(qū)，查詢到同一個條記錄此字段的值會不一樣。

可能說的比較繞。還是不明白，可以再來看第二個例子。

例1：

表的數(shù)據(jù)沒做任何update，在同一個會話里，修改了兩次time_zone，看到的表ee里字段date3 (timestamp類型)的值變化了，date1（datetime類型）沒變。

time_zone='+0:00'

root@iris>set time_zone='+0:00';
Query OK, 0 rows affected (0.00 sec)
 
root@iris>select * from iris.ee;
+----+----------------------------+-------------+-------------------------+
| id | date1                      | date2       | date3                   |
+----+----------------------------+-------------+-------------------------+
|  1 | 2018-12-07 09:55:52.000000 | 09:55:52.00 | 2018-12-07 09:55:52.000 |
|  2 | 2018-12-07 17:57:29.000000 | 17:57:29.00 | 2018-12-07 09:57:29.000 |
+----+----------------------------+-------------+-------------------------+
2 rows in set (0.00 sec)

time_zone='+13:00'

root@iris>set time_zone='+13:00';
Query OK, 0 rows affected (0.00 sec)
 
root@iris>select * from iris.ee;
+----+----------------------------+-------------+-------------------------+
| id | date1                      | date2       | date3                   |
+----+----------------------------+-------------+-------------------------+
|  1 | 2018-12-07 09:55:52.000000 | 09:55:52.00 | 2018-12-07 22:55:52.000 |
|  2 | 2018-12-07 17:57:29.000000 | 17:57:29.00 | 2018-12-07 22:57:29.000 |
+----+----------------------------+-------------+-------------------------+
2 rows in set (0.00 sec)
root@iris>