快捷導(dǎo)航

MySQL中時(shí)區(qū)參數(shù)time_zone解讀

更新時(shí)間：2025年01月22日 15:29:29 作者：Bing@DBA

MySQL時(shí)區(qū)參數(shù)time_zone用于控制系統(tǒng)函數(shù)和字段的DEFAULT CURRENT_TIMESTAMP屬性,修改時(shí)區(qū)可能會(huì)影響timestamp類(lèi)型的值,建議在MySQL配置文件中設(shè)置時(shí)區(qū)參數(shù),以確保高并發(fā)時(shí)的性能,在業(yè)務(wù)中盡量使用datetime類(lèi)型來(lái)存儲(chǔ)時(shí)間,因?yàn)槠鋾r(shí)間上限比TIMESTAMP更遠(yuǎn)

前言

MySQL 時(shí)區(qū)參數(shù) time_zone 有什么用？修改它有什么影響？

如何設(shè)置該參數(shù)，本篇文章會(huì)詳細(xì)介紹。

1.時(shí)區(qū)參數(shù)影響

time_zone 參數(shù)影響著 MySQL 系統(tǒng)函數(shù)還有字段的 DEFAULT CURRENT_TIMESTAMP 的屬性。

查詢(xún)當(dāng)前的時(shí)區(qū)，+8:00 就代表國(guó)內(nèi)的時(shí)區(qū)：

root@mysql 15:08:  [(none)]>select @@time_zone;
+-------------+
| @@time_zone |
+-------------+
| +08:00      |
+-------------+

查詢(xún)當(dāng)前時(shí)間：

root@mysql 15:09:  [(none)]>select now();
+---------------------+
| now()               |
+---------------------+
| 2024-12-12 15:09:44 |
+---------------------+

修改時(shí)區(qū)，為 UTC -8:00 美國(guó)時(shí)間：

root@mysql 15:09:  [(none)]>set global time_zone = '-08:00';
Query OK, 0 rows affected (0.00 sec)

查詢(xún)當(dāng)前時(shí)間：

root@mysql 15:09:  [(none)]>select now();
+---------------------+
| now()               |
+---------------------+
| 2024-12-11 23:09:55 |
+---------------------+

另外，需要注意的是 timestamp 類(lèi)型，會(huì)隨著 time_zone 的值產(chǎn)生變化，而 datetime 類(lèi)型則不會(huì)，請(qǐng)看下方演示。

確認(rèn)當(dāng)前 time_zone 參數(shù)值：

select @@time_zone;

+-------------+
| @@time_zone |
+-------------+
| +08:00      |
+-------------+

創(chuàng)建測(cè)試表結(jié)構(gòu)，兩張表的區(qū)別是 created_at、updated_at 分別為 datetime 和 timestamp 類(lèi)型。

