mysql解决时区相关问题
前言:
在使用MySQL的过程中,你可能会遇到时区相关问题,比如说时间显示错误、时区不是东八区、程序取得的时间和数据库存储的时间不一致等等问题。其实,这些问题都与数据库时区设置有关,本篇文章将从数据库参数入手,逐步介绍时区相关内容。
1.log_timestamps参数介绍
首先说明下log_timestamps参数并不影响时区,只是设置不同会影响某些日志记录的时间。该参数主要是控制errorlog、slowlog、generalog日志文件中的显示时间,但不会影响generallog和slowlog写到表(mysql.general_log,mysql.slow_log)中的显示时间。
log_timestamps是全局参数,可动态修改,默认使用UTC时区,这样会使得日志中记录的时间比北京时间慢8个小时,导致查看日志不方便。可以修改为SYSTEM变成使用系统时区。下面简单测试下该参数的作用及修改方法:
#查看参数值 mysql>showglobalvariableslike'log_timestamps'; +----------------+-------+ |Variable_name|Value| +----------------+-------+ |log_timestamps|UTC| +----------------+-------+ 1rowinset(0.00sec) #产生慢日志 mysql>selectsleep(10),now(); +-----------+---------------------+ |sleep(10)|now()| +-----------+---------------------+ |0|2020-06-2417:12:40| +-----------+---------------------+ 1rowinset(10.00sec) #慢日志文件记录内容发现时间是UTC时间 #Time:2020-06-24T09:12:50.555348Z #User@Host:root[root]@localhost[]Id:10 #Query_time:10.000354Lock_time:0.000000Rows_sent:1Rows_examined:1 SETtimestamp=1592989960; selectsleep(10),now(); #修改参数值再次测试 mysql>setgloballog_timestamps=SYSTEM; QueryOK,0rowsaffected(0.00sec) mysql>selectsleep(10),now(); +-----------+---------------------+ |sleep(10)|now()| +-----------+---------------------+ |0|2020-06-2417:13:44| +-----------+---------------------+ 1rowinset(10.00sec) #慢日志文件记录内容时间是对的 #Time:2020-06-24T17:13:54.514413+08:00 #User@Host:root[root]@localhost[]Id:10 #Query_time:10.000214Lock_time:0.000000Rows_sent:1Rows_examined:1 SETtimestamp=1592990024; selectsleep(10),now();
2.time_zone参数介绍
time_zone参数用来设置每个连接会话的时区,该参数分为全局和会话级别,可以动态修改。默认值为SYSTEM,此时使用的是全局参数system_time_zone的值,而system_time_zone默认继承自当前系统的时区,即默认情况下MySQL时区和系统时区相同。
时区设置主要影响时区敏感的时间值的显示和存储。包括一些函数(如now()、curtime())显示的值,以及存储在TIMESTAMP类型中的值,但不影响DATE、TIME和DATETIME列中的值,因为这些数据类型在存取时未进行时区转换,而TIMESTAMP类型存入数据库的实际是UTC的时间,查询显示时会根据具体的时区来显示不同的时间。
下面我们来测试下time_zone参数修改产生的影响:
#查看linux系统时间及时区 [root@centos~]#date SunJun2814:29:10CST2020 #查看MySQL当前时区、时间 mysql>showglobalvariableslike'%time_zone%'; +------------------+--------+ |Variable_name|Value| +------------------+--------+ |system_time_zone|CST| |time_zone|SYSTEM| +------------------+--------+ 2rowsinset(0.00sec) mysql>selectnow(); +---------------------+ |now()| +---------------------+ |2020-06-2814:31:12| +---------------------+ 1rowinset(0.00sec) #创建测试表、插入部分数据 mysql>CREATETABLE`time_zone_test`( ->`id`intunsignedNOTNULLAUTO_INCREMENTCOMMENT'自增主键', ->`dt_col`datetimeDEFAULTNULLCOMMENT'datetime时间', ->`ts_col`timestampDEFAULTNULLCOMMENT'timestamp时间', ->PRIMARYKEY(`id`) ->)ENGINE=InnoDBDEFAULTCHARSET=utf8COMMENT='time_zone测试表'; QueryOK,0rowsaffected,1warning(0.07sec) mysql>insertintotime_zone_test(dt_col,ts_col)values('2020-06-0117:30:00','2020-06-0117:30:00'),(now(),now()); QueryOK,2rowsaffected(0.01sec) Records:2Duplicates:0Warnings:0 mysql>select*fromtime_zone_test; +----+---------------------+---------------------+ |id|dt_col|ts_col| +----+---------------------+---------------------+ |1|2020-06-0117:30:00|2020-06-0117:30:00| |2|2020-06-2814:34:55|2020-06-2814:34:55| +----+---------------------+---------------------+ #改为UTC时区并重新连接发现timestamp存储的时间会随时区变化 mysql>setglobaltime_zone='+0:00'; QueryOK,0rowsaffected(0.00sec) mysql>settime_zone='+0:00'; QueryOK,0rowsaffected(0.00sec) mysql>showglobalvariableslike'%time_zone%'; +------------------+--------+ |Variable_name|Value| +------------------+--------+ |system_time_zone|CST| |time_zone|+00:00| +------------------+--------+ 2rowsinset(0.00sec) mysql>selectnow(); +---------------------+ |now()| +---------------------+ |2020-06-2806:36:16| +---------------------+ 1rowinset(0.00sec) mysql>select*fromtime_zone_test; +----+---------------------+---------------------+ |id|dt_col|ts_col| +----+---------------------+---------------------+ |1|2020-06-0117:30:00|2020-06-0109:30:00| |2|2020-06-2814:34:55|2020-06-2806:34:55| +----+---------------------+---------------------+ 2rowsinset(0.00sec) #改回东八时区,恢复正常 mysql>setglobaltime_zone='+8:00'; QueryOK,0rowsaffected(0.00sec) mysql>settime_zone='+8:00'; QueryOK,0rowsaffected(0.00sec) mysql>showglobalvariableslike'%time_zone%'; +------------------+--------+ |Variable_name|Value| +------------------+--------+ |system_time_zone|CST| |time_zone|+08:00| +------------------+--------+ 2rowsinset(0.00sec) mysql>selectnow(); +---------------------+ |now()| +---------------------+ |2020-06-2814:39:14| +---------------------+ 1rowinset(0.00sec) mysql>select*fromtime_zone_test; +----+---------------------+---------------------+ |id|dt_col|ts_col| +----+---------------------+---------------------+ |1|2020-06-0117:30:00|2020-06-0117:30:00| |2|2020-06-2814:34:55|2020-06-2814:34:55| +----+---------------------+---------------------+ 2rowsinset(0.00sec)
如果需要永久生效,还需写入配置文件中。例如将时区改为东八区,则需要在配置文件[mysqld]部分增加一行:default_time_zone='+8:00'。
3.时区常见问题及如何避免
时区设置不妥可能会产生各种问题,下面我们列举下几个常见的问题及解决方法:
3.1MySQL内部时间不是北京时间
遇到这类问题,首先检查下系统时间及时区是否正确,然后看下MySQL的time_zone,建议将time_zone改为'+8:00'。
3.2Java程序存取的时间与数据库中的时间相差8小时
出现此问题的原因大概率是程序时区与数据库时区不一致导致的。我们可以检查下两边的时区,如果想统一采用北京时间,则可以在jdbc连接串中增加serverTimezone=Asia/Shanghai,并且MySQL方面也可以将time_zone改为'+8:00'。
3.3程序时间与数据库时间相差13小时或14小时
如果说相差8小时不够让人惊讶,那相差13小时可能会让很多人摸不着头脑。出现这个问题的原因是JDBC与MySQL对“CST”时区协商不一致。因为CST时区是一个很混乱的时区,有四种含义:
- 美国中部时间CentralStandardTime(USA)UTC-05:00或UTC-06:00
- 澳大利亚中部时间CentralStandardTime(Australia)UTC+09:30
- 中国标准时ChinaStandardTimeUTC+08:00
- 古巴标准时CubaStandardTimeUTC-04:00
MySQL中,如果time_zone为默认的SYSTEM值,则时区会继承为系统时区CST,MySQL内部将其认为是UTC+08:00。而jdbc会将CST认为是美国中部时间,这就导致会相差13小时,如果处在冬令时还会相差14个小时。
解决此问题的方法也很简单,我们可以明确指定MySQL数据库的时区,不使用引发误解的CST,可以将time_zone改为'+8:00',同时jdbc连接串中也可以增加serverTimezone=Asia/Shanghai。
3.4如何避免出现时区问题
如何避免上述时区问题,可能你心里也有了些方法,简要总结几点如下:
- 首先保证系统时区准确。
- jdbc连接串中指定时区,并与数据库时区一致。
- time_zone参数建议设置为'+8:00',不使用容易误解的CST。
- 各环境数据库实例时区参数保持相同。
可能有的同学说了,我们数据库中time_zone参数选择的是默认的SYSTEM值,也没有发生程序时间和数据库时间不一致的问题。此时是否需要将time_zone改为'+8:00'?在这种情况下还是建议将time_zone改为'+8:00',特别是经常查询TIMESTAMP字段,因为当time_zone=system的时候,查询timestamp字段会调用系统的时区做时区转换,有全局锁__libc_lock_lock的保护,可能导致线程并发环境下系统性能受限。而改为'+8:00'则不会触发系统时区转换,使用MySQL自身转换,大大提高了性能。
总结:
读完本篇文章,你是否对数据库时区有了更深刻的认识呢。希望这篇文章对你有所帮助,特别是想了解MySQL时区相关内容时,可以拿来多读读。如果你遇到过其他时区相关问题,欢迎留言讨论。
以上就是mysql解决时区相关问题的详细内容,更多关于mysql时区相关问题的资料请关注毛票票其它相关文章!
声明:本文内容来源于网络,版权归原作者所有,内容由互联网用户自发贡献自行上传,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任。如果您发现有涉嫌版权的内容,欢迎发送邮件至:czq8825#qq.com(发邮件时,请将#更换为@)进行举报,并提供相关证据,一经查实,本站将立刻删除涉嫌侵权内容。