MySQL UPSERT用法介绍|极客笔记

MySQL UPSERT用法介绍

1. 什么是UPSERT

UPSERT是MySQL中一种常用的数据操作语句，其作用是在插入数据时如果已经存在则更新，如果不存在则插入。这个操作在实际开发中非常常见，可以极大地简化开发流程。

2. UPSERT的使用方法

UPSERT操作可以通过INSERT INTO ... ON DUPLICATE KEY UPDATE语句来实现。在执行这个语句时，MySQL会先尝试插入一行数据，如果插入的数据与表中的某一行数据产生冲突（例如主键冲突），则会执行更新操作。

下面是一个示例的UPSERT操作的语法：

INSERT INTO 表名 (列1, 列2, 列3, ...) VALUES (值1, 值2, 值3, ...)
    ON DUPLICATE KEY UPDATE 列名1=新值1, 列名2=新值2, 列名3=新值3, ...

其中，表名是要操作的表的名称，列1, 列2, 列3, ...是要插入或更新的列的名称，值1, 值2, 值3, ...是要插入的值，用于插入新数据或更新已有数据，列名1=新值1, 列名2=新值2, 列名3=新值3, ...是要更新的列和对应的新值。

示例1

假设有一个名为users的表，包含三列id, name和age，其中id为主键。现在，我们要插入一条数据，如果id已经存在，则更新name和age的值，否则插入新的一行数据。

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

INSERT INTO users (id, name, age)
VALUES (1, 'Alice', 25)
ON DUPLICATE KEY UPDATE 
    name = VALUES(name),
    age = VALUES(age);

运行结果：
插入一行数据：

id: 1
name: Alice
age: 25

示例2

继续使用上述users表，插入一条数据并更新已有数据。

INSERT INTO users (id, name, age)
VALUES (2, 'Bob', 30)
ON DUPLICATE KEY UPDATE 
    name = VALUES(name),
    age = VALUES(age);

运行结果：
插入一行数据：

id: 2
name: Bob
age: 30

示例3

使用UPSERT语句插入一条数据，如果已经存在则更新。

INSERT INTO users (id, name, age)
VALUES (1, 'David', 35)
ON DUPLICATE KEY UPDATE 
    name = VALUES(name),
    age = VALUES(age);

运行结果：
更新已有数据：

id: 1
name: David
age: 35

示例4

插入多条数据，并在遇到冲突时更新。

INSERT INTO users (id, name, age)
VALUES 
    (3,'Eva',40),
    (4,'Frank',35),
    (1,'George',30)
ON DUPLICATE KEY UPDATE 
    name = VALUES(name),
    age = VALUES(age);

运行结果：
更新已有数据和插入新数据：

id: 3, name: Eva, age: 40
id: 4, name: Frank, age: 35
id: 1, name: George, age: 30

示例5

使用UPSERT操作更新users表，当插入新数据或更新已有数据时，针对name列添加一定的前缀。

INSERT INTO users (id, name, age)
VALUES (5, 'Ivy', 25)
ON DUPLICATE KEY UPDATE 
    name = CONCAT('New_', VALUES(name)),
    age = VALUES(age);

运行结果：
插入一行数据：

id: 5
name: New_Ivy
age: 25

3. UPSERT的注意事项

在使用UPSERT时，需要注意以下几点：

表中必须定义了唯一索引或主键，以便用于判断是否存在冲突。
VALUES()函数用于获取插入语句中的值，可以在ON DUPLICATE KEY UPDATE子句中引用这些值。
在执行UPSERT操作时，MySQL会先尝试插入数据，如果插入失败（例如由于主键冲突），则会执行更新操作。更新操作将使用UPDATE子句中指定的新值。
UPSERT操作是原子的，即如果多个客户端同时执行相同的UPSERT操作，只有一个客户端能够插入成功，其他客户端都会执行更新操作。

4. 总结

UPSERT语句是MySQL中一种常用的数据操作语句，用于在插入数据时，如果已经存在则更新，如果不存在则插入。通过使用INSERT INTO ... ON DUPLICATE KEY UPDATE语句，可以非常方便地实现UPSERT操作。在使用UPSERT时需要注意表中必须定义唯一索引或主键，VALUES()函数用于获取插入语句中的值，操作是原子的，只有一个客户端能够插入成功，其他客户端都会执行更新操作。通过这篇文章的介绍，相信读者已经对MySQL中的UPSERT操作有了更深入的了解。