关于时区:技术分享-MySQL一文弄懂时区timezone

作者：胡呈清

爱可生 DBA 团队成员，善于故障剖析、性能优化，集体博客：https://www.jianshu.com/u/a95…，欢送探讨。

本文起源：原创投稿

* 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

你还在被以下问题困扰吗：

MySQL 的装置标准中应该设置什么时区？

JAVA 利用读取到的工夫和北京时间差了 14 个小时，为什么？怎么解决？

曾经运行一段时间的业务，批改 MySQL 的时区会影响曾经存储的工夫类型数据吗？

迁徙数据时会有导致工夫类型数据时区谬误的可能吗？

…

看完这篇文章，你能解决下面所有的纳闷。首先出场的是和时区相干的启动参数和零碎变量。

如果要在 MySQL 启动时就指定时区，则应该应用启动参数：default-time-zone，示例：

-- 办法 1：在启动命令中增加
mysqld --default-time-zone='+08:00' &

-- 办法 2：在配置文件中增加
[mysqld]
default-time-zone='+08:00'

启动后咱们能够看到管制时区的零碎变量，其中 time_zone 变量管制时区，在 MySQL 运行时能够通过 set 命令批改（留神：不能够写在 my.cnf 中）：

-- 查看
mysql> show global variables like '%time%zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | +08:00 |
+------------------+--------+
2 rows in set (0.00 sec)

-- 批改全局时区，所有曾经创立的、新创建的 session 都会被批改
set global time_zone='+00:00';

-- 批改以后 session 的时区
set session time_zone='+00:00';

启动参数和零碎变量的可用值遵循雷同的格局：

‘SYSTEM’ 表明应用零碎工夫
绝对于 UTC 工夫的偏移，比方 ‘+08:00’ 或者 ‘-6:00’
某个时区的名字，比方 ‘Europe/Helsinki’，”Asia/Shanghai” 或 ‘UTC’，前提是曾经把时区信息导入到了 mysql 库，否则会报错。导入办法：mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -S /tmp/mysqld.sock mysql

system_time_zone 变量只有全局值没有会话值，不能动静批改，MySQL 启动时，将尝试主动确定服务器的时区，并应用它来设置 system_time_zone 零碎变量，尔后该值不变。当 time_zone=’system’ 时，就是应用的这个时区，示例中 time_zone 就是 CST，而 CST 在 RedHat 上就是东八区：

mysql> show global variables like '%time%zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+
2 rows in set (0.00 sec)

-- 能够看到在以后操作系统上 CST 就是 +08:00 时区
[root@localhost ~]# date -R
Thu, 02 Dec 2021 17:41:46 +0800
[root@localhost ~]# date
2021 年 12 月 02 日 星期四 17:41:49 CST

概括一下就两点：

1. NOW() 和 CURTIME() 零碎函数的返回值受以后 session 的时区影响

不仅是 select now()，包含 insert .. values(now())、以及字段的 DEFAULT CURRENT_TIMESTAMP 属性也受此影响：

mysql> set time_zone='+00:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select now(),CURTIME();
+---------------------+-----------+
| now()               | CURTIME() |
+---------------------+-----------+
| 2021-12-02 08:45:33 | 08:45:33  |
+---------------------+-----------+
1 row in set (0.00 sec)

mysql> set time_zone='+08:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select now(),CURTIME();
+---------------------+-----------+
| now()               | CURTIME() |
+---------------------+-----------+
| 2021-12-02 16:45:39 | 16:45:39  |
+---------------------+-----------+
1 row in set (0.00 sec)

2. timestamp 数据类型字段存储的数据受时区影响

timestamp 数据类型会存储过后 session 的时区信息，读取时会依据以后 session 的时区进行转换；而 datetime 数据类型插入的是什么值，再读取就是什么值，不受时区影响。也能够了解为曾经存储的数据是不会变的，只是 timestamp 类型数据在读取时会依据时区转换：

mysql> set time_zone='+08:00';
Query OK, 0 rows affected (0.00 sec)

