数据库问题排查(Automatic Initialization and Updating for TIMESTAMP and DATETIME)

MySQL 的版本,暂时没法了解到,因为已经下班放假了。

  今天遇到一个问题,这里做一个简单的描述,希望能够了解这个问题。数据库建表语句如下:

CREATE TABLE IF NOT EXISTS `xxx` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `acquirer_id` varchar(32) NOT NULL,
  `ch_contract_id` varchar(32) DEFAULT ,
  `sign_status` tinyint(2) DEFAULT '0' 
  `created_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `modified_time` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=562 DEFAULT CHARSET=utf8 ;

根据某个特殊的需求,我需要获取用户最后使用的数据,于是我就做了一个update操作,具体如下:

update xxx set id = _id,  acquirer_id = _acquirer_id, ch_contract_id = _ch_contract_id, sign_status = _sign_status, created_time = _created_time  where id = _id

因为 modified_time的设置是,每次更新语句便自动更新。上边的语句执行的目的,是想着数据库,自动更新modified_time,业务上可以直接根据 modified_time 降序排序,获取最新的信息。但是,实际中发现,mybatis返回update受影响行数为1, 但是modified_time并没有变化。 且这条语句在mysql的log里,没有执行记录。

自己尝试在gui工具执行了以下的语句,返回受影响行数为0 :

update xxx set acquirer_id = _acquirer_id where id = _id

查看文档,发现了问题:

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).

MySQL的机制问题,导致的这个问题的 出现。
https://dev.mysql.com/doc/refman/8.0/en/timestamp-initialization.html

发表评论

This site uses Akismet to reduce spam. Learn how your comment data is processed.