MySQL Server时区支持

2024-07-06 17:36:59

本文介绍MySQL维护的时区设置——如何加载命名时间支持所需的系统表，如何及时了解时区变化，以及如何启用闰秒支持。

从MySQL 8.0.19开始，插入的日期时间值也支持时区偏移。

1 时区变量

MySQL Server维护了几个时区设置：

  • 服务器系统时区。当服务器启动时，它会尝试确定主机的时区，并使用它来设置system_time_zone系统变量。
  • 要在启动时明确指定MySQL Server的系统时区，请在启动mysqld之前设置TZ环境变量。如果使用mysqld_safe启动服务器，其--timezone选项提供了另一种设置系统时区的方法。TZ和--时区的允许值取决于系统。请参阅您的操作系统文档，了解哪些值是可接受的。
  • 服务器当前时区。全局time_zone系统变量表示服务器当前运行的时区。初始time_zone值为“system”，表示服务器时区与系统时区相同。

注意：
如果设置为SYSTEM，则每个需要时区计算的MySQL函数调用都会进行系统库调用，以确定当前系统时区。此调用可能受到全局互斥的保护，从而导致争用。

初始全局服务器时区值可以在启动时使用命令行上的--default时区选项显式指定，也可以在选项文件中使用以下行：

default-time-zone='timezone'

如果您具有SYSTEM_VARIABLES_ADMIN权限（或不推荐使用的SUPER权限），则可以使用以下语句在运行时设置全局服务器时区值：

SET GLOBAL time_zone = timezone;
  • 每个会话的时区。每个连接的客户端都有自己的会话时区设置，由会话time_zone变量给定。最初，会话变量的值取自全局time_zone变量，但客户端可以使用以下语句更改自己的时区：
SET time_zone = timezone;

会话时区设置会影响区分区域的时间值的显示和存储。这包括函数（如NOW（）或CURTIME（））显示的值，以及存储在TIMESTAMP列中和从中检索的值。TIMESTAMP列的值从会话时区转换为UTC用于存储，从UTC转换为会话时区用于检索。

会话时区设置不影响UTC_TIMESTAMP（）等函数显示的值或DATE、time或DATETIME列中的值。这些数据类型中的值也不存储在UTC中；时区仅在从TIMESTAMP值转换时适用于它们。如果需要DATE、TIME或DATETIME值的特定于区域设置的算术运算，请将它们转换为UTC，执行算术运算，然后再转换回来。

当前全局和会话时区值可以这样检索：

SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;

时区值可以用几种格式给出，它们都不区分大小写：

  1. 作为值“SYSTEM”，表示服务器时区与系统时区相同。
  2. 作为一个字符串，指示与UTC的偏移量，格式为[H]H:MM，前缀为+或-，如“+10:00”、“-6:00”或“+05:30”。前导零可以可选地用于小于10的小时值；在这种情况下，MySQL在存储和检索值时会预加一个前导零。MySQL将“-00:00”或“-0:00”转换为“+00:00”。
  3. 在MySQL 8.0.19之前，此值必须在“-12:59”到“+13:00”之间（包括在内）；从MySQL 8.0.19开始，允许的范围是“-13:59”到“+14:00”，包括在内。
  4. 作为命名时区，如“欧洲/赫尔辛基”、“美国/东方”、“MET”或“UTC”。

只有当mysql数据库中的时区信息表已经创建并填充时，才能使用命名时区。否则，使用命名时区会导致错误：

mysql> SET time_zone = 'UTC';
ERROR 1298 (HY000): Unknown or incorrect time zone: 'UTC'

2 填充时区表

mysql系统模式中有几个表用于存储时区信息。MySQL安装过程会创建时区表，但不会加载它们。要手动执行此操作，请使用以下说明。

注意：
加载时区信息不一定是一次性操作，因为信息偶尔会发生变化。当发生此类更改时，使用旧规则的应用程序将过期，您可能会发现有必要重新加载时区表，以保持MySQL服务器使用的信息是最新的。请参阅保持时区更改的最新状态。

