深度剖析onduplicate

小蓝 • 2025-01-02 12:01 • 编程

在MySQL中，onduplicate被广泛使用来处理对于重复的主键或唯一索引值。具体来说，它通过一条INSERT语句，当有重复的主键或唯一的索引出现时，会执行下一步操作。常见的有覆盖（insert into… onduplicate key do nothing）,更新（insert into… onduplicate key update ）等操作

一、onduplicate key do not

当主键或唯一索引重复时,INSERT语句将不会进行任何操作，并忽略这一行的插入

insert into table_name (primary_key, column1, column2, ...) values (value1, value2, value3, ...) 
onduplicate key do nothing

实现上，ONDUP_HANDLERR_FUNC 类型的函数do_nothing_handler()以及do_nothing()函数会被调用, 在do_nothing_handler()函数中什么都没有做，因此会直接跳出函数。

二、onduplicate key update where

当主键或唯一索引重复时，MySQL实现了相关的Updating机制。以onduplicate key update where这条语句为例, 当插入的主键重复时，会根据where分句指定的条件来更新。举个例子，假设存在主键为id的一条记录，需要更新column1的值。可以用以下代码完成：

insert into table_name (primary_key, column1, column2, ...) 
values (value1, value2, value3, ...) 
onduplicate key update set column1=new_value where id=primary_key_value

在执行这个语句的时候，MySQL会先检查表中是否已经有了值为primary_key_value的行。如果有的话，MySQL会更新对应行的column1的值为new_value。如果没有，MySQL会插入一行：

insert into table_name (primary_key, column1, column2, ...)
values (value1, value2, value3, ...)

这个特性可以非常方便地插入带有唯一性约束的数据，并防止出现冲突。

三、onduplicate key

在执行 INSERT INTO … ON DUPLICATE KEY UPDATE 时，MySQL会在插入过程中根据PRIMARY KEY 或 UNIQUE KEY 约束，检查新插入的行是否与表中已有的行有重复之处，如果有，则执行UPDATE操作。如果没有，则执行INSERT操作。

insert into table_name (primary_key, column1, column2, ...) 
values (value1, value2, value3, ...) 
onduplicate key 
column1 = case when values(column1) is null then column1 else values(column1) end, 
column2 = case when values(column2) is null then column2 else values(column2) end

当主键或唯一索引重复时，UPDATE语句的作用是将主键ID为X的行更新为值为Y的行, 如果不存在这样的行, INSERT语句插入一条新记录。

四、onduplicate key update性能

在执行INSERT INTO … ON DUPLICATE KEY UPDATE 的时候，不论数据是插入还是更新，MySQL都会把行锁定在内存中，直到整个命令完成后释放。如果并发插入过多会增加锁超时的风险。因此，在实际需求中需要进行测试。

五、onduplicate key update锁表

在MySQL 8.0中，当“INSERT ON DUPLICATE KEY UPDATE”查询在表的行存在重复关键字时进行插入和更新并发访问时存在潜在的锁竞争，因为此时MySQL会有行级锁以及语句级锁的问题。建议在高并发情况下做好数据优化、分片、读写分离等处理。

六、onduplicatekeyupdate批量更新

在有些情况下，我们可能会遇到这样的问题，需要INSERT一组数据，但如果在数据库中已经存在了相同主键的记录，则需要对这些记录进行更新操作。此时，比较高效的方法是使用INSERT INTO … ON DUPLICATE KEY UPDATE语句配合VALUES子句，来实现批量更新。具体来说：

INSERT INTO table_name (primary_key, col1, col2, ...)
VALUES
    (1, 'foo', 'bar'),
    (2, 'baz', 'qux')
ON DUPLICATE KEY UPDATE
    col1=VALUES(col1),
    col2=VALUES(col2);

在这个例子中，如果（1，‘foo’,’bar’）这个主键已经在表中存在，则执行UPDATE操作来更新对应的值。同理，如果表中存在主键为2的记录，则把（2，“baz”、“qux”）这条记录更新到表中。如果表中原来没有这两个主键的记录，则直接执行INSERT操作。

总结

对于大多数数据库管理系统而言，onduplicate是一项非常重要的性能特性。在MySQL中，这个语法早已经成为了开发者们必须要熟练掌握的知识之一。在实际开发过程中，我们可以根据具体情况来选择合适的onduplicate处理方式，并且需要注意性能优化、锁表等问题，以避免在高并发情况下造成数据不一致等问题。

原创文章，作者：小蓝，如若转载，请注明出处：https://www.506064.com/n/306669.html