MySQL自动更新列时间戳ON UPDATE CURRENT_TIMESTAMP

ON UPDATE很多人都用过，但什么时候不触发更新、更新有什么坏处等细节，却不一定都清楚。
读完本文，您将掌握ON UPDATE的用法、新版本特性、使用陷阱和优缺点。

作用

若一个时间列设置了自动更新ON UPDATE，当数据行发生更新时，数据库自动设置该列的值为当前时间¹。

支持的字段类型

从MySQL 5.6.5开始，TIMESTAMP和DATETIME列都支持自动更新，且一个表可设置多个自动更新列。

在MySQL 5.6.5之前:

• 只有TIMESTAMP支持自动更新
• 每个表只能有一个自动更新的时间列
• 不允许同时存在两个列：其中一个设置了DEFAULT CURRENT_TIMESTAMP，另一个设置了ON UPDATE CURRENT_TIMESTAMP

下面主要介绍MySQL 5.6.5及之后版本的自动更新列用法。

语法

col DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
col TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

 
 

 
 
1
2

注意

自动更新的触发时机

注意时间戳列“自动更新”的时机：

• 插入时如未指定该列值时。
• 当该行数据其他列有值变化时，如update。
  • 如果有更新操作、但其他列值并未变化，不会触发。此时要更新时间戳，需在SQL中指定新值给它（例如CURRENT_TIMESTAMP）

    如update table set column_tmt = CURRENT_TIMESTAMP

  • 如果更新时不想触发，可显式设置时间戳列为当前值。

    如update table set column_a = 'xxx', column_tmt = column_tmt

自动更新的时间值

CURRENT_TIMESTAMP还可以替换为任何等价标识符，如CURRENT_TIMESTAMP()、NOW()、LOCALTIME等（所有获取当前时间的标识符，可参考《MySQL日期与时间函数（日期/时间格式化、增减、对比、时区、UTC和UNIX时间）》）

自动更新值时，CURRENT_TIMESTAMP表示的是SQL执行时的时间。如果要写入实际每行数据发生变更的时间，需要执行SQL时手工设置自动更新列值为SYSDATE()，自动更新列的声明中、CURRENT_TIMESTAMP()不能替换为SYSDATE()。

DEFAULT设置

DEFAULT CURRENT_TIMESTAMP为该列设置默认值。

DEFAULT后的CURRENT_TIMESTAMP可替换为常量值，如2019-11-20 22:21:00；

如果未设置DEFAULT，自动更新仍然生效，只是没有默认值而已。TIMESTAMP的默认值为0、除非声明为NULL才会默认NULL，而DATETIME默认为NULL、设置为NOT NULL时默认值为0.

系统变量explicit_defaults_for_timestamp会影响默认值情况。

为了避免一些不必要的疏忽，建议自动更新列设置DEFAULT CURRENT_TIMESTAMP。

精度

与TIMESTAMP/DATETIME类型、及TIMESTAMP()函数一样，支持最多6位小数位。

• 各时间类型的精度，可参考《MySQL字段长度、取值范围、存储开销（5.6/5.7/8.x的主要类型，区分显示宽度/有无符号/定点浮点、不同时间类型）》中的“日期与时间”部分
• 时间函数相关，可参考《MySQL日期与时间函数（日期/时间格式化、增减、对比、时区、UTC和UNIX时间）》）

设置小数位时，各部分的精度必需保持一致，如：

col DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)

 
 

 
 
1

如果精度不一致、或部分设置了精度部分未设置，会报错ERROR 1067 (42000): Invalid default value for 'tmt'。

总结

自动更新列有好处也有不足：
好处：无需依赖业务实现时间戳，所有的db操作都会自动记录，便于排查问题。
不足：数据库服务器和业务服务器可能存在时间差，导致业务变动的时间与数据库时间戳存在差异，给实际维护和使用带来障碍。只能尽可能的校准服务器时间，但不能绝对避免该问题。

尽管如此，数据库设计时仍建议增加一个自动更新列作为时间戳，忠实反映数据库的最后变化时间。

---

1. https://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html ↩︎