时区信息记录表|全方位认识 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存储引擎,喜好专研开源技术,多次在公开场合做过线下线上数据库专题分享,发表过多篇数据库相关的研究文章。

Image placeholder
Royyyy
未设置
  83人点赞

没有讨论,发表一下自己的看法吧

推荐文章
【Golang+MySQL】记一次 MySQL 数据库迁移(一)

【Golang+mysql】记一次mysql数据库迁移(一)文章地址:https://github.com/stayfoo/stayfoo-hub一、准备目标: 腾讯云CVM自建mysql数据迁移到腾

MySQL 性能优化:8 种常见 SQL 错误用法!

1、LIMIT语句分页查询是最常用的场景之一,但也通常也是最容易出问题的地方。比如对于下面简单的语句,一般DBA想到的办法是在type,name,create_time字段上加组合索引。这样条件排序都

Oracle/云MySQL/MsSQL“大迁移”真相及最优方案

最近一段时间碰到一些数据迁移的项目,如:Oracle迁移到MySQL,MsSQL迁移到MySQL,云MySQL迁移到本地MySQL。对于这方面做了系统的整理。包括:迁移方案的选择、如何跳出迁移遇到的坑

一条SQL语句在MySQL中如何执行的

前两天发了一条SQL慢的原因有哪些,在那篇文章我没有说到优化器之类的,我觉得如果配合一条SQL是如何执行的,会更好,所以特地找了一篇。来源:JavaGuide  |作者:木木匠本篇文章会分析一个sql

mysql 进行update时,要更新的字段中有单引号或者双引号导致不能批量生成sql的问题

前言将数据从一张表迁移到另外一张表的过程中,通过mysql的concat方法批量生成sql时遇到了一个问题,即进行UPDATE更新操作时如果原表中的字段中包含单引号'或者双引号",那么就会生成不正确的

MySQL 亿级数据数据库优化方案测试-银行交易流水记录的查询

作者:逸宸a链接:https://www.jianshu.com/p/cbdef47fb837对MySQL的性能和亿级数据的处理方法思考,以及分库分表到底该如何做,在什么场景比较合适?比如银行交易流水

基础信息:MySQL 特性

MySQL数据库的优缺点: 关系型数据库管理系统(RDBMS):MySQL是一个典型的关系型数据库管理系统。 易用:MySQL很容易上手。只要你掌握一些简单的SQL知识,就可以构建SQL语句与My

基础信息:什么是 MySQL?

MySQL是一个开源的深受欢迎的关系型数据库管理系统(简称RDBMS)。目前排名第二,仅次于Oracle数据库。 MySQL可以免费下载,但是,还提供了几个付费版本,这些版本提供了附加功能。 顾名思义

MySQL 中 JSON 字段的使用技巧

mysql5.7.8之后开始原生支持json.在类似mongodb这种nosql数据库中,json存储数据是非常自然的,在mysql中合理的使用json,能够带来极大的便利 Json字段的使用场景 在

Ubuntu18.04 安装 MySQL 以及设置远程访问

安装MySQL sudoapt-getinstallmysql-server sudoaptisntallmysql-client sudoaptinstalllibmysqlclient-dev

MySQL 数据库操作:创建和查看数据库

数据库是数据的集合。MySQL允许我们高效地存储和检索数据库中的数据。在MySQL中,我们可以使用CREATEDATABASE语句创建数据库。但是,如果数据库已经存在,则会引发错误。为了避免该错误,我

MySQL 表结构生成 Markdown 文档 | 工具篇

背景 在实施软件工程的时候,当要将某一版本归档时,需要汇总的文档要求还是比较高的、各类文档齐全,包括项目架构、项目安装、接口等文档,而数据库表结构说明文档亦属于其一。记得很早之前想找一个可以导出MyS

MySQL 数据库操作:删除数据库

使用MySQL的DROPDATABASE命令可以很容易的删除一个数据库。数据库删除的同时,所属的数据表将一起被删除。如果删除的数据库不存在,则会引发错误。为了避免错误的发生,可以在DROPDATABA

MySQL 定时备份

1设置好crontab定时任务备份 #每天备份三次数据库 05,15,22***sh/data/script/alldatabase_back.sh&>/dev/null #每天2点备份 02***s

Laravel-Binlog 扩展(用于实时监听 MySQL 数据变更、数据同步等场景)

Laravel-Binlogv0.2.1 (该扩展当前用于我司测试环境实时同步Mysql数据变更到ElasticSearch,稳定性待测试!!哈哈哈)我司正式环境走的阿里云DTS数据订阅 基于Sw

MySQL 读后总结

一条SQL查询语句是如何执行的? Mysql基本架构示意图Mysql分为Server层和存储引擎层两部分 Server层包括连接器、查询缓存、分析器、优化器、执行器等,涵盖MySQL的大多数核心服务

MySQL 优化笔记

优化方向 SQL优化 sql优化分析 索引优化 优化数据库对象 优化表的数据类型 表拆分(水平、垂直) 反范式 使用中间表 优化mysqlserver mysql内存管理优化 log机制及优化

MySQL 优化笔记

优化方向 SQL优化 sql优化分析 索引优化 优化数据库对象 优化表的数据类型 表拆分(水平、垂直) 反范式 使用中间表 优化mysqlserver mysql内存管理优化 log机制及优化

pymysql fetchone () , fetchall () , fetchmany ()

最近在用python操作mysql数据库时,碰到了下面这两个函数,标记一下: 1.定义 1.1fetchone(): 返回单个的元组,也就是一条记录(row),如果没有结果则返回None 1.2fet

MySQL 安装和配置

MySQL安装和配置 MySQL安装 Mysql安装官网:http://www.mysql.com/ 官网下载:http://dev.mysql.com/downloads/mysql/ 官网5.5下

2019年8月数据库流行度排行:双星闪耀 MySQL 成月度最大赢家

炎炎夏日,DB-Engines的8月榜单已经发布,本月积分MySQL获得了最显著的增长,较上月增加了24分,Oracle获得了18分的增长,Oracle公司的两个王牌产品,闪耀8月。以下是前10名的榜

MySQL是怎么保证数据一致性的

在《写数据库同时发mq消息事务一致性的一种解决方案》一文的方案中把分布式事务巧妙转成了数据库事务。我们都知道关系型数据库事务能保证数据一致性,那数据库到底是怎么设计事务这一特性的呢?一、MySQL事务

《关于MySQL的一些骚操作》

概要回顾以前写的项目,发现在规范的时候,还是可以做点骚操作的。假使以后还有新的项目用到了MySQL,那么肯定是要实践一番的。为了准备,创建测试数据表(建表语句中默认使用utf8mb4以及utf8mb4

可能是全网最好的MySQL重要知识点

什么是MySQL?MySQL是一种关系型数据库,在Java企业级开发中非常常用,因为MySQL是开源免费的,并且方便扩展。阿里巴巴数据库系统也大量用到了MySQL,因此它的稳定性是有保障的。MySQL

MySQL 百万级数据量分页查询方法及其优化

作者|大神养成记原文|  http://t.cn/RnvCJnm方法1:直接使用数据库提供的SQL语句语句样式: MySQL中,可用如下方法:SELECT*FROM表名称LIMITM,N适应场景: 适