mysql> create table t(ts timestamp, dt datetime);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t values('2021-12-02 16:45:39','2021-12-02 16:45:39');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2021-12-02 16:45:39 | 2021-12-02 16:45:39 |
+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> set time_zone='+00:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2021-12-02 08:45:39 | 2021-12-02 16:45:39 |
+---------------------+---------------------+
1 row in set (0.00 sec)

对于时区所有明面上的货色都在下面了，咱们后面提到的困扰就是在暗处的教训。

1. MySQL 的装置标准中应该设置什么时区？

对于国内的业务了，在 my.cnf 写入 default-time-zone='+08:00' `，其余地区和开发确认取对应时区即可。

为什么不设置为 system 呢？应用零碎工夫看起来也是个不错的抉择，比拟省事。不倡议的起因有两点：

操作系统的设置可能不归 DBA 管，万一他人没有设置正确的零碎时区呢？把后背交给他人可能会有点发凉；
多了一层零碎调用，性能有损耗。

2. JAVA 利用读取到的工夫和北京时间差了 14 个小时，为什么？怎么解决？

这通常是 JDBC 参数中没有为连贯设置时区属性（用 serverTimezone 参数指定），并且 MySQL 中没有设置全局时区，这样 MySQL 默认应用的是零碎时区，即 CST。这样一来利用与 MySQL 建设的连贯的 session time_zone 为CST，后面咱们提到 CST 在 RedHat 上是 +08:00 时区，但其实它一共能代表 4 个时区：

Central Standard Time (USA) UT-6:00 美国规范工夫
Central Standard Time (Australia) UT+9:30 澳大利亚规范工夫
China Standard Time UT+8:00 中国规范工夫
Cuba Standard Time UT-4:00 古巴规范工夫

JDBC 在解析 CST 时应用了美国规范工夫，这就会导致时区谬误。要解决也简略：一是恪守下面刚说到的标准，对 MySQL 显示的设置 ’+08:00’ 时区；二是 JDBC 设置正确的 serverTimezone。

3. 曾经运行一段时间的业务，批改 MySQL 的时区会影响曾经存储的工夫类型数据吗？

齐全不会，只会影响对 timestamp 数据类型的读取。这里不得不提一句，为啥要用 timestamp？用 datetime 不香吗，范畴更大，存储空间其实差异很小，连忙加到开发标准中吧。

4. 迁徙数据时会有导致工夫类型数据时区谬误的可能吗？

这个还真有，还是针对 timestamp 数据类型，比方应用 mysqldump 导出 csv 格局的数据，默认这种导出形式会应用 UTC 时区读取 timestamp 类型数据，这象征导入时必须手工设置 session.time_zone=’+00:00’ 能力保障工夫精确：

-- 将 test.t 导出成 csv
mysqldump -S /data/mysql/data/3306/mysqld.sock --single-transaction \
--master-data=2 -t -T /data/backup/test3 --fields-terminated-by=',' test t

-- 查看导出数据
cat /data/backup/test3/t.txt
2021-12-02 08:45:39,2021-12-02 16:45:39

如何防止？mysqldump 也提供了一个参数 --skip-tz-utc ，意思就是导出数据的那个连贯不设置 UTC 时区，应用 MySQL 的 gloobal time_zone 零碎变量值。

其实 mysqldump 导出 sql 文件时默认也是应用 UTC 时区，并且会在导出的 sql 文件头部带有 session time_zone 信息，这样能够保障导 SQL 文件导入和导出时应用雷同的时区，从而保证数据的时区正确（而导出的 csv 文件显然不能够携带此信息）。须要留神的是 --compact 参数会去掉 sql 文件的所有头信息，所以肯定要记得：--compact 参数得和 --skip-tz-utc 一起应用。

-- MySQL dump 10.13  Distrib 8.0.18, for linux-glibc2.12 (x86_64)
--
-- Host: 10.186.17.104    Database: sbtest
-- ------------------------------------------------------
...
/*!40103 SET TIME_ZONE='+00:00' */;
...

关于时区:技术分享-MySQL一文弄懂时区timezone

启动参数 & 零碎变量

时区影响了什么

论断