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

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

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

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

問題：

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

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

解決方案：

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

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

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

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

命令如下：

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

其他：

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

測(cè)試環(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ù)器的時(shí)區(qū)設(shè)置為（這是本次測(cè)試起始的系統(tǒng)時(shí)區(qū)情況）：

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

test1

測(cè)試目的：同一個(gè)會(huì)話的time_zone值變化時(shí)，插入記錄的時(shí)間顯示的“不同”；

當(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ù)庫使用時(shí)區(qū)跟system一致，即當(dāng)前系統(tǒng)時(shí)區(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)前會(huì)話的時(shí)區(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的值：原來時(shí)區(qū)是耶路撒冷（+2:00），改為“+8:00”后，時(shí)間變晚了6個(gè)小時(shí)，從九點(diǎn)變成17點(diǎn)。（分秒的差距是由于執(zhí)行間隔時(shí)間造成）
+----+----------------------------+-------------+-------------------------+
2 rows in set (0.00 sec)

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

test2

測(cè)試目的：修改會(huì)話級(jí)別和全局級(jí)別變量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)使用全局級(jí)別變量時(shí)：

會(huì)話1：

會(huì)話2：

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

分別在會(huì)話1和會(huì)話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());

查詢表記錄：

會(huì)話1：

會(huì)話2：

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

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

會(huì)話1：

會(huì)話2：

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

注意

（1）插入的記錄：

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

如下圖；

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

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

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

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

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

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

例1：

表的數(shù)據(jù)沒做任何update，在同一個(gè)會(huì)話里，修改了兩次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>