MySQL数据库常用的时间类型有timestamp和datetime,两者主要区别是占用存储空间长度不一致、可存储的时间也有限制,但针对不同版本下,timestamp字段类型的设置需要慎重,因为不注意的可能会被“坑死”。
一、TIMESTAMP和DATETIME字段类型对比
1.timestamp注意事项
(1)5.7版本之前,没有explicit_defaults_for_timestamp
参数(5.7默认开启,timestamp不会设置default值属性),timestamp字段默认存在default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP属性,默认值针对的主要是以下函数产生的时间
(2)mysql的timestamp值自动从当前时区转换到utc时区存储,并且自动从utc时区转换为当前系统时区检索返回
2.datetime注意事项:
在mysql5.7之后,datetime字段也可以指定默认值,并且格式和timestamp一样
二、数据库时区参数
1.system_time_zone
数据库实例启动,从my.cnf配置参数timezone=timezone_name获取,若my.cnf未设置则从操作系统获取环境变量TZ获取
2.time_zone
数据库当前实际使用的时区,默认为system,即系统时区,其设置可以通过set global time_zone=''设置,其中参数值有以下三种形式:'SYSTEM' 、'+10:00' 、'Europe/Helsinki'
三、实际使用实例
1.不同版本下timestamp和datetime使用实例
(1)测试timestamp和datetime创建带默认值的表
同时执行创建带默认值的SQL语句,发现5.6(不含5.6)之前版本datetime不支持带DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
(2)测试timestamp和datetime创建表结构类型
不指定默认值的情况下创建表结构,发现5.6(不含5.6)timestamp默认值是CURRENT_TIMESTAMP并且随着记录更新而更新
(3)、测试timestamp和datetime类型mysqldump导出导入影响
测试结论为,timestamp数据类型的记录导出会以utc时间格式导出,导入库中自动由UTC格式转为系统默认时区,所以看到导出文件timestamp内容时间戳和实际存储的不相符。
如果需要看到和导入与实际相符的时间戳,需要加入参数--tz-utc=false用于禁止timestamp时区转换,默认是开启的,即导出文件中开头设置的/*!40103 SET TIME_ZONE='+00:00' */;
系统默认是cst时区,数据库参数设置也是CST和SYSTEM,根据系统时间插入数据:
| system_time_zone | CST |
| time_zone | SYSTEM |
然后将数据库表test01表利用mysqldump导出
删除test01表,并用mysqldump导出文件还原
(4)ON UPDATE CURRENT_TIMESTAMP属性影响
如果包含ON UPDATE CURRENT_TIMESTAMP属性,则如果对表记录更新,此记录对应的timestamp类型记录也会更新
2.不同版本下mysqldump结束时间分析
使用mysqldump备份的同仁都可能会注意到,正常备份结束的话,在文件结尾会有一条成功结束的表示,即 :-- Dump completed on 2018-01-12 6:22:25。
这个在mysql5.0后可以通过一些参数控制,例如可以加参数--comments=false来禁止添加comments信息,即所有--开头的comment会不记录在备份文件中,也可以增加--dump-date=false来禁止时间戳添加,即只包含-- Dump completed。
但特别需要注意的是,此时间戳的由来在mysqldump 10.11版本(mysql5.0.x版本对应MySQL dump 10.11, 含10.11版本)记录的是UTC时区时间戳,而在mysqldump 10.12版本之后(mysql 5.5对应的版本是MySQL dump 10.13,含10.13)记录的是系统当前时区时间戳。
所以大家看到的是mysqldump 10.11备份结束的时间总是比系统当前时间提前8小时,例如mysql5.0系统当前是CST时区:2018-01-12 14:22:25 而备份文件结束时间戳是-- Dump completed on 2018-01-12 6:22:25。这个只是记录的时间戳不同,不影响最终的数据记录时间。
源码片段如下:
./client/mysqldump.c