MySQL - 不更新 updated_at 列 [英] MySQL - not updating the updated_at column

问题描述

我有一个表架构定义为:

I have a table schema defined as:

CREATE TABLE IF NOT EXISTS `updated_tables` (
  `table_name` VARCHAR(50) NOT NULL,
  `updated_at` TIMESTAMP(6)  NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`table_name`),
  UNIQUE INDEX `table_name_UNIQUE` (`table_name` ASC))
  ENGINE = InnoDB;

我使用这个查询:

SELECT * FROM citadel_test.updated_tables;
INSERT INTO `updated_tables` (`table_name`) VALUES ('testone') ON DUPLICATE KEY UPDATE `table_name`=VALUES(`table_name`);

运行后，条目testone被创建，同时updated_at也被创建并带有适当的timestamp(6).到目前为止一切都很好.

After it is run, the entry testone is created, also updated_at is created as well with appropriate timestamp(6). All good so far.

现在，当我再次运行此查询时，我希望使用来自时间戳 (6) 的新值更新 updated_at.但它没有发生.首次创建的值永远不会更新.

Now, when I run this query again, my expectation is to update the updated_at with new values from timestamp(6). But it does not happend. Values that are created for the first time, are never updated.

我需要更改表架构中的某些内容，但我不知道是什么.

Something I need to change in my table schema, but I do not know what.

推荐答案

在第一次之后创建的值永远不会更新，因为您的下一次插入根本不会更新行的任何字段.因为，数据库行没有发生变化，所以 updated_at 字段没有变化.

The values that are created after the first time are never updated because your next insert is not updating any field of the row at all. Since, there is no change happening in database row , so no change in updated_at field.

如果您只想测试更新时间戳功能，您可以多修改一个字段，然后尝试一下.

If you only want to test update timestamp feature, you can modify your table with one more field and then try it.

CREATE TABLE IF NOT EXISTS `updated_tables` (
  `table_name` VARCHAR(50) NOT NULL,
  `status` INT(3) NOT NULL,
  `updated_at` TIMESTAMP(6)  NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`table_name`),
  UNIQUE INDEX `table_name_UNIQUE` (`table_name` ASC))
  ENGINE = InnoDB;

下面的插入首先将状态设置为 1，然后将其更新为 2.由于行字段的实际更新正在发生，您的 updated_at 也将更改.

The below insert is first setting status as 1 and then updating it to 2. Since an actual update on field of row is happening, your updated_at will also be changed.

SELECT * FROM updated_tables;
INSERT INTO `updated_tables` (`table_name`, `status`) VALUES ('testone', 1) ON DUPLICATE KEY UPDATE `status`=VALUES(`status`);
INSERT INTO `updated_tables` (`table_name`, `status`) VALUES ('testone', 2) ON DUPLICATE KEY UPDATE `status`=VALUES(`status`);

这篇关于MySQL - 不更新 updated_at 列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！