sql怎样使用on duplicate key update处理插入重复 sql重复插入处理的操作技巧

发布时间：2025-08-11

点击量：

on duplicate key update 可在插入时避免主键或唯一键冲突报错，冲突时执行更新；2. 基础用法为插入记录，若唯一键冲突则更新指定字段；3. 使用 values() 函数可引用 insert 中的值进行更新；4. 多个唯一键任一冲突均可触发更新；5. 可通过 if 条件控制是否更新以避免不必要的修改；6. mysql_affected_rows() 返回 1 表示插入，2 表示更新，0 表示无变化；7. 高并发下可用乐观锁（版本号控制）或悲观锁（select for update）保证一致性；8. on duplicate key update 性能较好，但高并发大数据量时可考虑批量插入、先查后插更或消息队列；9. 自增id在更新时不变化，last_insert_id() 返回最近插入的id而非更新记录id，需注意使用场景；应根据业务需求和性能要求选择合适方案。

SQL中使用

ON DUPLICATE KEY UPDATE

可以在插入新记录时，如果主键或唯一键冲突，则更新现有记录，而不是报错。这是一种处理重复插入的有效方法。

ON DUPLICATE KEY UPDATE

的基本语法：

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

解决方案

基础用法：

假设有一个名为
```
users
```
的表，包含
```
id
```
(主键),
```
username
```
和
```
email
```
三列。想要插入一条新记录，如果
```
username
```
已经存在，则更新
```
email
```
。
```
INSERT INTO users (username, email)
VALUES ('john_doe', 'new_email@example.com')
ON DUPLICATE KEY UPDATE
email = 'new_email@example.com';
```
如果
```
username
```
'john_doe' 不存在，则插入一条新记录。如果存在，则更新该用户的
```
email
```
。

使用

VALUES()

函数：

VALUES()

函数可以获取

INSERT

语句中指定的值，这在更新其他列时非常有用。

INSERT INTO users (username, email, login_count)
VALUES ('jane_doe', 'jane@example.com', 1)
ON DUPLICATE KEY UPDATE
email = VALUES(email),
login_count = login_count + 1;

这里，如果

username

'jane_doe' 已经存在，则更新

email

为

INSERT

语句中的值，并将

login_count

增加 1。

处理多个唯一键：
```
ON DUPLICATE KEY UPDATE
```
适用于存在多个唯一键的情况。只要任何一个唯一键冲突，就会触发更新。

假设
```
users
```
表还有一个唯一键
```
email
```
。
```
INSERT INTO users (username, email)
VALUES ('new_user', 'existing_email@example.com')
ON DUPLICATE KEY UPDATE
username = VALUES(username);
```
如果
```
email
```
'existing_email@example.com' 已经存在，则更新该用户的
```
username
```
为 'new_user'。
避免不必要的更新：

如果只想在插入时更新某些特定列，可以使用条件判断来避免不必要的更新。
```
INSERT INTO users (username, email, status)
VALUES ('some_user', 'some_user@example.com', 'active')
ON DUPLICATE KEY UPDATE
email = IF(status = 'inactive', VALUES(email), email),
status = IF(status = 'inactive', 'active', status);
```
只有当现有记录的
```
status
```
为 'inactive' 时，才会更新
```
email
```
和
```
status
```
。
AletheaAI
世界上第一个从自然语言描述中生成交互式 AI 角色的多模态 AI 系统。
83 查看详情
获取受影响的行数：

可以通过
```
mysql_affected_rows()
```
函数（在 PHP 中）或类似的函数（在其他编程语言中）来获取受影响的行数。如果插入了一条新记录，则返回 1。如果更新了一条现有记录，则返回 2。如果没有发生任何变化，则返回 0。

如何避免因并发插入导致的问题？

高并发场景下，多个连接同时尝试插入相同唯一键的数据，可能会导致一些意想不到的问题。一种常见的解决方案是使用乐观锁或悲观锁。

乐观锁： 在表中添加一个版本号字段（例如

version

），每次更新记录时，版本号加 1。在

