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 UPDATEcolumn1 = value1, column2 = value2, ...;
解决方案
基础用法：
假设有一个名为
users
的表，包含
id
(主键),
username
和
三列。想要插入一条新记录，如果
username
已经存在，则更新
。
INSERT INTO users (username, email)VALUES ('john_doe', 'new_email@example.com')ON DUPLICATE KEY UPDATEemail = 'new_email@example.com';
如果
username
‘john_doe’ 不存在，则插入一条新记录。如果存在，则更新该用户的
。
使用
VALUES()
函数：
VALUES()
函数可以获取
INSERT
语句中指定的值，这在更新其他列时非常有用。
INSERT INTO users (username, email, login_count)VALUES ('jane_doe', 'jane@example.com', 1)ON DUPLICATE KEY UPDATEemail = VALUES(email),login_count = login_count + 1;
这里，如果
username
‘jane_doe’ 已经存在，则更新
为
INSERT
语句中的值，并将
login_count
增加 1。
处理多个唯一键：
ON DUPLICATE KEY UPDATE
适用于存在多个唯一键的情况。只要任何一个唯一键冲突，就会触发更新。
假设
users
表还有一个唯一键
。
INSERT INTO users (username, email)VALUES ('new_user', 'existing_email@example.com')ON DUPLICATE KEY UPDATEusername = VALUES(username);
如果
‘existing_email@example.com’ 已经存在，则更新该用户的
username
为 ‘new_user’。
避免不必要的更新：
如果只想在插入时更新某些特定列，可以使用条件判断来避免不必要的更新。
INSERT INTO users (username, email, status)VALUES ('some_user', 'some_user@example.com', 'active')ON DUPLICATE KEY UPDATEemail = IF(status = 'inactive', VALUES(email), email),status = IF(status = 'inactive', 'active', status);
只有当现有记录的
status
为 ‘inactive’ 时，才会更新
和
status
。
降重鸟
要想效果好，就用降重鸟。AI改写智能降低AIGC率和重复率。
113 查看详情
获取受影响的行数：
可以通过
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 UPDATEemail = VALUES(email),version = version + 1;-- 后续更新操作UPDATE users SET email = 'updated@example.com', version = version + 1WHERE 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 UPDATEemail = 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 UPDATEemail = VALUES(email);-- 获取自增ID的值SELECT LAST_INSERT_ID();
需要根据具体的业务需求来选择合适的处理方式。
以上就是sql怎样使用on duplicate key update处理插入重复 sql重复插入处理的操作技巧的详细内容，更多请关注创想鸟其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/597799.html
微信扫一扫 
支付宝扫一扫 