CREATE TABLE `api_datetime` (
  `id` bigint(64) NOT NULL AUTO_INCREMENT,
  user varchar(10),
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時(shí)間',
  `enabled` bit(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;


CREATE TABLE `api_timestamp` (
  `id` bigint(64) NOT NULL AUTO_INCREMENT,
  user varchar(10),
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時(shí)間',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時(shí)間',
  `enabled` bit(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

模擬數(shù)據(jù)插入：

insert into api_timestamp(user, enabled) values ('+08:00', b'1');
insert into api_datetime(user, enabled) values ('+08:00', b'1');

查詢(xún)表數(shù)據(jù)：

root@mysql 16:21:  [test]>select user,created_at, updated_at from api_datetime;
+--------+---------------------+---------------------+
| user   | created_at          | updated_at          |
+--------+---------------------+---------------------+
| +08:00 | 2024-12-12 16:20:34 | 2024-12-12 16:20:34 |
+--------+---------------------+---------------------+
1 row in set (0.00 sec)

root@mysql 16:21:  [test]>select user,created_at, updated_at from api_timestamp;
+--------+---------------------+---------------------+
| user   | created_at          | updated_at          |
+--------+---------------------+---------------------+
| +08:00 | 2024-12-12 16:20:33 | 2024-12-12 16:20:33 |
+--------+---------------------+---------------------+

修改 time_zone 參數(shù)值，為 -8:00：

set global time_zone = '-8:00';

插入測(cè)試數(shù)據(jù)：

insert into api_timestamp(user, enabled) values ('-08:00', b'1');
insert into api_datetime(user, enabled) values ('-08:00', b'1');

root@mysql 16:25:  [test]>select user,created_at, updated_at from api_datetime;
+--------+---------------------+---------------------+
| user   | created_at          | updated_at          |
+--------+---------------------+---------------------+
| +08:00 | 2024-12-12 16:20:34 | 2024-12-12 16:20:34 |
| -08:00 | 2024-12-12 00:25:52 | 2024-12-12 00:25:52 |
+--------+---------------------+---------------------+
2 rows in set (0.00 sec)

root@mysql 16:25:  [test]>select user,created_at, updated_at from api_timestamp;
+--------+---------------------+---------------------+
| user   | created_at          | updated_at          |
+--------+---------------------+---------------------+
| +08:00 | 2024-12-12 00:20:33 | 2024-12-12 00:20:33 |
| -08:00 | 2024-12-12 00:25:52 | 2024-12-12 00:25:52 |
+--------+---------------------+---------------------+
2 rows in set (0.00 sec)

由上方測(cè)試，我們發(fā)現(xiàn)如果字段設(shè)置為 CURRENT_TIMESTAMP 無(wú)論是 datetime 還是 timestamp 類(lèi)型，都會(huì)隨 time_zone 參數(shù)影響，不過(guò) datetime 類(lèi)型的歷史數(shù)據(jù)不會(huì)受影響，timestamp 類(lèi)型的歷史數(shù)據(jù)，會(huì)隨著 time_zone 的調(diào)整而發(fā)生變化。

2.如何設(shè)置

推薦直接寫(xiě)在 MySQL 的配置文件中，需要重啟生效。

[mysqld]
default-time-zone='+08:00'

該參數(shù)默認(rèn)為 SYSTEM 表示該參數(shù)值，取自操作系統(tǒng)的時(shí)區(qū)設(shè)置。不過(guò)還是建議在 MySQL 參數(shù)文件中設(shè)置一下，因?yàn)椴僮飨到y(tǒng)可能可能不完全歸 DBA 管理，萬(wàn)一有人突然調(diào)整了，可能會(huì)引起線(xiàn)上問(wèn)題。

另外，如果 time_zone 使用默認(rèn)的 system 值，表示默認(rèn)使用操作系統(tǒng)的時(shí)區(qū)，則每次通過(guò)時(shí)區(qū)計(jì)算時(shí)間時(shí)，要調(diào)用操作系統(tǒng)底層系統(tǒng)函數(shù) __tz_convert()，而這個(gè)函數(shù)需要額外的加鎖操作，以確保這時(shí)操作系統(tǒng)時(shí)區(qū)沒(méi)有修改。高并發(fā)的時(shí)候會(huì)導(dǎo)致 TIMESTAMP 類(lèi)型的表和操作，性能降低。

3.字段類(lèi)型選擇

業(yè)務(wù)中盡量使用 datetime 類(lèi)型來(lái)存儲(chǔ)時(shí)間，除了歷史數(shù)據(jù)不會(huì)隨著時(shí)區(qū)發(fā)生變化外，還有一個(gè)最大值限制問(wèn)題。

TIMESTAMP 存儲(chǔ)的是 1970-01-01 00:00:00’ 到現(xiàn)在的毫秒數(shù)，TIMESTAMP 占用 4 個(gè)字節(jié)，因此其存儲(chǔ)的時(shí)間上限只能到 2038-01-19 03:14:07 已經(jīng)離現(xiàn)在不遠(yuǎn)了，是需要重視的，業(yè)務(wù)又將面臨一次類(lèi)似千年蟲(chóng)的問(wèn)題。