浅谈Mysql时间的存储 datetime还是时间戳timestamp

知识点掌握了，还需要不断练习才能熟练运用。下面golang学习网给大家带来一个数据库开发实战，手把手教大家学习《浅谈Mysql时间的存储 datetime还是时间戳timestamp》，在实现功能的过程中也带大家重新温习相关知识点，温故而知新，回头看看说不定又有不一样的感悟！

简单对比

占用空间

MySQL 常用的日期时间类型常用的是datetime、timestamp。除此之外 还有用的不多的YEAR DATE TIME
注意5.6.4的版本

从上表可以看到，DATETIME默认占用5个字节，而TIMESTAMP默认占用4个字节，如果需要更高精度的存储（秒后的小数点个数，比如毫秒）那么需要额外的存储空间。

优缺对比

• DATETIME占用字节较多，但表示范围较大，与时区无关。
• TIMESTAMPA:只能表示1970-2038年的时间，且B:不能用于分区列（真的么？还得查查 好像又不一定 也有说 官方文档说从MySQL 5.1.43开始，除了TIMESTAMP 外，其他日期类型都不接受！？），因为这种数据类型受时区限制，会受数据库时区的影响。**C:**当MySQL参数time_zone=system时，查询timestamp字段会调用系统时区做时区转换，而由于系统时区存在全局锁问题，在多并发大数据量访问时会导致线程上下文频繁切换，CPU使用率暴涨，系统响应变慢设置假死。（但C似乎只在一个博客看到这个问题，真的存在么？）为了避免这种问题，记得手动设置时区

timestamp在mysql中定义的是int类型的数据，然后1970年到2038年的秒数刚好21亿，为了限制，所以只能截止到2038年。虽然现在可以设置数字精度了 但是数据精度提高的代价是其内部存储空间的变大，但仍未改变时间戳类型的最小和最大取值范围。
**但是我觉得吧 随着时间临近，mysql会更新的。**而且还有这么多年呢 肯定也会有其他东西取代他

此外还有语言提供的字符串类型，10位（精确到秒）或13位（精确到毫秒）。其中13位必须bigint存储，占用8字节，而且在显示的时候，mysql不会自动转成我们常见的日期格式，所以不推荐使用。

如何存储毫秒或者更高级别的小数？

意思就是，毫秒部分需要以参数形式传参给数据类型，默认是不保存毫秒的，可以保存1-6位。如果需要保存三位的毫秒值，数据类型可以定义为DATETIME(3)或TIMESTAMP(3)，不需要保存毫秒的话，只需要将类型直接写为DATETIME TIMESTAMP。

时间戳详解

一个方便的用法

在创建新记录和修改现有记录的时候都对这个数据列刷新：（datetime也能用）

TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

显示格式（非存储格式）

TIMESTAMP值可以从1970的某时的开始一直到2037年，精度为一秒，其值作为数字显示。
TIMESTAMP值显示尺寸的格式如下表所示：

+---------------+----------------+
| 列类型　　　　| 显示格式　　　 |
| TIMESTAMP(14) | YYYYMMDDHHMMSS |　
| TIMESTAMP(12) | YYMMDDHHMMSS　 |
| TIMESTAMP(10) | YYMMDDHHMM　　 |
| TIMESTAMP(8)　| YYYYMMDD　　　 |
| TIMESTAMP(6)　| YYMMDD　　　　 |
| TIMESTAMP(4)　| YYMM　　　　　 |
| TIMESTAMP(2)　| YY　　　　　　 |
+---------------+----------------+

“完整”TIMESTAMP格式是14位，但TIMESTAMP列也可以用更短的显示尺寸，创造最常见的显示尺寸是6、8、12、和14。
你可以在创建表时指定一个任意的显示尺寸，但是定义列长为0或比14大均会被强制定义为列长14。
列长在从1～13范围的奇数值尺寸均被强制为下一个更大的偶数。
这有以下含义

• 虽然你建表时定义了列TIMESTAMP(8)，但在你进行数据插入与更新时TIMESTAMP列实际上保存了14位的数据（包括年月日时分秒），只不过在你进行查询时MySQL返回给你的是8位的年月日数据。如果你使用ALTER TABLE拓宽一个狭窄的TIMESTAMP列，以前被“隐蔽”的信息将被显示。
• 同样，缩小一个TIMESTAMP列不会导致信息失去，除了感觉上值在显示时，较少的信息被显示出。
• 尽管TIMESTAMP值被存储为完整精度，直接操作存储值的唯一函数是UNIX_TIMESTAMP()；由于MySQL返回TIMESTAMP列的列值是进过格式化后的检索的值，这意味着你可能不能使用某些函数来操作TIMESTAMP列（例如HOUR()或SECOND()），除非TIMESTAMP值的相关部分被包含在格式化的值中。
• 例如，一个TIMESTAMP列只有被定义为TIMESTAMP(10)以上时，TIMESTAMP列的HH部分才会被显示，因此在更短的TIMESTAMP值上使用HOUR()会产生一个不可预知的结果。
• 不合法TIMESTAMP值被变换到适当类型的“零”值(00000000000000)。（DATETIME,DATE亦然）

java可能遇到的坑

详情请看原文：原文链接：https://www.jb51.net/article/255355.htm
送 sql 前，会将 jdbc 中的 Date 对象参数，根据 serverTimeZone 配置的时区转化为日期字符串后，再发送 sql 请求给 mysql server，同样在 mysql server 返回查询结果后，结果中的日期值也是日期字符串，mysql 驱动会根据 serverTimeZone 配置的时区，将日期字符串转化为 Date 对象。

因此，当 serverTimeZone 与数据库实际时区不一致时，会发生时区转换错误，导致时间偏差，如下：

a、比如 sql 参数是一个 Date 对象，时间值是东 8 区的2020-02-23 08:00:00，注意它里面存储的可不是2020-02-23 08:00:00这个字符串，它是 Date 对象(绝对时间)，只是我用文字表达出来是东 8 区的2020-02-23 08:00:00。

b、然后，由于 serverTimeZone 配置的是东 8 区，mysql 驱动会将这个 Date 对象转为2020-02-23 08:00:00，注意这时已经是字符串了，然后再将 sql 发送给 mysql，注意这里的 sql 里面已经将 Date 参数替换为2020-02-23 08:00:00了，因为 Date 对象本身是无法走网络的。

c、然后 mysql 数据库接收到这个时间字符串2020-02-23 08:00:00后，由于数据库时区配置是东 9 区，它会认为这个时间是东 9 区的，它会以东 9 区解析这个时间字符串，这时数据库保存的时间是东9区的2020-02-23 08:00:00，也就是东8区的2020-02-23 07:00:00，保存的时间就偏差了 1 个小时。

d、查询结果里时间为什么又对了呢，因为查询结果返回了东 9 区的时间字符串，而 java 应用又将其理解为是东 8 区的时间，负负得正了！

时间戳查询的时候 能否返回原生的时间戳呢

今天关于《浅谈Mysql时间的存储 datetime还是时间戳timestamp》的内容介绍就到此结束，如果有什么疑问或者建议，可以在golang学习网公众号下多多回复交流；文中若有不正之处，也希望回复留言以告知！