MySQL批量更新(bulk update)中对于Oracle merge into的两种替代策略--以更新学生成绩为例

MySQL批量更新(bulk update)中对于Oracle merge into的两种替代策略–以更新学生成绩为例

〇、背景介绍

Oracle数据库中的MERGE INTO语句，通常被称为合并语句，用于同时处理更新和插入操作。这种语句特别适合那些需要根据一组条件来决定是插入新记录还是更新现有记录的场景。
其基本语法如下：

-- target_table表示要更新的表名
MERGE INTO target_table 
-- source_table 表示新的数据，可以是输入的新数据，也可以从其他现有的表获取
USING source_table
-- condition 即已用于匹配已存在数据和要更新数据的条件
ON (condition)
-- 当source_table中的记录满足条件时执行
WHEN MATCHED THEN
    UPDATE SET column1 = value1, column2 = value2, ...
-- 当source_table中的记录不满足条件时执行
WHEN NOT MATCHED THEN
    INSERT (column1, column2, ...)
    VALUES (value1, value2, ...)

一 、场景引入

假设有学生成绩表，结构如下：

CREATE TABLE IF NOT EXISTS grade(
id INT AUTO_INCREMENT, -- 或者其他主键生成策略
name VARCHAR(20),
grade INT,
PRIMARY KEY (id)
)

准备数据：

INSERT INTO grade (name, grade) VALUES ('Alice', 85);
INSERT INTO grade (name, grade) VALUES ('Bob', 90);
INSERT INTO grade (name, grade) VALUES ('Charlie', 78);
INSERT INTO grade (name, grade) VALUES ('David', 92);
INSERT INTO grade (name, grade) VALUES ('Eva', 88);
INSERT INTO grade (name, grade) VALUES ('Frank', 75);
INSERT INTO grade (name, grade) VALUES ('Grace', 95);
INSERT INTO grade (name, grade) VALUES ('Helen', 80);
INSERT INTO grade (name, grade) VALUES ('Ian', 70);
INSERT INTO grade (name, grade) VALUES ('Julia', 82);

id	name	grade
1	Alice	85
2	Bob	90
3	Charlie	78
4	David	92
5	Eva	88
6	Frank	75
7	Grace	95
8	Helen	80
9	Ian	70
10	Julia	82

假设学生的成绩在录入时出现了错误，现在需要进行批量的根据学号更新正确的成绩。

三、解决方案一 for-each

最直接的解决方案，就是编写单条的根据学号等条件更新学生成绩的方法，然后在for循环中进行调用。这不是本文探讨的重点。

四、解决方案二 INSERT INTO … ON DUPLICATE KEY UPDATE

MySQL的INSERT INTO … ON DUPLICATE KEY UPDATE语句的主要作用是合并插入和更新操作，使得操作更加简洁和高效。因为它可以避免在插入数据之前进行额外的存在性检查。

注意：INSERT INTO … ON DUPLICATE KEY UPDATE依赖于主键或者其他索引的唯一键进行，因此在执行时必须提供更新内容的主键值

其基本语法如下：

-- table_name即要更新的表
INSERT INTO table_name (column1, column2, ...)
-- values表示要更新的数据，有多条时使用逗号隔开。
VALUES (value1, value2, ...)
-- 表示更新值的方式，可以与values中提供的数据进行对应
ON DUPLICATE KEY UPDATE
    column1 = new_value1,
    column2 = new_value2,
    ...;

在更新学生的场景下，可以写出如下sql：

INSERT INTO grade (id,grade)
VALUES 
(1,90),
(2,87),
(3,80),
(4,92),
(5,85),
(6,73),
(7,98),
(8,76),
(9,73),
(10,85)
ON DUPLICATE KEY UPDATE
grade = VALUES(grade);

值得注意的是，INSERT INTO … ON DUPLICATE KEY UPDATE 执行修改时，会产生两条 affected row，不执行但条件存在时产生一条affected row，条件不存在时产生0调affected row。

Query 1 OK: 18 rows affected

验证修改结果：

SELECT * FROM grade

结果：

id	name	grade
1	Alice	90
2	Bob	87
3	Charlie	80
4	David	92
5	Eva	85
6	Frank	73
7	Grace	98
8	Helen	76
9	Ian	73
10	Julia	85

五、解决方案三：CASE WHEN

如前文所述，INSERT INTO … ON DUPLICATE KEY UPDATE的执行依赖于主键或者唯一键，有时我们输入的数据中没有主键（比如学生的学号不是主键，或者输入中不包含学号而是学生的姓名等），那么此时会全部变成插入数据，无法更新。在此场景下，可以使用UPDATE+CASE WHEN的策略。

UPDATE table_name
SET column_name = CASE
-- 各个情况对应的值（各学号对应的成绩）
    WHEN condition1 THEN 'value1'
    WHEN condition2 THEN 'value2'
    ELSE column_name
END
WHERE codition;

在这个场景下，可以写出如下代码：

UPDATE grade 
SET grade = CASE
WHEN id = 1 THEN 85
WHEN id = 2 THEN 90
WHEN id = 3 THEN 78
WHEN id = 4 THEN 92
WHEN id = 5 THEN 88
WHEN id = 6 THEN 75
WHEN id = 7 THEN 95
WHEN id = 8 THEN 80
WHEN id = 9 THEN 70
WHEN id = 10 THEN 82
ELSE grade
END
WHERE id IN (1,2,3,4,5,6,7,8,9,10)

验证结果：

SELECT * FROM grade

id	name	grade
1	Alice	85
2	Bob	90
3	Charlie	78
4	David	92
5	Eva	88
6	Frank	75
7	Grace	95
8	Helen	80
9	Ian	70
10	Julia	82

五、总结

以上讨论了在MySQL中MERGE INTO语句的两种替代策略。第一种是使用MySQL的“INSERT INTO … ON DUPLICATE KEY UPDATE”语句，这种方法依赖于主键或唯一键，允许在插入时条件满足自动更新。第二种是使用“UPDATE+CASE WHEN”结构，这适用于无法依靠主键进行更新的情况。两种方法都提供了有效的批量更新数据的途径，选择哪种方法取决于具体的数据结构和更新需求。