数据库

时区信息记录表|全方位认识 mysql 系统库

广告
广告

在上一期《优化器成本记录表|全方位认识 mysql 系统库》中,我们详细介绍了mysql 系统库中的优化器成本记录表,本期我们将为大家带来系列第六篇《时区信息记录表|全方位认识 mysql 系统库》,下面请跟随我们一起开始 mysql 系统库的系统学习之旅吧!

01 时区信息概述

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

  • 系统时区:当Server启动时,尝试确定主机的时区并使用它来设置Server的system_time_zone系统变量值。该变量为只读变量,此外,您还可以在Server启动时使用–timezone = timezone_name选项为mysqld_safe设置MySQL服务器的系统时区。或者在Server启动之前设置 TZ系统环境变量为timezone_name值。
  • Server的当前时区。 全局系统变量time_zone的值表示当前正在运行的Server时区,该系统变量的初始值为’SYSTEM’,表示Server时区与系统时区相同。* 如果系统变量time_zone设置为SYSTEM,则每个有时区计算需要的MySQL函数在调用时,都会调用系统库来确定当前的系统时区。 因此,此系统调用可能会受全局互斥保护,从而导致争用发生。* Server的全局当前时区可以在启动时使用–default-time-zone = timezone启动选项明确指定,也可以在my.cnf中使用default-time-zone=’timezone’ 指定,如果您具有SUPER权限,则可以使用语句SET GLOBAL time_zone = timezone; 在Server运行时设置全局Server时区值。* time_zone是全局,会话变量,可以在会话级别为每个会话单独设置自己的时区(连接时区),每个会话的time_zone值默认会继承time_zone系统变量的全局值,但每个会话可以使用SET time_zone = timezone;语句进行 修改。 要注意:当前会话的时区设置会影响对时区敏感的时间值的显示和存储。例如:NOW()或CURTIME()等函数显示的值是使用会话时区值、TIMESTAMP数据类型列中存储和检索的值使用的是会话时区值– TIMESTAMP列的值会从当前时区转换为UTC存储,查询时从UTC转换为当前时区。但当前时区设置不影响诸如UTC_TIMESTAMP()函数或DATE、TIME或DATETIME数据类型列中值的显示和存储–这些数据类型中的值不以UTC存储;如果有需要针对DATE、TIME或DATETIME显示时区影响,可以将它们的值转换为UTC,然后执行算术运算,然后再转换回去。* time_zone系统变量可以设置为SYSTEM,表示与系统时区相同,也可以设置具体的时区,例如: ‘+10:00’或’-6:00′ 表示在UTC时区的基础上,+ 10个时区或 -6个时区(与UTC时区的偏移量),实际上相当于东十区和西六区。还可以设置为具体的时区名称,例如: ‘Europe/Helsinki’, ‘US/Eastern’, or ‘MET’,但是,可设置的有效值来自mysql系统字典库的time_zone表,该表中的信息需要手工使用相应的命令插入,稍后会介绍插入方法。

如何填充时区表(mysql 系统字典库下有时区相关的表time_zone、time_zone_leap_second、time_zone_name、time_zone_transition、time_zone_transition_type,这些表是在MySQL初始化时创建,但不会加载数据到这些表中):

  • 如果您的系统具有自己的zoneinfo数据库(描述时区的文件集,通常在/usr/share/zoneinfo目录下),则可以使用mysql自带的mysql_tzinfo_to_sql程序来填充时区表。如果您的系统没有zoneinfo数据库,则可以使用本节后面所述的可下载软件包来进行填充时区表:
# 使用系统自带的时区数据集文件来填充MySQL 时区表(一次加载操作系统支持的所有时区),mysql_tzinfo_to_sql命令会读取您系统的时区文件并生成SQL语句来插入到MySQL的时区表中。注意:这种方式不会导入跳秒信息到time_zone_leap_second表中,需要单独操作
[root@localhost ~]# mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql -pletsg0
mysql: [Warning] Using a password on the command line interface can be insecure.
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.
# mysql_tzinfo_to_sql也可用于加载单个时区文件或生成闰秒信息:
## 加载单个时区文件,格式为:mysql_tzinfo_to_sql tz_file tz_name | mysql -u root mysql ,如下
[root@localhost ~]# mysql_tzinfo_to_sql /usr/share/zoneinfo/Asia/Shanghai Asia/Shanghai | mysql -u root mysql -pletsg0 
## 如果您的时区需要考虑闰秒(跳秒),命令如下,其中tz_file是您的时区文件的名称(绝对路径,要注意:导入跳秒信息的时区必须要是使用了跳秒的时区,即,在time_zone表中的use_leap_second字段为Y才有跳秒信息可导入,否则该表中的信息为空):
shell> mysql_tzinfo_to_sql --leap tz_file | mysql -u root mysql ,例如:
[root@localhost ~]# mysql_tzinfo_to_sql --leap /usr/share/zoneinfo/right/US/Arizona | mysql -u root mysql -pletsg0 
## 注意:运行mysql_tzinfo_to_sql之后,最好重新启动服务器,以便使得Server使用新的时区数据,以免它不会继续使用任何先前缓存的时区数据。
# 如果您的系统没有zoneinfo数据库(例如Windows),那么您可以使用可在MySQL Developer Zone上下载的软件包,链接如下:http://dev.mysql.com/downloads/timezones.html
## 下载完成之后导入该文件到数据库中,然后重启Server即可
shell> mysql -u root mysql <file_name
## 注意:不要使用包含MyISAM表的可下载软件包。MySQL 5.7及更高版本使用InnoDB作为时区表。尝试用MyISAM表替换它们会导致问题。如果您的系统有zoneinfo数据库,请不要自行下载软件包。改用mysql_tzinfo_to_sql工具直接导入系统中的时区数据集。否则,您可能会在MySQL和系统上的其他应用程序之间的日期时间处理方面产生差异。

   02 时区信息记录表详解 

