The automatic update of the DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE field fails when not specified in INSERT INTO ON DUPLICATE KEY UPDATE

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE字段在INSERT INTO ON DUPLICATE KEY UPDATE没有指定时自动更新失效

| username: TiDBer_Q8VdpFLm

[TiDB Version] 5.7.25-TiDB-v5.4.1

Create Table Statement:
CREATE TABLE test_on_update(
id bigint(20) NOT NULL,
user_id bigint(20) DEFAULT NULL,
description VARCHAR(255) DEFAULT NULL,
updated_time TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
PRIMARY KEY(id)
);

Insert Test Data:
INSERT INTO test_on_update (id, user_id, description, updated_time) VALUES (1, 1001, ‘test1’, “2000-01-01 00:00:00”);
INSERT INTO test_on_update (id, user_id, description, updated_time) VALUES (2, 1002, ‘test2’, “2000-01-01 00:00:00”);
INSERT INTO test_on_update (id, user_id, description, updated_time) VALUES (3, 1003, ‘test3’, “2000-01-01 00:00:00”);

When executing the following statement, updated_time will automatically update to the current time:
UPDATE test_on_update SET description=‘test_description_update’ WHERE id=1;

[Question] Why doesn’t updated_time automatically update when executing the following statement:
INSERT INTO test_on_update (id) VALUES (2) ON DUPLICATE KEY UPDATE str = “test_description_insert”;

Why does updated_time only update when specified as follows:
INSERT INTO test_on_update (id) VALUES (2) ON DUPLICATE KEY UPDATE str = “test_description_insert”, updated_time=NOW();

2
| username: Icemap | Original post link

Hello, I used this SQL:

CREATE TABLE test_on_update(
	id bigint(20) NOT NULL,
	user_id bigint(20) DEFAULT NULL,
	description VARCHAR(255) DEFAULT NULL,
	updated_time TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
	PRIMARY KEY(id)
);

INSERT INTO test_on_update (id, user_id,description,updated_time) VALUES (1, 1001, 'test1', "2000-01-01 00:00:00");
INSERT INTO test_on_update (id, user_id,description,updated_time) VALUES (2, 1002, 'test2', "2000-01-01 00:00:00");
INSERT INTO test_on_update (id, user_id,description,updated_time) VALUES (3, 1003, 'test3', "2000-01-01 00:00:00");

INSERT INTO test_on_update (id) VALUES (2) ON DUPLICATE KEY UPDATE description = "test_description_insert";

The updated_time field can be updated:

SELECT * FROM test_on_update;
+----+---------+-------------------------+----------------------------+
| id | user_id | description             | updated_time               |
+----+---------+-------------------------+----------------------------+
| 1  | 1001    | test1                   | 2000-01-01 00:00:00        |
| 2  | 1002    | test_description_insert | 2022-06-29 12:01:38.789075 |
| 3  | 1003    | test3                   | 2000-01-01 00:00:00        |
+----+---------+-------------------------+----------------------------+
3
| username: TiDBer_Q8VdpFLm | Original post link

Hello~ Are you also using version 5.7.25-TiDB-v5.4.1?

4
| username: Icemap | Original post link

I’m using v6.1.0, I’ll try switching to your version later.

5
| username: Kongdom | Original post link

Hello, I have also verified it and it works. The version is 5.3.0.

6
| username: TiDBer_Q8VdpFLm | Original post link

I think I know why. If the command executed is INSERT INTO test_on_update (id) VALUES (2) ON DUPLICATE KEY UPDATE description = "test2";, then updated_time will not be automatically updated. This is because if the content of the field to be updated is the same as the original, TiDB does not perform an actual update operation. Consequently, the test_on_update field will not be updated either. So, does TiDB perform a validation before executing the update operation?

7
| username: Kongdom | Original post link

Yes, I have also noticed this. This situation should not be considered an update.

8
| username: TiDBer_Q8VdpFLm | Original post link

Yes, not performing the actual update operation indeed doesn’t count as an update.

9
| username: Icemap | Original post link

Yes, this situation does not count as an Update. You can see it from the affected_rows.

10
| username: system | Original post link

This topic was automatically closed 1 minute after the last reply. No new replies are allowed.