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

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, ...;

解决方案

1. 基础用法：

   假设有一个名为

   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

   。

2. 使用

   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。

3. 处理多个唯一键：

   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’。

4. 避免不必要的更新：

   如果只想在插入时更新某些特定列，可以使用条件判断来避免不必要的更新。

   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

   。

5. 获取受影响的行数：

   可以通过

   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();

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