本文共 2050 字，大约阅读时间需要 6 分钟。

MySQL时间戳类型详解

MySQL中的时间戳类型是处理时间数据的重要工具之一，本文将从多个方面详细介绍时间戳类型的特性、存储机制以及在不同版本中的应用。

1. 时间戳数据存取

在MySQL 5.5、5.6和5.7中，默认的timestamp类型的取值范围为1970-01-01 00:00:01 UTC至2038-01-19 03:14:07 UTC，精确到秒级别。该类型使用4字节的INT类型存储数据。

存取过程中需要注意以下几点：

从MySQL 5.6.4开始，支持精确到微秒的时间戳类型定义，即timestamp(N)，其中N的取值范围为0-6。精确到毫秒需设置为timestamp(3)，精确到微秒则为timestamp(6)。

2. 时间戳字段定义

时间戳字段的定义会影响插入和更新操作的行为。主要有以下四种组合定义方式：

• 仅定义为timestamp：插入和更新时不会自动设置为当前时间。
• timestamp DEFAULT CURRENT_TIMESTAMP：插入时若无值则赋当前时间，更新时若无值则不修改。
• timestamp ON UPDATE CURRENT_TIMESTAMP：插入时若无值则赋0000-00-00 00:00:00，更新时若无值则赋当前时间。
• timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP：插入和更新时若无值均赋当前时间。

需要注意的是：

• 在MySQL 5.5及之前版本中，只能对单个时间戳字段定义DEFAULT或ON UPDATE属性，但从MySQL 5.6开始，该限制被取消。
• 在MySQL 5.6中，explicit_defaults_for_timestamp参数默认为1，而在5.7中，默认为0。
• 在5.5和5.7中，timestamp类型默认为NOT NULL，而在5.6中，默认为NULL。

3. 时间戳类型引发的异常

在以下情况下使用time_zone=system时，可能会导致异常：

• 查询时间戳字段时，会调用系统时区进行时区转换。
• 全局锁机制可能导致线程上下文频繁切换，影响性能。

4. 时间戳类型与时间类型的选择

虽然timestamp类型存储空间较小（4字节），但其定义和取值范围可能对业务需求产生限制。在需要更大范围或时区支持的情况下，建议使用datetime类型。从MySQL 5.6.4开始，datetime类型支持毫秒精度，与timestamp类型效果相似。

5. 时间戳类型的使用建议

• 只关心更新时间：建议定义为TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP。
• 关注创建和更新时间：使用datetime类型，显式指定创建时间，避免依赖默认值。
• 减少时间戳字段数量：显式定义DEFAULT和ON UPDATE属性。
• 避免不必要的更新：仅在必要时显式插入和更新时间戳字段。

6. 时间戳与datetime的异同

• 相同点：
  • 可自动更新和初始化，默认显示格式为YYYY-MM-dd HH:mm:ss。
• 不同点：
  • timestamp的取值范围较小，且支持时区转换，存储为毫秒值。
  • datetime的取值范围更大，支持更高精度，存储为秒和毫秒。

7. 解决2038年问题

timestamp类型的取值范围有上限（2038-01-19 03:14:07 UTC），超过该范围会导致错误。解决方案包括：

8. 替换timestamp为datetime的步骤

• 修改字段名：

  ALTER TABLE `student` CHANGE `entry_date` `temp_entry_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00';

• 新建datetime字段：

  ALTER TABLE `student` ADD `entry_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00';

• 数据迁移：

  UPDATE `student` SET `entry_date` = `temp_entry_date`;

• 删除旧字段：

  ALTER TABLE `student` DROP `temp_entry_date`;

通过以上步骤，可以在不影响业务连续性的情况下，成功替换timestamp为datetime类型，扩展时间范围。

转载地址：http://jvdfk.baihongyu.com/