UPDATE

语句中，检查版本号是否与读取时的版本号一致。如果不一致，说明有其他连接已经更新了该记录，需要重新尝试。

INSERT INTO users (username, email, version)
VALUES ('concurrent_user', 'concurrent@example.com', 1)
ON DUPLICATE KEY UPDATE
email = VALUES(email),
version = version + 1;

--  后续更新操作
UPDATE users SET email = 'updated@example.com', version = version + 1
WHERE username = 'concurrent_user' AND version = original_version;

悲观锁： 使用

SELECT ... FOR UPDATE

语句锁定记录，防止其他连接同时修改。

--  开始事务
START TRANSACTION;

--  锁定记录
SELECT * FROM users WHERE username = 'concurrent_user' FOR UPDATE;

--  执行更新操作
UPDATE users SET email = 'updated@example.com' WHERE username = 'concurrent_user';

--  提交事务
COMMIT;

悲观锁会降低并发性能，但可以保证数据的一致性。

ON DUPLICATE KEY UPDATE

性能如何

？何时应该考虑其他方案？

ON DUPLICATE KEY UPDATE

在大多数情况下性能良好，因为它只需要一次数据库交互。但是，在高并发、数据量巨大的场景下，可能需要考虑其他方案，例如：

批量插入： 将多个插入操作合并成一个

INSERT

语句，可以减少数据库交互次数。

INSERT INTO users (username, email) VALUES
('user1', 'user1@example.com'),
('user2', 'user2@example.com'),
('user3', 'user3@example.com')
ON DUPLICATE KEY UPDATE
email = VALUES(email);

先查询后插入/更新： 先查询记录是否存在，然后根据查询结果执行插入或更新操作。这种方案需要多次数据库交互，但可以更灵活地控制更新逻辑。

--  先查询
SELECT id FROM users WHERE username = 'check_user';

--  根据查询结果执行插入或更新
IF record_exists THEN
  UPDATE users SET email = 'updated@example.com' WHERE username = 'check_user';
ELSE
  INSERT INTO users (username, email) VALUES ('check_user', 'new@example.com');
END IF;

使用消息队列： 将插入/更新操作放入消息队列，由消费者异步处理。这种方案可以提高系统的吞吐量，但会增加系统的复杂性。

选择哪种方案取决于具体的应用场景和性能需求。在做出决定之前，应该进行充分的测试和评估。

如何处理自增ID的问题？

在使用自增ID作为主键时，

ON DUPLICATE KEY UPDATE

的行为可能会有些微妙。如果插入操作导致了更新，自增ID的值不会改变。如果插入操作插入了一条新记录，自增ID的值会增加。

如果需要在更新时获取自增ID的值，可以使用

LAST_INSERT_ID()

函数。但是，需要注意的是，

LAST_INSERT_ID()

函数返回的是最近一次插入操作的自增ID值，而不是更新操作的自增ID值。

如果需要获取更新操作的自增ID值，可以在

UPDATE

语句中使用变量来保存自增ID的值。

INSERT INTO users (username, email)
VALUES ('auto_user', 'auto@example.com')
ON DUPLICATE KEY UPDATE
email = VALUES(email);

--  获取自增ID的值
SELECT LAST_INSERT_ID();

需要根据具体的业务需求来选择合适的处理方式。

以上就是sql怎样使用on duplicate key update处理插入重复 sql重复插入处理的操作技巧的详细内容，更多请关注其它相关文章！

上一篇：预解析 SQL 机制及优化预解析 SQL 在查询效率中的核

下一篇：sql语句怎样避免因大批量更新未加限制导致的锁表问题 sql

sql怎样使用on duplicate key update处理插入重复 sql重复插入处理的操作技巧

发布时间：2025-08-11

点击量：

如何避免因并发插入导致的问题？

ON DUPLICATE KEY UPDATE 性能如何？何时应该考虑其他方案？

如何处理自增ID的问题？

返回

4008988990

ON DUPLICATE KEY UPDATE
性能如何？何时应该考虑其他方案？