如何避免Mysql的timestamp的大坑-526互联

如何避免Mysql的timestamp的大坑

Mysql的timestamp类型讨论

需要测试MYSQL的同学，可以点以下链接免费试用腾讯云mysql服务器

https://curl.qcloud.com/tgnMO3KJ

一．时间戳字段定义

timestamp时间戳是指格林威治时间1970年01月01日00时00分00秒(北京时间1970年01月01日08时00分00秒)起到现在的总秒数。

在MySQL 5.6.4及之后版本，可以将时间戳类型数据精确到微秒，数据类型定义为timestamp(N),N取值范围为0-6，默认为0，如需要精确到毫秒则设置为Timestamp(3)，如需要精确到微秒则设置为timestamp(6)，数据精度提取的代价是其内部存储空间的变化，但仍未改变时间戳类型的最大和最小取值范围。

时间戳字段定义主要影响两类操作：

1. 插入记录时，时间戳字段包含DEFAULT CURRENT_TIMESTAMP，如插入记录时未指定具体时间数据则将该时间戳字段值设置为当前时间。

2. 更新记录时，时间戳字段包含ON UPDATE CURRENT_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，该字段在插入或更新时未指定值，则被赋值为当前时间。

二．Timestamp与datetime的区别

两者都可用来表示YYYY-MM-DD HH:MM:SS[.fraction]类型的日期，但timestamp没有时区问题，而datetime有时区问题，原因是timestamp在存储上是包含时区的，以UTC格式存储的，而datetime存储类似于时间字符串的形式，是不包含时区的。也就是说mysql数据库如果设置的时区改变，timestamp类型会随时区改变而改变，datetime类型则不变。也就是说timestamp包含时区，而datetime不包含。

三．时间戳类型和时间类型选择

在部分数据库指导文档中，会推荐使timestamp类型代替datetime字段，其理由是timestamp类型使用4字节，而datetime字段使用8字节，但随着磁盘性能提升和内存成本降低，在实际生产环境中，使用timestamp类型并不会带来太多性能提升，反而可能因timestamp类型的定义和取值范围限制和影响业务使用，例如2038问题。