MySQL `MERGE`语句

MySQL MERGE语句

介绍

MySQL MERGE语句是一种用于在数据库中合并（即插入、更新和删除）数据的功能强大的语句。它可以根据指定的条件在一个操作中执行多个操作，使数据的更新变得更加简单和高效。

本文将详细介绍MERGE语句的语法、用法和示例，帮助读者了解如何使用它来处理各种数据合并场景。

语法

下面是MERGE语句的基本语法：

MERGE INTO target_table
USING source_table
ON condition
WHEN MATCHED THEN
    UPDATE SET column1 = value1, column2 = value2, ...
WHEN NOT MATCHED THEN
    INSERT (column1, column2, ...)
    VALUES (value1, value2, ...)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

在上述语法中：

target_table是需要进行合并操作的目标表。
source_table是用于合并的源表，它可以是另一个数据库表、子查询、或者其他支持SELECT语句的数据源。
condition是一个逻辑表达式，用于指定何时执行MERGE操作，也就是判断目标表和源表之间的匹配关系。
WHEN MATCHED THEN模块用于指定当目标表的一行与源表的一行匹配时执行的操作，常见的是更新操作。
WHEN NOT MATCHED THEN模块用于指定当目标表的一行没有与源表的任何一行匹配时执行的操作，通常是插入操作。
WHEN NOT MATCHED BY SOURCE THEN模块用于指定当目标表的一行没有与源表的任何一行匹配且无源表数据时执行的操作，常见的是删除操作。

示例

建表和插入数据

在开始使用MERGE语句之前，我们先在MySQL中创建一个示例表，并插入一些数据作为示例：

CREATE TABLE students (
    id INT,
    name VARCHAR(50),
    score INT
);

INSERT INTO students VALUES
    (1, 'Alice', 80),
    (2, 'Bob', 90),
    (3, 'Charlie', 70),
    (4, 'David', 85);

更新表数据

假设我们有一个用于存储学生信息的目标表students，并且我们从另一个数据源中获得了一些新的学生信息。我们可以使用MERGE语句来更新目标表中的学生信息。

首先，我们创建一个源表new_students，并将新的学生信息插入该表中：

CREATE TABLE new_students (
    id INT,
    name VARCHAR(50),
    score INT
);

INSERT INTO new_students VALUES
    (1, 'Alice', 85),
    (3, 'Charlie', 75),
    (5, 'Eva', 95);

然后，我们使用MERGE语句将源表new_students的数据合并到目标表students中：

MERGE INTO students
USING new_students
ON students.id = new_students.id
WHEN MATCHED THEN
    UPDATE SET students.score = new_students.score
WHEN NOT MATCHED THEN
    INSERT (id, name, score)
    VALUES (new_students.id, new_students.name, new_students.score);

执行上述MERGE语句后，我们可以查看目标表students中的数据：

SELECT * FROM students;

/* 运行结果如下：
   +------+------+-------+
   | id   | name | score |
   +------+------+-------+
   | 1    | Alice| 85    |
   | 2    | Bob  | 90    |
   | 3    | Charlie | 75 |
   | 4    | David| 85    |
   | 5    | Eva  | 95    |
   +------+------+-------+
*/

可以看到，目标表中的学生信息已经更新，对于已经存在的学生（id为1和3），MERGE语句仅更新了他们的分数；对于新的学生（id为5），MERGE语句自动插入了他的信息。

插入数据并删除不匹配的行

除了更新数据，MERGE语句还可以用于插入新的行和删除不匹配的行。

假设我们得到了一个新的学生数据源，并且需要将其中的学生信息合并到目标表中。如果学生数据源中的学生已存在于目标表中，则更新其分数；如果学生数据源中的学生在目标表中不存在，则插入一条新数据；如果目标表中的学生不存在于学生数据源中，则删除目标表中的这些学生数据。

首先，我们创建一个源表student_source，并将新的学生信息插入该表中：

CREATE TABLE student_source (
    id INT,
    name VARCHAR(50),
    score INT
);

INSERT INTO student_source VALUES
    (1, 'Alice', 85),
    (3, 'Charlie', 75),
    (5, 'Eva', 95);

然后，我们使用MERGE语句将学生数据源student_source的数据合并到目标表students中，并删除目标表中不存在于学生数据源中的学生数据：

MERGE INTO students
USING student_source
ON students.id = student_source.id
WHEN MATCHED THEN
    UPDATE SET students.score = student_source.score
WHEN NOT MATCHED THEN
    INSERT (id, name, score)
    VALUES (student_source.id, student_source.name, student_source.score)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

执行上述MERGE语句后，我们可以查看目标表students中的数据：

SELECT * FROM students;

/* 运行结果如下：
   +------+------+-------+
   | id   | name | score |
   +------+------+-------+
   | 1    | Alice| 85    |
   | 3    | Charlie | 75 |
   | 5    | Eva  | 95    |
   +------+------+-------+
*/

可以看到，目标表中的学生信息已经更新，对于已经存在的学生（id为1和3），MERGE语句仅更新了他们的分数；对于新的学生（id为5），MERGE语句自动插入了他的信息；同时，不在学生数据源中的学生（id为2和4）已经被删除。

注意事项

在使用MERGE语句时，需要注意以下几点：

保证目标表和源表有相同的列结构，否则可能会引发错误。
注意判断匹配条件的准确性，以确保只合并到目标表中符合要求的数据。
可以在WHEN MATCHED THEN和WHEN NOT MATCHED THEN模块中包含多个操作，以实现更复杂的数据合并逻辑。
虽然MySQL并不直接支持MERGE语句，但是可以通过使用INSERT、UPDATE和DELETE语句的组合来达到相同的效果。在使用这种方法时，需要注意保持事务的一致性，以避免操作不完整或不正确的数据。
当目标表和源表之间的匹配条件有多个列时，可以使用逻辑运算符（如AND、OR）来组合条件，以确定匹配关系。
如果你需要在WHEN MATCHED THEN或WHEN NOT MATCHED THEN模块中执行更复杂的操作，例如多表联合查询、插入数据时使用默认值等，可以使用子查询或自定义函数来实现。
在使用MERGE语句时，为了保证性能和效率，可以对目标表和源表的相关列添加索引，以加快匹配和合并的速度。
在合并大量数据时，应注意控制事务的大小，避免因为一次性处理过多的数据而导致性能下降或者内存不足的问题。