为什么MySQL 使用timestamp可以无视时区问题.
之前一直有过疑惑为什么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无视时区的资料请关注毛票票其它相关文章！