mysql获取当前系统日期和时间

mysql获取当前系统日期和时间,第1张

可以了解一下MySQL的时间戳(Timestamp)数据类型:

默认时间戳(Timestamp)类型的取值范围为'1970-01-01 00:00:01' UTC至'2038-01-19 03:14:07' UTC,数据精确到秒级别,该取值范围包含约22亿个数值,因此在MySQL内部使用4个字节INT类型来存放时间戳数据:

1、在存储时间戳数据时,先将本地时区时间转换为UTC时区时间,再将UTC时区时间转换为INT格式的毫秒值(使用UNIX_TIMESTAMP函数),然后存放到数据库中。

2、在读取时间戳数据时,先将INT格式的毫秒值转换为UTC时区时间(使用FROM_UNIXTIME函数),然后再转换为本地时区时间,最后返回给客户端。

(Timestamp)时间戳列可以有四张组合定义,其含义分别为:

1、当字段定义为timestamp,表示该字段在插入和更新时都不会自动设置为当前时间。

2、当字段定义为timestamp DEFAULT CURRENT_TIMESTAMP,表示该字段仅在插入且未指定值时被赋予当前时间,再更新时且未指定值时不做修改。

3、当字段定义为timestamp ON UPDATE CURRENT_TIMESTAMP,表示该字段在插入且未指定值时被赋值为"0000-00-00 00:00:00",在更新且未指定值时更新为当前时间。

4、当字段定义为timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,表示该字段在插入或更新时未指定值,则被赋值为当前时间。

你还在被以下问题困扰吗:

MySQL的安装规范中应该设置什么时区?

JAVA应用读取到的时间和北京时间差了14个小时,为什么?怎么解决?

已经运行一段时间的业务,修改MySQL的时区会影响已经存储的时间类型数据吗?

迁移数据时会有导致时间类型数据时区错误的可能吗?

...

看完这篇文章,你能解决上面所有的疑惑。首先出场的是和时区相关的启动参数和系统变量。

如果要在 MySQL 启动时就指定时区,则应该使用启动参数: default-time-zone ,示例:

启动后我们可以看到控制时区的系统变量,其中 time_zone 变量控制时区,在MySQL运行时可以通过 set 命令修改(注意:不可以写在 my.cnf 中):

启动参数和系统变量的可用值遵循相同的格式:

system_time_zone 变量只有全局值没有会话值,不能动态修改,MySQL 启动时,将尝试自动确定服务器的时区,并使用它来设置 system_time_zone 系统变量, 此后该值不变。当 time_zone='system' 时,就是使用的这个时区,示例中 time_zone 就是 CST,而 CST 在 RedHat 上就是东八区:

概括一下就两点:

不仅是select now(),包括insert .. values(now())、以及字段的 DEFAULT CURRENT_TIMESTAMP 属性也受此影响:

timestamp 数据类型会存储当时session的时区信息,读取时会根据当前 session 的时区进行转换;而 datetime 数据类型插入的是什么值,再读取就是什么值,不受时区影响。也可以理解为已经存储的数据是不会变的,只是 timestamp 类型数据在读取时会根据时区转换:

关于时区所有明面上的东西都在上面了,我们前面提到的困扰就是在暗处的经验。

1. MySQL的安装规范中应该设置什么时区?

对于国内的业务了,在 my.cnf 写入 default-time-zone='+08:00' ,其他地区和开发确认取对应时区即可。

为什么不设置为 system 呢?使用系统时间看起来也是个不错的选择,比较省事。不建议的原因有两点:

2. JAVA应用读取到的时间和北京时间差了14个小时,为什么?怎么解决?

这通常是 JDBC 参数中没有为连接设置时区属性(用 serverTimezone 参数指定),并且MySQL中没有设置全局时区,这样MySQL默认使用的是系统时区,即 CST。这样一来应用与MySQL 建立的连接的 session time_zone 为 CST ,前面我们提到 CST 在 RedHat 上是 +08:00 时区,但其实它一共能代表4个时区:

JDBC在解析CST时使用了美国标准时间,这就会导致时区错误。要解决也简单:一是遵守上面刚说到的规范,对MySQL显式地设置'+08:00'时区;二是JDBC设置正确的 serverTimezone。

3. 已经运行一段时间的业务,修改MySQL的时区会影响已经存储的时间类型数据吗?

完全不会,只会影响对 timestamp 数据类型的读取。这里不得不提一句,为啥要用 timestamp?用 datetime 不香吗,范围更大,存储空间其实差别很小,赶紧加到开发规范中吧。

4. 迁移数据时会有导致时间类型数据时区错误的可能吗?

这个还真有。

如何避免?mysqldump 也提供了一个参数 --skip-tz-utc ,意思就是导出数据的那个连接不设置 UTC 时区,使用 MySQL 的 global time_zone 系统变量值。

其实 mysqldump 导出 sql 文件时默认也是使用 UTC 时区,并且会在导出的 sql 文件头部带有 session time_zone 信息,这样可以保证导 SQL 文件导入和导出时使用相同的时区,从而保证数据的时区正确(而导出的 csv 文件显然不可以携带此信息)。需要注意的是 --compact 参数会去掉 sql 文件的所有头信息,所以一定要记得: --compact 参数得和 --skip-tz-utc 一起使用。


欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/zaji/8516003.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2023-04-17
下一篇 2023-04-17

发表评论

登录后才能评论

评论列表(0条)

保存