本文共 1967 字，大约阅读时间需要 6 分钟。

一、INSERT ON DUPLICATE KEY UPDATE 语法

INSERT INTO ... ON DUPLICATE KEY UPDATE 是 MySQL 中专门用于处理插入操作中唯一键冲突的语法，MySQL独有功能，不是 SQL 标准语法。

该语法主要用于在插入数据时，检测到唯一索引（包括主键）冲突时，执行后续的更新操作，而不是直接报错或忽略。适用于需要在数据唯一性约束下进行数据插入和更新操作的场景。

基本语法格式

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...) ON DUPLICATE KEY UPDATE column1 = value1, column2 = value2, ...;

说明：

• table_name：目标表的名称。
• (column1, column2, ...)：插入的字段名列表。
• (value1, value2, ...)：对应字段的值。
• ON DUPLICATE KEY UPDATE：在唯一键冲突时执行的更新操作。
• column1 = value1, column2 = value2, ...：需要更新的字段及其新值。
• column1 = VALUES(column1), column2 = VALUES(column2), ...：可以使用 VALUES() 函数获取插入数据中的原值。

语法使用场景

• 检测数据唯一性约束冲突时，执行更新操作。
• 适用于唯一索引、主键约束下的数据插入操作。
• 支持批量插入数据时的冲突处理。

处理逻辑

• 数据唯一性检查基于唯一索引（包括主键）。
• 如果数据已存在，执行指定的 UPDATE 语句，更新冲突行的相关字段。
• 更新操作不会改变其他字段的值，只更新指定的字段。
• 如果更新值与原值相同，更新操作不会产生实际变化。

需要注意的是：如果表中存在多个唯一索引，MySQL 会优先根据第一个存在冲突的唯一索引执行更新操作。

二、示例操作说明

1. 不存在记录，执行插入操作

示例：向 t_user 表中插入一条新记录。

INSERT INTO t_user(user_name, age, height) VALUES("lisi", 17, 180) ON DUPLICATE KEY UPDATE age = 18;

2. 存在记录，执行更新操作

示例：尝试插入已存在的用户信息，执行更新操作。

INSERT INTO t_user(user_name, age, height) VALUES("lisi", 17, 180) ON DUPLICATE KEY UPDATE age = 18;

3. 更新字段值与原值相同

示例：尝试插入已存在的用户信息，更新的字段值与原值相同。

INSERT INTO t_user(user_name, age, height, type) VALUES("lisi", 18, 180, 1) ON DUPLICATE KEY UPDATE age = 18;

4. 存在多个唯一索引时的处理

当表中存在多个唯一索引时，MySQL 会优先根据第一个冲突的唯一索引执行更新操作。

• 示例 1：根据 user_name 更新字段。
• 示例 2：根据 id 更新字段。
• 示例 3：根据 user_name 更新字段。
• 示例 4：根据 id 更新字段。

5. 使用 VALUES() 函数

VALUES() 函数可用于获取插入数据中的原值，常用于批量操作或动态更新场景。

INSERT INTO t_user(id, user_name, age, height, type) VALUES(2, "lisisi", 57, 480, 0) ON DUPLICATE KEY UPDATE age = VALUES(age) + 100;

6. 批量操作示例

批量插入数据时，可以使用 VALUES() 函数来更新冲突行的字段值。

INSERT INTO t_user(user_name, age, height, type) VALUES("lisi", 71, 701, 0),("lisisi", 72, 280, 1),("zhangsan", 73, 703, 0),("wangwu", 74, 704, null),("laoliu", 75, null, null) ON DUPLICATE KEY UPDATE user_name = VALUES(user_name),age = VALUES(age),height = VALUES(height),type = VALUES(type);

转载地址：http://gpdfk.baihongyu.com/