MySQL 服务器维护多个时区设置：MySQL Server maintains several time zone settings:

会话时区设置会影响时区敏感的时间值的显示和存储。这包括诸如 NOW() 或 CURTIME() 这样的函数显示的值，以及存储在 TIMESTAMP 列中并从中检索的值。TIMESTAMP 列的值在存储时会从会话时区转换为 UTC，在检索时会从 UTC 转换为会话时区。The session time zone setting affects display and storage of time values that are zone-sensitive. This includes the values displayed by functions such as NOW() or CURTIME(), and values stored in and retrieved from TIMESTAMP columns. Values for TIMESTAMP columns are converted from the session time zone to UTC for storage, and from UTC to the session time zone for retrieval.

会话时区设置不会影响诸如 UTC_TIMESTAMP() 之类函数显示的值，也不会影响 DATE、TIME 或 DATETIME 列中的值。这些数据类型中的值也不会以 UTC 存储；时区仅在从 TIMESTAMP 值转换时才适用。如果您希望对 DATE、TIME 或 DATETIME 值进行基于本地的运算，请先将它们转换为 UTC，执行运算，然后再转换回本地时间。The session time zone setting does not affect values displayed by functions such as UTC_TIMESTAMP() or values in DATE, TIME, or DATETIME columns. Nor are values in those data types stored in UTC; the time zone applies for them only when converting from TIMESTAMP values. If you want locale-specific arithmetic for DATE, TIME, or DATETIME values, convert them to UTC, perform the arithmetic, and then convert back.

当前的全局和会话时区值可以这样获取：The current global and session time zone values can be retrieved like this:

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

timezone 值可以以几种格式给出，且不区分大小写：timezone values can be given in several formats, none of which are case-sensitive:

mysql 系统模式中存在几个表用于存储时区信息（参见 7.3 节，“mysql 系统模式”）。MySQL 安装程序会创建时区表，但不会加载它们。要手动加载，请使用以下说明。Several tables in the mysql system schema exist to store time zone information (see Section 7.3, “The mysql System Schema”). The MySQL installation procedure creates the time zone tables, but does not load them. To do so manually, use the following instructions.

如果您的系统有自己的 zoneinfo 数据库（描述时区的文件集合），请使用 mysql_tzinfo_to_sql 程序来加载时区表。此类系统的例子有 Linux、macOS、FreeBSD 和 Solaris。这些文件的一个可能位置是 /usr/share/zoneinfo 目录。如果您的系统没有 zoneinfo 数据库，您可以使用可下载的包，如本节后面所述。If your system has its own zoneinfo database (the set of files describing time zones), use the mysql_tzinfo_to_sql program to load the time zone tables. Examples of such systems are Linux, macOS, FreeBSD, and Solaris. One likely location for these files is the /usr/share/zoneinfo directory. If your system has no zoneinfo database, you can use a downloadable package, as described later in this section.

要从命令行加载时区表，请将 zoneinfo 目录路径名传递给 mysql_tzinfo_to_sql，并将输出发送到 mysql 程序。例如：To load the time zone tables from the command line, pass the zoneinfo directory path name to mysql_tzinfo_to_sql and send the output into the mysql program. For example:

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

这里显示的 mysql 命令假定您使用如 root 这样的帐户连接到服务器，该帐户具有修改 mysql 系统模式中表的权限。根据需要调整连接参数。The mysql command shown here assumes that you connect to the server using an account such as root that has privileges for modifying tables in the mysql system schema. Adjust the connection parameters as required.

mysql_tzinfo_to_sql 读取系统的时区文件并从中生成 SQL 语句。mysql 处理这些语句以加载时区表。mysql_tzinfo_to_sql reads your system's time zone files and generates SQL statements from them. mysql processes those statements to load the time zone tables.

mysql_tzinfo_to_sql 也可以用于加载单个时区文件或生成闰秒信息：mysql_tzinfo_to_sql also can be used to load a single time zone file or generate leap second information:

运行mysql_tzinfo_to_sql后，重启服务器以确保不继续使用任何先前缓存的时区数据。After running mysql_tzinfo_to_sql, restart the server so that it does not continue to use any previously cached time zone data.

