使用MySQL的INSERT INTO ... ON DUPLICATE KEY UPDATE进行高效数据插入和更新

关注

原创 2024-04-16 10:56:55 1.3k 阅读 AI 写同款· GEO 优化 ›

mysql特殊语法insert into .. on duplicate key update ..使用详解阅读详情

使用MySQL的INSERT INTO … ON DUPLICATE KEY UPDATE进行高效数据插入和更新

在数据库管理中，经常会遇到需要在表中插入新数据，如果已存在相同的唯一键（或主键），则更新现有数据的情况。MySQL提供了一种强大而简洁的解决方案：INSERT INTO … ON DUPLICATE KEY UPDATE语句。在本篇博客中，我们将介绍如何使用这个语句进行高效的数据插入和更新操作。

什么是INSERT INTO … ON DUPLICATE KEY UPDATE语句？

INSERT INTO ... ON DUPLICATE KEY UPDATE语句是MySQL中的一种特殊的插入语句，它结合了插入和更新操作。当我们试图向表中插入新数据时，MySQL会检查表中是否已经存在具有相同唯一键（或主键）的行。如果存在相同的唯一键，则会更新现有行的数据；否则，将插入新行。

如何使用INSERT INTO … ON DUPLICATE KEY UPDATE语句？

主键更新

让我们通过一个简单的示例来说明如何使用这个语句。假设我们有一个名为users的表，它包含以下列：

id：主键
name：姓名
phone：电话号码
age：年龄
email：电子邮件地址

现在，我们想要将一条用户信息插入到这个表中，如果已存在相同id的用户，则更新其表字段。我们可以使用以下语句：

INSERT INTO users (id, name, phone, age, email)
VALUES (1, 'John', '13888888886', 30, 'john@example.com')
ON DUPLICATE KEY UPDATE 
name = VALUES(name), phone = VALUES(phone), age = VALUES(age), email = VALUES(email);

在这个示例中，我们尝试向users表中插入一条新数据，其id为1。如果具有相同id的行已经存在，则将更新该行的name和age字段，否则将插入新行。

批量更新数据

除了单个数据行的插入和更新，INSERT INTO … ON DUPLICATE KEY UPDATE语句还可以用于批量更新数据。假设我们有一个包含多个用户信息的列表，我们要批量插入或更新这些用户信息。我们可以使用以下语句：

INSERT INTO users (id, name, phone, age, email) VALUES
(1, 'John', '13888888886', 30, 'john@example.com'),
(2, 'Alice', '13888888887', 25, 'alice@example.com'),
(3, 'Bob', '13888888888', 35, 'bob@example.com')
ON DUPLICATE KEY UPDATE 
name = VALUES(name), phone = VALUES(phone), age = VALUES(age), email = VALUES(email);

在这个例子中，我们一次性插入了三个用户的信息。如果其中任何一个用户的id已经存在于users表中，则将更新该行的name、age和email字段，否则将插入新行。

唯一键更新

users表以手机号phone做为唯一键：添加唯一索引。

ALTER TABLE `users`
ADD UNIQUE INDEX `unique_phone` (`phone`) USING BTREE ;

现在，我们想要将一条用户信息插入到这个表中，如果已存在相同phone的用户，则更新其name，age和email字段。我们可以使用以下语句：

INSERT INTO users (id, name, phone, age, email)
VALUES (2, 'John', '13888888888', 30, 'john@example.com')
ON DUPLICATE KEY UPDATE name = VALUES(name), age = VALUES(age), email = VALUES(email);

在这个示例中，我们尝试向users表中插入一条新数据，其id为2。如果具有相同phone的行已经存在，则将更新该行的name，age和email字段，否则将插入新行。

批量更新同上。

Mybatis的XML写法

单条插入

<!-- 插入单条数据 -->
<insert id="insertOrUpdateUser" parameterType="map">
    INSERT INTO users (id, name, phone, age, email)
    VALUES (#{id}, #{name}, #{phone}, #{age}, #{email})
    ON DUPLICATE KEY UPDATE
    	name = #{name}, age = #{age}, email = #{email}
</insert>

批量插入

<!-- 批量插入数据 -->
<insert id="batchInsertOrUpdateUsers" parameterType="list">
    INSERT INTO users (id, name, phone, age, email)
    VALUES
    <foreach collection="list" item="user" separator=",">
        (#{user.id}, #{user.name}, #{user.phone}, #{user.age}, #{user.email})
    </foreach>
    ON DUPLICATE KEY UPDATE 
    	name = VALUES(name), age = VALUES(age), email = VALUES(email)
</insert>