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

🛰️ 应用开发者专区
2

您好，我使用这个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";

updated_time 字段是可以更新的：

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        |
+----+---------+-------------------------+----------------------------+