MySQL :: MySQL 9.0 参考手册 :: 7.1.15 MySQL 服务器时区支持

版本 9.0

8.4 当前
8.0
5.7

8.0 日语

MySQL 9.0 参考手册 / ... / MySQL 服务器时区支持

7.1.15 MySQL 服务器时区支持

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

还支持为插入的 datetime 值设置时区偏移量；有关更多信息，请参见第 13.2.2 节，“DATE、DATETIME 和 TIMESTAMP 类型”。

有关复制设置中的时区设置的信息，请参见第 19.5.1.14 节，“复制和系统函数” 和第 19.5.1.34 节，“复制和时区”。

时区变量

MySQL 服务器维护几个时区设置

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

注意

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

可以在启动时使用命令行上的 --default-time-zone 选项明确指定初始全局服务器时区值，也可以在选项文件中使用以下行
```
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;

timezone 值可以采用几种格式，这些格式都不区分大小写

作为值 'SYSTEM'，表示服务器时区与系统时区相同。
作为表示相对于 UTC 的偏移量的字符串，格式为 [H]H:MM，前面加上 + 或 -，例如 '+10:00'、'-6:00' 或 '+05:30'。对于小于 10 的小时值，可以可选地使用前导零；MySQL 在存储和检索此类值时会在前面加上前导零。MySQL 会将 '-00:00' 或 '-0:00' 转换为 '+00:00'。
此值必须在 '-13:59' 到 '+14:00'（含）的范围内。
作为命名时区，例如 'Europe/Helsinki'、'US/Eastern'、'MET' 或 'UTC'。
注意

仅当 mysql 数据库中的时区信息表已创建并填充时，才能使用命名时区。否则，使用命名时区会导致错误
```
mysql> SET time_zone = 'UTC';
ERROR 1298 (HY000): Unknown or incorrect time zone: 'UTC'
```

填充时区表

mysql 系统架构中的多个表用于存储时区信息（请参见第 7.3 节，“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 命令假定您使用具有修改 mysql 系统架构中表的权限的帐户（例如 root）连接到服务器。根据需要调整连接参数。

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.mysqlserver.cn/downloads/timezones.html

警告

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

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

mysql -u root -p mysql < file_name

然后重新启动服务器。

警告

请不要使用包含 MyISAM 表的可下载时区软件包。这适用于旧版本的 MySQL。MySQL 现在对时区表使用 InnoDB。尝试用 MyISAM 表替换它们会导致问题。

保持时区更改的最新状态

当时区规则发生变化时，使用旧规则的应用程序会变得过时。要保持最新状态，必须确保您的系统使用最新的时区信息。对于 MySQL，在保持最新状态时需要考虑多个因素

如果 MySQL 服务器的时区设置为 SYSTEM，操作系统时间会影响服务器使用的时间的价值。确保您的操作系统使用最新的时区信息。对于大多数操作系统，最新的更新或服务包会准备您的系统以适应时间更改。请查看您操作系统的供应商网站以获取解决时间更改的更新。
如果您将系统 /etc/localtime 时区文件替换为使用与 mysqld 启动时生效的规则不同的规则的版本，请重新启动 mysqld 以确保它使用更新后的规则。否则，mysqld 可能不会注意到系统何时更改了时间。
如果您在 MySQL 中使用命名时区，请确保 mysql 数据库中的时区表是最新的
- 如果您的系统有自己的 zoneinfo 数据库，请在 zoneinfo 数据库更新时重新加载 MySQL 时区表。
- 对于没有自己的 zoneinfo 数据库的系统，请查看 MySQL 开发者专区以获取更新。当有新的更新可用时，请下载并将其用于替换当前时区表的内容。
有关两种方法的说明，请参见填充时区表。 mysqld 会缓存它查找的时区信息，因此在更新时区表后，重新启动 mysqld 以确保它不会继续提供过时的时区数据。

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

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

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

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

该测试使用以下查询

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;

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

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

+---------------------+---------------------+
| 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 |
+---------------------+---------------------+

时区闰秒支持

闰秒值返回的时间部分以 :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)

PREV HOME UP NEXT