当前位置: 首页 - 编程技术 - 文章正文

数据库时区和timestamp

xiaoqihv
问题:迁移后时间变了。 迁移前数据

迁移后数据

查看时区

show VARIABLES like '%time_zone%'; 迁移前时区

迁移后时区

这是什么意思?

首先time_zone显示的是当前数据库设置的时区,然后system_time_zone顾名思义就是数据库所在系统的时区,而当time_zone的值是system(默认)的时候,代表我们mysql数据库使用系统时区。

那有什么影响?

时区的变化,影响的必然就是时间了,而在mysql中,我们存储时间的方式主要是四种;

使用mysql的datetime数据类型进行存储使用timestamp数据类型进行存储将时间直接转成时间戳,然后在数据库中记录时间戳第四个就是直接将时间按需要的格式存储到字符串字段中 在考虑有什么影响之前,我们首先需要知道这四种方式来存储时间他们有什么不同。 时间存储方式之间的异同 datetime datetime格式数据的空间占用是8个字节;存储的时间范围是 0000-00-00 00:00:00.000000 - 9999-12-31 23:59:59.999999;另外在存储方式上,datetime存储时间时,写入的是什么,存储的就是什么,与时区没有关系。timestamp timestamp格式数据的空间占用是4个字节;存储的时间范围是 1970-01-01 00:00:01.000000 to 2038-01-19 03:14:07.999999;另外在存储方式上,timestamp存储时间时,本质存储的是时间戳。比如写入的是2022-04-22 22:09:30,且mysql时区为+8,所以存储的是UNIX_TIMESTAMP(2022-04-22 22:09:30)的值,与时区有关。为什么说与时区有关呢,因为从格式转换到时间戳时,是需要知道当前时间的时区的,当查询的时候,会从时间戳根据数据库的时区转换成对应时区的时间。时间戳 这本质上和timestamp存储是类似的,但是不同的一点是从时间转换成时间戳的这一步是应用服务去做的,所以mysql数据库就不需要关心时区的问题了,所以对于mysql来说,这也是与时区无关的。字符串来存储 我个人的感觉来说,其实和使用datetime是类似的,都是存入什么就是什么,所以也与时区无关。那与datetime不同的是,datetime格式作为mysql官方提供的两种时间存储类型之一,对于时间存储的支持上,肯定是比使用字符串来存储会更好的。 如何消除时间变化对于timestamp存储的时间的影响?

上面我们把四种时间存储方式都看了一下;发现当时区变化时,真正会有所影响的,只有一种类型,也就是timestamp。具体影响后的表现形式,其实上面的截图里面我们也看到了,那我们接下来一起看一下,如何去消除或者说避免时区对于我们timestamp时间的影响。

首先,从timestamp的定义来说,无论存储的是以utc还是时间戳来存储,在查询的时候最终呈现给我们看到的都是数据库时区对应的时间;所以我们看到的是什么时间,这与我们查询的时候数据库的时区是什么息息相关。这也是上面为什么会截图数据库时区的原因。要是细心的同事可能会有出现一些疑惑。从上面的截图看,虽然两个数据库的系统时区不同,但是数据库时区来看 一个是 +08:00,一个是Asia/Shanghai;都是东八区,但是为什么时间显示不一致呢。这也是当时在处理这个问题的时候,将我们带入歧路的最大的原因,都认为+08:00==Asia/Shanghai。但是实际上,最后我们发现对于mysql的时区设置来说,+08:00真的不等于Asia/Shangha。当我们将迁移后的时区手动改成+08:00时,再去查询就能看到和迁移之前的数据库是一致的,而设置成Asia/Shanghai的时候,时间实际上是相差12个小时的,也就是数据库所在服务器的系统时区。上面我们也提到了,当mysql默认时区是system,也就是使用系统时区,所以其实就是说时区设置成Asia/Shanghai是无效的。问题找到了,那怎么处理呢?第一种选择可以是修改数据库时区,设置成和我们之前的数据库时区一致就可以了。可以通过命令修改;也可以修改mysql配置文件,重启数据库后生效。 set global time_zone = '+8:00'; ##修改mysql全局时区为北京时间,即我们所在的东8区 set time_zone = '+8:00'; ##修改当前会话时区 但是,当因为某些原因,我们不能去改变数据库的全局时区的时候,我们可以怎么做呢。一个是当我们使用数据库工具链接数据库的时候,我们可以通过上面的set time_zone的方式修改当前会话时区,而在服务中,我们也可以在连接数据库的时候,在数据库连接参数中设置时区,和上面的设置会话时区的效果是相同的,就是设置本次数据库连接的数据库时区。
文章地址:https://wenmayi.cn/post/1073.html