我在读MySQL最新8.0文档的时候，想预演一下实际情况中的闰秒，结果并不顺利。甚至有MySQL文档编写人员想当然的成分存在。所以特别整理了一篇文章，以供后人节省时间。如果有理解不对的地方，还希望热心读者留言指出。
名词概念，还请搜索关键字深入了解，这里只是为了方便文章理解，仅串起它们之间的基本关系：闰秒，跳秒，leap second，GMT，UTC，UT1；unix_timestamp、POSIX时间、utc_timestamp
本文介绍MySQL如何维护时区设置，如何加载时区支持所需的系统表，如何保持当前时区更改，以及如何启用MySQL闰秒支持。
我的环境：Win7(64bit) + MySQL 8.0.27 + SQL-yog
官方文档地址：https://dev.mysql.com/doc/refman/8.0/en/time-zone-support.html
-
闰秒是什么？
-
MySQL在闰秒的时候会出现什么问题？
-
MySQL 8支持闰秒，该怎么设置？
一、闰秒
闰秒是偶尔（平均7年）出现在6月底或12月底23:59:59之后，多增加1秒，以消除原子钟测量的精确时间（称为UTC）和不精确的太阳观测时（称为UT1）之间的差异。这是由于地球自转的不规则和地球自转减慢程度的不同而有所不同。
每个UTC闰秒的插入，都是由国际地球自转服务（IERS）提前约六个月决定，以确保UTC和UT1读数之间的差值永远不会超过0.9秒。历史上一共出现了27次。互联网工程任务组织（IETF）会及时跟踪整理，可以点击查看https://www.ietf.org/timezones/data/leap-seconds.list
二、MySQL开启闰秒支持，导入官方时区数据
默认安装的MySQL没有填充时区命名记录。用以下语句查看无记录：
| mysql>SELECT COUNT(*) FROM mysql.time_zone_name; +----------+ | COUNT(*) | +----------+ | 0 | +----------+ |
官方提供了导入工具：
-
Linux可以使用该命令：
mysql_tzinfo_to_sql/usr/share/zoneinfo | mysql -u root -p mysql
-
Windows可以下载MySQL提供的最新数据：
https://dev.mysql.com/downloads/timezones.html
打开页面后下载图中标识的zip文件，以便后续设置闰秒
下载、解压\导入\重启MySQL。导入命令参考mysql -u root -p mysql < sql_file_name
这里先补充一个概念：
除了闰秒之外，全球大部分国家都曾经定义过一个叫“夏令时”制度。夏天的时候日照时间变长，可以节约能源消耗，所以人为设定某一日晚上将时间提前一小时。待夏末的时候再把时间拨回来。我国仅86年到91年实行过夏令时，而有些国家现在还坚持着夏令时制度。这可是每年阶段性的“闰3600秒”！
所以我们可以清楚一件事：闰秒与否、是否执行夏令时等时间制度，是根据不同时区、不同国家/地区，多个维度共同作用，并非全球统一且永远不变的。
所以，我在本文第四部分，会分析刚刚MySQL提供的2021最新时区文件。
三、官方文档——支持时区闰秒【查看英文原版：5.1.15MySQL Server Time Zone Support】
根据以下用例，我无法得到最后的黄底结果！难道是官方文档有误？先来逐字分析
| mysql>CREATE TABLE t1 ( a INT, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARYKEY(ts) ); Query OK, 0 rows affected (0.01 sec) -- 这里创建新表，ts字段是默认当前时间戳。之所以使用TimeStamp类型，是因为该类型在你更改时区的时候会自行随之改动。 mysql>-- change to UTC -- 因为闰秒公告是以UTC+00:00时区发布的，为了后续select显示时保持一致，我们先修改当前Session的time_zone与其一致。不修改也可以，那么则会按我们+08:00显示 mysql>SET time_zone ='+00:00'; Query OK, 0 rows affected (0.00 sec) mysql>-- Simulate NOW() = '2008-12-31 23:59:59' -- 修改当前时间戳为'2008-12-31 23:59:59'，即将发生闰秒的前一秒 mysql>SET timestamp = 1230767999; Query OK, 0 rows affected (0.00 sec) -- 向新表中插入一条数据，他的ts字段会取默认值“当前时间” mysql>INSERT INTO t1 (a) VALUES(1); Query OK,插入成功1 row affected (0.00 sec) mysql>-- Simulate NOW() = '2008-12-31 23:59:60' -- 修改当前时间戳+1秒，即IERS确定的闰秒：'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) /* 如果IERS没有规定1230768000为闰秒的话， 那么这个TimeStamp值应该对于'2009-01-01 00:00:00'UTC。 但是IERS规定这个要增加的闰秒叫'2008-12-31 23:59:60'， 而'2008-12-31 23:59:60'不是TimeStamp类型的“合法范围” 所以如果你用这个字符串进行查询，MySQL不会返回任何结果报错 */ mysql>-- the leap value with seconds=60 is invalid mysql>SELECT * FROM t1 WHERE ts = '2008-12-31 23:59:60'; Emptyset,2warnings(0.00 sec) /* 我实际的显示是： 错误代码： 1525 Incorrect TIMESTAMP value: '2008-12-31 23:59:60' */ -- 那么我们应该这样查看闰秒？ 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| -- 注意这里：MySQL是用前一秒来显示闰秒 +------+---------------------+ 1 row in set (0.00 sec) 我本地显示的是2009-01-01 00:00:00 -- 让我们看看现在t1表里的情况吧： 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 | -- 对，这里希望ts显示59 +------+---------------------+--------------------+ 2 rows in set (0.00 sec) 我显示的是2009-01-01 00:00:00 | 1230768000 -- 最后，我们测试一下MySQL会不会犯错： 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>SELECT * FROM t1 WHERE ts = '2009-01-01 00:00:00'; Emptyset, (0.00 sec) -- 也是对的，并没有数据 -- a=2的那条闰秒记录呢？ 上文找一下“那么我们应该这样查看闰秒？” |
四、探索之路
虽然最终解决了黄底问题，但过程并不是很顺利。有几个关键信息要先搞清楚，我会在这一部分进行说明。着急知道如何解决的朋友，请直接看第五部分。
1. MySQL的TimeStamp类型的显示格式，严格使用POSIX格式。显示小时部分只用00~23，分部只用00~59，秒部只用00~59。那对于闰秒（60、61）的情况，MySQL的方案是以闰秒的前一秒替代显示，即是复用23:59:59。而不会显示60或者61。
2.我们根据第三部分里的用例，保持UTC+00:00时区不变，先测试没有开启闰秒配置时，2016年末的闰秒会怎样显示：
SELECT FROM_UNIXTIME(1483228799);
+---------------------+
2016-12-31 23:59:59 -- 闰秒前一秒，正常显示
+---------------------+
SELECT FROM_UNIXTIME(1483228800);
+---------------------+ -- IERS定义的闰秒，但MySQL还没设置
2017-01-01 00:00:00 -- 所以会显示次日00:00:00，正常
+---------------------+ -- 下面要想法子，让他显示成23:59:59
3. 官方下载的MySQL最新时区SQL文件，都写入了哪些表？
（1） time_zone只有两个字段：时区ID、是否使用闰秒。
（2） time_zone_leap_second保存了目前所有的27次闰秒记录。
（3） time_zone_name也是两个字段：时区ID，时区名称
（4） time_zone_transition字段包括：时区ID、时间转变类型ID、转变时间值。
（5） time_zone_transition_type字段有：时区ID、时间转变类型ID、偏移量、是否DST夏令时、时间标准缩写
可见，除了“闰秒记录表”time_zone_leap_second之外，其余表都可以由“时区ID”串联起来的。那么我们先只看这张表：
4. “闰秒记录表”time_zone_leap_second
运行如下SQL：
SET time_zone = "+00:00"; -- 为了日期展现一致，统一回0时区
SELECT t.*, FROM_UNIXTIME(Transition_time-Correction)
FROM mysql.time_zone_leap_second t;查看结果：
看到最后一列，应该很明确了：
以第一行举例，IERS决定以”1972-06-30 23:59:60”为闰秒，与其对应的MySQL的时间戳值为78796800。而MySQL只能显示到59秒，所以只要遇到大于等于78796800的时间戳，若需要显示日期格式时，就减掉Correction列的值，然后再转化成日期格式进行显示（最后一列即是如何）即达成闰秒的效果。举个例子：
78796799 小于78796800，正常显示为”1972-06-30 23:59:59”；
78796800 等于78796800，减掉Correction列的值后，正确显示为”1972-06-30 23:59:59”；
78796801 大于78796800，减掉Correction列的值后，正确显示为”1972-07-01 00:00:00”。
当开启时区支持闰秒后，MySQL会自动为我们进行这种操作。
5. time_zone和time_zone_name表的作用
既然闰秒已经记录，那么我的时区是否支持闰秒，需要查time_zone表和time_zone_name表。
SELECT * FROM mysql.time_zone tz
LEFT JOIN mysql.time_zone_name tzn
ON tz.time_zone_id = tzn.time_zone_id
WHERE NAME LIKE'%UTC%';
经过上面的查询，我们知道UTC时区支持闰秒。那么，快试试吧！
SET time_zone = "UTC"; -- 区别于SET time_zone = "+00:00";
SELECT FROM_UNIXTIME(1483228825),FROM_UNIXTIME(1483228826),
FROM_UNIXTIME(1483228827),FROM_UNIXTIME(1483228828);
你可以再试试上海：SET time_zone = "Asia/Shanghai";
~~ 成功！~~
五、解决问题
1. 导入MySQL官方Time_Zone文件
根据本文第二部分，下载好压缩文件后，解压；
导入到你的MySQL后，重启MySQL。
2. 创建测试表t2，执行测试SQL
mysql>CREATE TABLE t2 (
a INT,
ts TIMESTAMPDEFAULTCURRENT_TIMESTAMP,
PRIMARY KEY (ts)
);
QUERY OK, 0 ROWS affected (0.01 sec)
mysql>-- change to UTC
mysql>SET time_zone = "UTC"; -- 问题解决关键
QUERY OK, 0 ROWS affected (0.00 sec)
mysql>-- Simulate NOW() = '2008-12-31 23:59:59'
mysql> SELECTUNIX_TIMESTAMP('2008-12-31 23:59:59');
-- 修改当前时间戳为'2008-12-31 23:59:59'，即将发生闰秒的前一秒
mysql>SETtimestamp= 1230768022; -- 不再是理想值1230767999
QUERY OK, 0 ROWS affected (0.00 sec)
-- 向新表中插入一条数据，他的ts字段会取默认值“当前时间”
mysql>INSERTINTO t2 (a) VALUES (1);
QUERY OK, 1 ROW affected (0.00 sec)
mysql>-- Simulate NOW() = '2008-12-31 23:59:60'
mysql>SETtimestamp= 1230768023;
QUERY OK, 0 ROWS affected (0.00 sec)
mysql>INSERTINTO t2 (a) VALUES (2);
QUERY OK, 1 ROW affected (0.00 sec)
mysql>-- the leap value with seconds=60 is invalid
mysql>SELECT * FROM t1 WHERE ts = '2008-12-31 23:59:60';
错误代码： 1525
Incorrect TIMESTAMP VALUE: '2008-12-31 23:59:60'
mysql>SELECT * FROM t2 WHEREUNIX_TIMESTAMP(ts) = 1230768023;
+------+---------------------+
| a | ts |
+------+---------------------+
| 2 | 2008-12-31 23:59:59 | -- 终于正确显示闰秒了，MySQL以前闰秒前一秒进行显示
+------+---------------------+
1 ROW IN SET (0.00 sec)
mysql>-- values differ internally but display the same
mysql>SELECT a, ts, UNIX_TIMESTAMP(ts) FROM t2;
+------+---------------------+--------------------+
| a | ts | UNIX_TIMESTAMP(ts) |
+------+---------------------+--------------------+
| 1 | 2008-12-31 23:59:59 | 1230768022 |
| 2 | 2008-12-31 23:59:59 | 1230768023 | -- 对，是59
+------+---------------------+--------------------+
2 ROWS IN SET (0.00 sec)
六、背后原因
| -- MySQL全局time_zone， 当前会话Time_Zone SELECT @@global.time_zone, @@session.time_zone; +--------+-----+ | SYSTEM | UTC | +--------+-----+ -- MySQL全局time_zone是MySQL启动时取my.cnf的配置 -- 若my.cnf中没有配置，则取操作系统的time_zone。 SELECT @@system_time_zone; +----------------+ |�й���ʱ�� | -- Windows系统没有Zoneinfo库 +----------------+ |
MySQL官方的闰秒文档，吐槽三点：
-
MySQL在配置文件my.cnf没有配置时区时，默认提取windows7的时区数据发生乱码错误，致使@@global.time_zone不可知；
-
MySQL官方闰秒文档中使用的”+00:00”时区，并没有在自己提供的最新时区文件timezone_leaps.sql中提供数据支持；文档编写人员想当然的以为”+00:00”可以代表”UTC”.
-
MySQL官方闰秒文档中使用的演示TimeStamp值是理想（理论）值，即1230767999对应'2008-12-3123:59:59'，但实际生活中在此之前的23次闰秒已经实际占用了23个时间戳，应当予以说明并使用正确的值1230768023。这使我在正确导入官方下载的TimeZone数据、调整了time_zone="Asia/Shanghai"之后还是看到错误的结果.
经过测试：
阿里云RDS设置的TimeZone是CST
MySQL Docker镜像设置的是UTC
当“开启”MySQL闰秒配置后，只对以后新生成的TimeStamp类型的值起作用，而之前的TimeStamp数据需要我们自己进行对应处理。
87
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
