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
。 -
获取受影响的行数：
可以通过
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();需要根据具体的业务需求来选择合适的处理方式。