如果您的系统有自己的zoneinfo数据库（描述时区的一组文件），请使用mysql_tzinfo_to_sql程序加载时区表。这类系统的例子有Linux、macOS、FreeBSD和Solaris。这些文件的一个可能位置是/usr/share/zoneinfo目录。如果您的系统没有zoneinfo数据库，您可以使用可下载的软件包。

要从命令行加载时区表，请将zoneinfo目录路径名传递给mysql_tzinfo_To_sql，并将输出发送到mysql程序中。例如：

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

这里显示的mysql命令假设您使用root等帐户连接到服务器，该帐户具有修改mysql系统模式中的表的权限。根据需要调整连接参数。

mysql_tzinfo_to_sql读取系统的时区文件并从中生成sql语句。mysql处理这些语句以加载时区表。

mysql_tzinfo_to_sql还可以用于加载单个时区文件或生成闰秒信息：

  • 要加载与时区名称tz_name对应的单个时区文件tz_file，请调用mysql_tzinfo_To_sql，如下所示：
mysql_tzinfo_to_sql tz_file tz_name | mysql -u root -p mysql

使用这种方法，必须执行一个单独的命令来加载服务器需要了解的每个命名区域的时区文件。

  • 如果您的时区必须考虑闰秒，请初始化闰秒信息，如下所示，其中tz_file是时区文件的名称：
mysql_tzinfo_to_sql --leap tz_file | mysql -u root -p mysql

在运行mysql_tzinfo_to_sql之后，重新启动服务器，使其不再继续使用任何以前缓存的时区数据。

如果您的系统没有zoneinfo数据库（例如，Windows），您可以使用一个包含SQL语句的包，该包可在MySQL开发区下载：

https://dev.mysql.com/downloads/timezones.html

注意：

如果您的系统有zoneinfo数据库，请不要使用可下载的时区包。请改用mysql_tzinfo_to_sql实用程序。否则，您可能会导致MySQL和系统上其他应用程序在日期时间处理方面存在差异。

要使用已下载的SQL语句时区包，请对其进行解压缩，然后将解压缩后的文件内容加载到时区表中：

mysql -u root -p mysql < file_name

然后重新启动服务器。

警告：
不要使用包含MyISAM表的可下载时区包。这适用于较旧的MySQL版本。MySQL现在使用InnoDB作为时区表。尝试用MyISAM表替换它们会导致问题。

3 随时了解时区变化

当时区规则更改时，使用旧规则的应用程序将过期。要保持最新，必须确保您的系统使用当前时区信息。对于MySQL，保持最新状态需要考虑多个因素：

  • 如果MySQL服务器的时区设置为system，则操作系统时间会影响其使用的时间值。请确保您的操作系统使用的是最新的时区信息。对于大多数操作系统，最新的更新或service pack会为系统的时间变化做好准备。请查看操作系统供应商的网站，以获取解决时间更改的更新。
  • 如果将系统的/etc/localtime zone文件替换为使用与mysqld启动时有效的规则不同的规则的版本，请重新启动mysqld，使其使用更新的规则。否则，当系统更改时间时，mysqld可能不会注意到。
  • 如果您在MySQL中使用命名时区，请确保MySQL数据库中的时区表是最新的：
  1. 如果您的系统有自己的zoneinfo数据库，请在更新zoneinfo时重新加载MySQL时区表。
  2. 对于没有自己的zoneinfo数据库的系统，请查看MySQL开发区以获取更新。当有新的更新可用时，请下载并使用它来替换当前时区表的内容。

mysqld缓存它查找的时区信息，因此在更新时区表后，重新启动mysqld以确保它不会继续提供过时的时区数据。

如果您不确定命名时区是否可用（用作服务器的时区设置或由设置自己时区的客户端使用），请检查时区表是否为空。以下查询确定包含时区名称的表是否有任何行：

mysql> SELECT COUNT(*) FROM mysql.time_zone_name;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+

计数为零表示表为空。在这种情况下，当前没有任何应用程序使用命名时区，因此不需要更新表（除非您希望启用命名时区支持）。计数大于零表示该表不是空的，并且其内容可用于命名时区支持。在这种情况下，请确保重新加载时区表，以便使用命名时区的应用程序能够获得正确的查询结果。