如果您的系统没有 zoneinfo 数据库（例如 Windows），您可以使用包含 SQL 语句的包，该包可以从 MySQL 开发者专区下载：If your system has no zoneinfo database (for example, Windows), you can use a package containing SQL statements that is available for download at the MySQL Developer Zone:

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

要使用您已下载的 SQL 语句时区包，请解压它，然后将解压后的文件内容加载到时区表中：To use an SQL-statement time zone package that you have downloaded, unpack it, then load the unpacked file contents into the time zone tables:

mysql -u root -p mysql < file_name

然后重启服务器。Then restart the server.

当时区规则改变时，使用旧规则的应用程序就会过时。为了保持最新，有必要确保您的系统使用当前的时区信息。对于 MySQL，有多个因素需要考虑以保持最新：When time zone rules change, applications that use the old rules become out of date. To stay current, it is necessary to make sure that your system uses current time zone information is used. For MySQL, there are multiple factors to consider in staying current:

如果您不确定命名时区是否可用，无论是用作服务器的时区设置还是由设置自己时区的客户端使用，请检查您的时区表是否为空。以下查询可确定包含时区名称的表是否有任何行：If you are uncertain whether named time zones are available, for use either as the server's time zone setting or by clients that set their own time zone, check whether your time zone tables are empty. The following query determines whether the table that contains time zone names has any rows:

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

计数为零表示表为空。在这种情况下，目前没有应用程序使用命名时区，并且您无需更新表（除非您想启用命名时区支持）。计数大于零表示表不为空，其内容可用于命名时区支持。在这种情况下，请确保重新加载您的时区表，以便使用命名时区的应用程序可以获得正确的查询结果。A count of zero indicates that the table is empty. In this case, no applications currently are using named time zones, and you need not update the tables (unless you want to enable named time zone support). A count greater than zero indicates that the table is not empty and that its contents are available to be used for named time zone support. In this case, be sure to reload your time zone tables so that applications that use named time zones can obtain correct query results.

要检查您的 MySQL 安装是否已正确更新以适应夏令时规则的变化，请使用如下测试。示例中使用的值适用于美国在 2007 年 3 月 11 日凌晨 2 点发生的 1 小时夏令时变化。To check whether your MySQL installation is updated properly for a change in Daylight Saving Time rules, use a test like the one following. The example uses values that are appropriate for the 2007 DST 1-hour change that occurs in the United States on March 11 at 2 a.m.

测试使用以下查询：The test uses this query:

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' 时区的等效值）。The two time values indicate the times at which the DST change occurs, and the use of named time zones requires that the time zone tables be used. The desired result is that both queries return the same result (the input time, converted to the equivalent value in the 'US/Central' time zone).

在更新时区表之前，您会看到如下错误结果：Before updating the time zone tables, you see an incorrect result like this:

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

更新表之后，您应该会看到正确的结果：After updating the tables, you should see the correct result:

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

闰秒值返回的时间部分以 :59:59 结尾。这意味着像 NOW() 这样的函数在闰秒期间可能会返回相同的值连续两到三秒。字面时间值的时间部分以 :59:60 或 :59:61 结尾仍然被视为无效。Leap second values are returned with a time part that ends with :59:59. This means that a function such as NOW() can return the same value for two or three consecutive seconds during the leap second. It remains true that literal temporal values having a time part that ends with :59:60 or :59:61 are considered invalid.

如果有必要搜索在闰秒前一秒的 TIMESTAMP 值，如果使用与 'YYYY-MM-DD hh:mm:ss' 值的比较，可能会得到异常的结果。以下示例演示了这一点。它将会话时区更改为 UTC，因此内部 TIMESTAMP 值（以 UTC 表示）与显示值（应用了时区校正）之间没有差异。If it is necessary to search for TIMESTAMP values one second before the leap second, anomalous results may be obtained if you use a comparison with 'YYYY-MM-DD hh:mm:ss' values. The following example demonstrates this. It changes the session time zone to UTC so there is no difference between internal TIMESTAMP values (which are in UTC) and displayed values (which have time zone correction applied).

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 值的比较，该值已应用闰秒修正：To work around this, you can use a comparison based on the UTC value actually stored in the column, which has the leap second correction applied:

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)