快捷導(dǎo)航

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

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

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

前言

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

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

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

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

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

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

查詢當(dāng)前時間：

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

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

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

查詢當(dāng)前時間：

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

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

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

select @@time_zone;

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

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

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)建時間',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
  `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)建時間',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
  `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');

查詢表數(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';

插入測試數(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)

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

2.如何設(shè)置

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

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

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

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

3.字段類型選擇

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

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