最新消息

为什么MySQL 使用timestamp可以无视时区问题.

乐鱼网教程 2021-04-07 21:18

之前一直有过疑惑为什么MySQL数据库存timestamp可以无视时区问题.


在业务中也是一直使用Laravel框架,内置的Migration也是使用的timestamp类型字段,也没太关心.

开始


查看当前数据库时区mysql>showvariableslike"%time_zone%";+------------------+--------+|Variable_name|Value|+------------------+--------+|system_time_zone|CST||time_zone|+08:00|+------------------+--------+2rowsinset(0.30sec)查看表结构mysql>desctimestamp_test;+--------------+-----------+------+-----+---------+----------------+|Field|Type|Null|Key|Default|Extra|+--------------+-----------+------+-----+---------+----------------+|id|int|NO|PRI|NULL|auto_increment||created_time|datetime|YES||NULL|||created_at|timestamp|YES||NULL||+--------------+-----------+------+-----+---------+----------------+3rowsinset(0.26sec)插入数据mysql>insertintotimestamp_test(created_time,created_at)values('2020-12-0908:00:00','2020-12-0908:00:00');QueryOK,1rowaffected(0.22sec)mysql>select*fromtimestamp_test;+----+---------------------+---------------------+|id|created_time|created_at|+----+---------------------+---------------------+|1|2020-12-0908:00:00|2020-12-0908:00:00|+----+---------------------+---------------------+1rowinset(0.06sec)这个时间看起来是没问题的,那么我们尝试修改时区再插入数据mysql>SETtime_zone="+00:00";QueryOK,0rowsaffected(0.03sec)mysql>insertintotimestamp_test(created_time,created_at)values('2020-12-0908:00:00','2020-12-0908:00:00');QueryOK,1rowaffected(0.03sec)mysql>SETtime_zone="+08:00";QueryOK,0rowsaffected(0.04sec)这时候再查看数据,两条插入的SQL是一样的,但是发现查询的结果是不一样的


这两条数据created_at的相差正好是时区的时间差mysql>select*fromtimestamp_test;+----+---------------------+---------------------+|id|created_time|created_at|+----+---------------------+---------------------+|1|2020-12-0908:00:00|2020-12-0908:00:00||2|2020-12-0908:00:00|2020-12-0916:00:00|+----+---------------------+---------------------+2rowsinset(0.06sec)再看一下实际存储的时间戳,然后我们变化时区,发现字段时间变化了,但是原始的时间戳数据没变mysql>select*,unix_timestamp(created_at)fromtimestamp_test;+----+---------------------+---------------------+----------------------------+|id|created_time|created_at|unix_timestamp(created_at)|+----+---------------------+---------------------+----------------------------+|1|2020-12-0908:00:00|2020-12-0908:00:00|1607472000||2|2020-12-0908:00:00|2020-12-0916:00:00|1607500800|+----+---------------------+---------------------+----------------------------+2rowsinset(0.06sec)mysql>SETtime_zone="+00:00";QueryOK,0rowsaffected(0.09sec)mysql>showvariableslike"%time_zone%";+------------------+--------+|Variable_name|Value|+------------------+--------+|system_time_zone|CST||time_zone|+00:00|+------------------+--------+2rowsinset(0.08sec)mysql>select*,unix_timestamp(created_at)fromtimestamp_test;+----+---------------------+---------------------+----------------------------+|id|created_time|created_at|unix_timestamp(created_at)|+----+---------------------+---------------------+----------------------------+|1|2020-12-0908:00:00|2020-12-0900:00:00|1607472000||2|2020-12-0908:00:00|2020-12-0908:00:00|1607500800|+----+---------------------+---------------------+----------------------------+2rowsinset(0.18sec)因为这一切是MySQL隐式的帮我们转换了,让我们不用关心时区的问题就是数据库实际上会保存UTC时间戳,写入的时候先按Session时区转成UTC时间,读出的时候再按Session时区转成当前时区的时间,这些转换都是透明的假如我们在正八区存储了2020-12-0908:00:00时间的一条数据我们在正八区取出这一条数据,时间依然是2020-12-0908:00:00这时候我们有一台在零时区的服务器,连接MySQL,并且把当前连接的时区设置为+00:00,再去查数据库这条记录,查到的数据是:2020-12-0900:00:00,正好对应零时区的时间,这样子我们就不用考虑时区的问题.以上就是为什么MySQLtimestamp可以无视时区问题.的详细内容,更多关于MySQLtimestamp无视时区的资料请关注软件开发网其它相关文章!您可能感兴趣的文章:MySQLtimestamp的类型与时区实例详解通过实例解析MySqlCURRENT_TIMESTAMP函数MySQL的时间差函数TIMESTAMPDIFF、DATEDIFF的用法MySQL的时间差函数(TIMESTAMPDIFF、DATEDIFF)、日期转换计算函数(date_add、day、date_format、str_to_date)MySQL中datetime和timestamp的区别及使用详解MySQL5.6中TIMESTAMP有那些变化MySQL5.6中的TIMESTAMP和explicit_defaults_for_timestamp参数Mysql中的Datetime和Timestamp比较mysql数据类型TIMESTAMP
退出