MySQL中如何实现Upsert仅在插入时设置列值，更新时不修改？

在MySQL的Upsert（INSERT ... ON DUPLICATE KEY UPDATE）操作中，我们需要实现这类需求：像createdBy这类记录创建者的列，仅在插入新行时赋值，后续更新时保留原始值；而updatedBy则每次操作都使用当前登录用户的值。

示例表结构 #

CREATE TABLE `movie` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` NVARCHAR(100) NOT NULL,
    `createdBy` NVARCHAR(100) NOT NULL,
    `updatedBy` NVARCHAR(100) NOT NULL,
    UNIQUE INDEX (`name`)
);

错误尝试分析 #

你之前用IFNULL(createdBy, 'Jill')赋值createdBy的方式行不通：在INSERT的VALUES子句中，createdBy是引用的表列，此时新行还未插入，该列值为NULL，最终会导致createdBy被设为NULL，违反列的NOT NULL约束。

正确实现方案 #

核心思路：INSERT阶段正常传递createdBy的当前用户值；触发ON DUPLICATE KEY UPDATE时，不修改createdBy列，仅更新需要变动的字段（如name、updatedBy）。

正确的Upsert语句 #

-- Jill执行Upsert（插入场景）
INSERT INTO `movie` (`name`, `createdBy`, `updatedBy`)
VALUES ('The Matrix', 'Jill', 'Jill') 
ON DUPLICATE KEY UPDATE
`id` = LAST_INSERT_ID(`id`),
`name` = VALUES(`name`),
-- 此处省略createdBy的更新，或写成createdBy = createdBy（效果一致）
`updatedBy` = VALUES(`updatedBy`);

-- Bob执行Upsert（更新场景）
INSERT INTO `movie` (`name`, `createdBy`, `updatedBy`)
VALUES ('The Matrix Reloaded', 'Bob', 'Bob') 
ON DUPLICATE KEY UPDATE
`id` = LAST_INSERT_ID(`id`),
`name` = VALUES(`name`),
-- 不更新createdBy，保留原始值
`updatedBy` = VALUES(`updatedBy`);

原理说明 #

• 插入新行时：createdBy会被设为传入的当前用户值，满足初始赋值需求。
• 更新已有行时：由于ON DUPLICATE KEY UPDATE中未对createdBy列赋值，数据库会保留该列的原始值，不会被当前传入的createdBy值覆盖。

验证期望结果 #

1. Jill插入行后：

id = 1
name = 'The Matrix'
createdBy = 'Jill' -- 保留创建者Jill的信息
updatedBy = 'Jill' -- 最后更新者为Jill

2. Bob更新同一行后：

id = 1
name = 'The Matrix Reloaded'
createdBy = 'Jill' -- 未被修改，仍为原始创建者
updatedBy = 'Bob' -- 最后更新者变为Bob

内容的提问来源于stack exchange，提问作者Matthew