问题
应用中用gmt_modified字段来判断记录更新时间,对于1天未更新的就过期掉记录。结果发现有些记录应该更新过(有log为证),结果gmt_modified字段就是没变化,这是为什么呢?
CREATE TABLE `advice` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`gmt_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`gmt_modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
)ENGINE=InnoDB
通过UT来模拟实际场景,终于发现一个坑:timestamp字段并不是每次更新记录就一定更新。详情看MySQL 5.6官网描述:
An auto-updated column is automatically updated to the current timestamp when the value of any other column in the row is changed from its current value. An auto-updated column remains unchanged if all other columns are set to their current values. To prevent an auto-updated column from updating when other columns change, explicitly set it to its current value. To update an auto-updated column even when other columns do not change, explicitly set it to the value it should have (for example, set it to CURRENT_TIMESTAMP).
简单点说,如果更新记录的所有其他字段都没有变化,gmt_modified
字段是不会更新的。
解决方案:
在SQL中明确设置gmt_modified=null,可以触发gmt_modified
字段的更新。
UPDATE advice SET column1=?,column2=?,gmt_modified=null WHERE id=?
所以官网的资料该看的还是要看,不然踩了坑,要花更多的时间来debug,找出问题原因。