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

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

作用

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

支持的字段类型

MySQL 5.6.5开始,TIMESTAMPDATETIME列都支持自动更新,且一个表可设置多个自动更新列。

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

注意

自动更新的触发时机

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

  • 插入时如未指定该列值时。
  • 当该行数据其他列有值变化时,如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位小数位。

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

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

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

总结

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

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


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

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值