MySQL中insertOrUpdate的功能如何实现的-51CTO.COM

insertOrUpdate 在我们日常使用中比较常见，那么它是如何实现的呢，不知道大家有没有考虑过呢？

在 MySQL 中，可采用INSERT INTO ... ON DUPLICATE KEY UPDATE语句实现 insertOrUpdate 功能。

值得留意的是，在出现重复键时，会在先前索引值和当前值之间添加临时键锁，这可能导致死锁。

若要使用 INSERT INTO ... ON DUPLICATE KEY UPDATE 语句，需满足以下条件：

举个例子：

设想有一张 student 表，包括 id、name 和 age 三列，其中 id 是主键。现在要插入一条数据，若该数据的主键已存在，则更新该数据的姓名和年龄，否则插入该数据。

INSERT INTO student (id, name, age) VALUES (1, 'Paidaxing', 20)
ON DUPLICATE KEY UPDATE name='Paidaxing', age=18;

底层实现

使用INSERT INTO ... ON DUPLICATE KEY UPDATE语句，如果数据库中已存在具有相同唯一索引或主键的记录，则更新该记录。其底层原理和执行流程如下：

检查唯一索引或主键：执行INSERT INTO ... ON DUPLICATE KEY UPDATE语句时，数据库首先尝试插入新行。在此过程中，数据库会检查表中是否存在与新插入行具有相同的唯一索引或主键的记录。
冲突处理：如果不存在冲突的唯一索引或主键，新行将被正常插入。如果存在冲突，即发现重复的唯一索引或主键值，数据库将不会插入新行，而是转而执行更新操作。
执行更新：在检测到唯一索引或主键的冲突后，数据库将根据ON DUPLICATE KEY UPDATE后面指定的列和值来更新已存在的记录。这里可以指定一个或多个列进行更新，并且可以使用 VALUES 函数引用原本尝试插入的值。

除了 INSERT INTO ... ON DUPLICATE KEY UPDATE 之外，还有一些类似的 SQL 语句，比如：

在 MySQL 中使用 INSERT ON DUPLICATE KEY UPDATE 语句时，如果插入操作失败（因为主键或唯一键冲突），而执行了更新操作，确实会导致自增主键计数器增加，即使没有实际插入新记录。

这是因为 MySQL 在尝试插入新记录时，会先分配一个新的自增主键值，无论后续是插入成功还是执行更新操作，这个主键值都已经被分配并且会增加。

例如，假设有一个表 test 定义如下：

CREATE TABLE test (
    id INT AUTO_INCREMENT PRIMARY KEY,
    value VARCHAR(255),
    UNIQUE KEY unique_value (value)
);

然后执行以下语句：

INSERT INTO test (value) VALUES ('a')
ON DUPLICATE KEY UPDATE value = 'a';

图片

再执行一次：

图片

此时，由于 value 列存在唯一键约束，并且已经存在一条记录 value='a'，所以不会插入新记录，而是会执行更新操作。但即便如此，自增主键 id 的计数器依然会增加。

然后再插入一条新的记录：

图片

这意味着下一次插入新记录时，自增主键的值会比之前增加，即 2 已经被用过了，虽然没插入成功，但是新的记录就直接用 3 了。