2.1. time_zone

该表提供查询时区ID和跳秒之间的映射关系数据。如何填充时区表(mysql 系统字典库下有时区相关的表time_zone、time_zone_leap_second、time_zone_name、time_zone_transition、time_zone_transition_type,这些表是在MySQL初始化时创建,但不会加载数据到这些表中):

下面是该表中存储的信息内容(需要手工导入时区数据信息到数据库才有数据)。

root@localhost : mysql 04:51:37> select * from time_zone limit 1186,2;
+--------------+------------------+
| Time_zone_id | Use_leap_seconds |
+--------------+------------------+
| 2373 | N |
| 2375 | Y |
+--------------+------------------+
2 rows in set (0.01 sec)

表字段含义。 

  • Time_zone_id: 时区ID。
  • Use_leap_seconds: 表示该时区是否使用了跳秒(GPS原子钟时间和UTC时间之差,因为两个时间系统的秒长不一样,也就是我们所说的时间尺度不一样,最终随着时间的累积,两者之间就会差。而世界协调时为了协调人们生活中的时间,就采用了跳秒的办法来处理这种差异。目前两者之差为15秒)。

2.2. time_zone_leap_second

该表提供查询跳秒机器修正值信息,该表中的信息与time_zone_transition表中的类似,但time_zone_transition表中还记录了时区ID等信息。

下面是该表中存储的信息内容。

root@localhost : mysql 04:51:44> select * from time_zone_leap_second;
+-----------------+------------+
| Transition_time | Correction |
+-----------------+------------+
| 78796800 | 1 |
| 94694401 | 2 |
......

表字段含义。 

  • Transition_time: 跳秒的瞬变时间(表示UTC和GPS时间的差异 ?
  • Correction: 跳秒的修正值。

2.3. time_zone_name

该表提供查询时区的名称列表和时区ID的映射关系。

下面是该表 中存储的信息内容。

root@localhost : mysql 04:53:13> select * from time_zone_name limit 2;
+----------------+--------------+
| Name | Time_zone_id |
+----------------+--------------+
| Africa/Abidjan | 1 |
| Africa/Accra | 3 |
+----------------+--------------+
2 rows in set (0.00 sec)

表字段含义。 

  • Name: 时区名称,该值为time_zone系统变量的有效值之一。
  • Time_zone_id: 时区ID,该ID和表time_zone中的ID相对应。

2.4. time_zone_transition

该表提供查询时区的跳秒数据。

下面是该表中存储的信息内容。

root@localhost : mysql 04:59:54> select * from time_zone_transition limit 2;
+--------------+-----------------+--------------------+
| Time_zone_id | Transition_time | Transition_type_id |
+--------------+-----------------+--------------------+
| 1 | -1830383032 | 1 |
| 3 | -1640995148 | 2 |
+--------------+-----------------+--------------------+
2 rows in set (0.00 sec)

表字段含义。 

  • Time_zone_id: 时区ID。
  • Transition_time: 与time_zone_leap_second表中的Transition_time字段含义相同。
  • Transition_type_id: 与time_zone_transition_type表中的Transition_type_id相对应。

2.5. time_zone_transition_type

该表提供查询具体的跳秒信息以及与时区的对应数据。

下面是该表中存储的信息内容。

root@localhost : mysql 05:14:59> select * from time_zone_transition_type limit 2;
+--------------+--------------------+--------+--------+--------------+
| Time_zone_id | Transition_type_id | Offset | Is_DST | Abbreviation |
+--------------+--------------------+--------+--------+--------------+
| 1 | 0 | -968 | 0 | LMT |
| 1 | 1 | 0 | 0 | GMT |
+--------------+--------------------+--------+--------+--------------+
2 rows in set (0.00 sec)

表字段含义。 

  • Time_zone_id: 时区ID。
  • Transition_type_id: 与time_zone_transition表中的Transition_type_id值对应。
  • Offset: 与UTC时间之间的偏移量。
  • Is_DST: ?
  • Abbreviation: 某某标准时间的缩写,例如:GMT,该值为time_zone系统变量的有效值之一。

本期内容就介绍到这里,本期内容参考链接如下:

https://dev.mysql.com/doc/refman/5.7/en/time-zone-support.html

“翻过这座山,你就可以看到一片海!”。坚持阅读我们的”全方位认识 mysql 系统库”系列文章分享,你就可以系统地学完它。谢谢你的阅读,我们下期不见不散!

| 作者简介 

罗小波·沃趣科技高级数据库技术专家

IT从业多年,主要负责MySQL 产品的数据库支撑与售后二线支撑。曾参与版本发布系统、轻量级监控系统、运维管理平台、数据库管理平台的设计与编写,熟悉MySQL体系结构,Innodb存储引擎,喜好专研开源技术,多次在公开场合做过线下线上数据库专题分享,发表过多篇数据库相关的研究文章。

我还没有学会写个人说明!

漫画 | 互联网公司面试黑话图鉴:个个奥斯卡!

上一篇

Java 14 可能带来什么新特性?

下一篇

你也可能喜欢

时区信息记录表|全方位认识 mysql 系统库

长按储存图像,分享给朋友

ITPUB 每周精要将以邮件的形式发放至您的邮箱


微信扫一扫

微信扫一扫