要检查MySQL安装是否已针对夏令时规则的更改进行了正确更新，请使用以下测试。此示例使用的值适用于美国3月11日凌晨2点发生的2007夏令时1小时变化。

测试使用以下查询：

SELECT
  CONVERT_TZ('2007-03-11 2:00:00','US/Eastern','US/Central') AS time1,
  CONVERT_TZ('2007-03-11 3:00:00','US/Eastern','US/Central') AS time2;

这两个时间值表示夏令时更改发生的时间，使用命名时区需要使用时区表。所需的结果是，两个查询都返回相同的结果（输入时间，转换为“美国/中部”时区中的等效值）。

在更新时区表之前，您会看到如下错误结果：

+---------------------+---------------------+
| time1               | time2               |
+---------------------+---------------------+
| 2007-03-11 01:00:00 | 2007-03-11 02:00:00 |
+---------------------+---------------------+

更新表格后，您应该会看到正确的结果：

+---------------------+---------------------+
| time1               | time2               |
+---------------------+---------------------+
| 2007-03-11 01:00:00 | 2007-03-11 01:00:00 |
+---------------------+---------------------+

4 时区闰秒支持

闰秒值返回的时间部分以：59:59结束。这意味着像NOW（）这样的函数可以在闰秒期间连续两三秒返回相同的值。确实，时间部分以：59:60或：59:61结尾的文字时间值被视为无效。

如果有必要在闰秒前一秒搜索TIMESTAMP值，如果将其与“YYYY-MM-DD hh:MM:ss”值进行比较，则可能会获得异常结果。下面的示例演示了这一点。它将会话时区更改为UTC，因此内部TIMESTAMP值（以UTC为单位）和显示值（已应用时区校正）之间没有差异。

mysql> CREATE TABLE t1 (
         a INT,
         ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
         PRIMARY KEY (ts)
       );
Query OK, 0 rows affected (0.01 sec)

mysql> -- change to UTC
mysql> SET time_zone = '+00:00';
Query OK, 0 rows affected (0.00 sec)

mysql> -- Simulate NOW() = '2008-12-31 23:59:59'
mysql> SET timestamp = 1230767999;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 (a) VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> -- Simulate NOW() = '2008-12-31 23:59:60'
mysql> SET timestamp = 1230768000;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t1 (a) VALUES (2);
Query OK, 1 row affected (0.00 sec)

mysql> -- values differ internally but display the same
mysql> SELECT a, ts, UNIX_TIMESTAMP(ts) FROM t1;
+------+---------------------+--------------------+
| a    | ts                  | UNIX_TIMESTAMP(ts) |
+------+---------------------+--------------------+
|    1 | 2008-12-31 23:59:59 |         1230767999 |
|    2 | 2008-12-31 23:59:59 |         1230768000 |
+------+---------------------+--------------------+
2 rows in set (0.00 sec)

mysql> -- only the non-leap value matches
mysql> SELECT * FROM t1 WHERE ts = '2008-12-31 23:59:59';
+------+---------------------+
| a    | ts                  |
+------+---------------------+
|    1 | 2008-12-31 23:59:59 |
+------+---------------------+
1 row in set (0.00 sec)

mysql> -- the leap value with seconds=60 is invalid
mysql> SELECT * FROM t1 WHERE ts = '2008-12-31 23:59:60';
Empty set, 2 warnings (0.00 sec)

要解决此问题，您可以使用基于实际存储在列中的UTC值的比较，该值已应用闰秒校正：

mysql> -- selecting using UNIX_TIMESTAMP value return leap value
mysql> SELECT * FROM t1 WHERE UNIX_TIMESTAMP(ts) = 1230768000;
+------+---------------------+
| a    | ts                  |
+------+---------------------+
|    2 | 2008-12-31 23:59:59 |
+------+---------------------+
1 row in set (0.00 sec)

上一篇：【基础算法总结】分治—快排


下一篇：Spring的三种注入方式的优缺点分析