结构化查询语言(Structured Query Language)

提示：请小心并关闭 AUTOCOMMIT

在许多情况下，应用程序希望将行存储到数据库中，但不确定这些行是否已经存在。如果行存在，则需要使用 UPDATE 命令；如果不存在，则需要使用 INSERT 命令。通常，以下伪代码会被用来实现：

IF (SELECT COUNT(*) = 0 ...) THEN
  INSERT ...
ELSE
  UPDATE ...
;

这种方法存在诸多不便之处：

1. 需要两次往返数据库的操作：即 SELECT + INSERT 或 SELECT + UPDATE。
2. 无法进行批量存储：因为决定使用 INSERT 还是 UPDATE 的条件会因行而异，必须逐行处理。
3. 语法分散：分布在三个 SQL 语句中，容易出错。

为了解决这些问题，SQL 标准定义了 MERGE 命令，它将上述逻辑合并为一个语句。MERGE 根据目标表中各行的存在与否，执行 INSERT 或 UPDATE 操作。

MERGE 的语法

MERGE INTO <目标表> <目标表别名>
      USING <源表> <源表别名>
      ON (<匹配条件>)
  WHEN MATCHED THEN
UPDATE SET column1 = value1 [, column2 = value2 ...]
  WHEN NOT MATCHED THEN
INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...]);

描述

1. 目标表：在 MERGE INTO 之后指定目标表。
2. 源表：在 USING 之后指定源表。
3. 匹配条件：通过 ON 指定目标行和源行的匹配规则，语法与 WHERE 子句一致。可以是主键、外键，也可以是任意列的复杂条件。
4. WHEN MATCHED：
   • 匹配时执行的 UPDATE 操作，无需再次指定表名和 WHERE 子句。
5. WHEN NOT MATCHED：
   • 不匹配时执行的 INSERT 操作，也无需再次指定目标表。

示例

示例 1：插入或更新

创建一个表 hobby_shadow，并将部分行从表 hobby 中插入其中。随后，通过 MERGE 实现存在时更新、不存在时插入。

-- 在新表中存储 hobby 表中 ID 为偶数的行
CREATE TABLE hobby_shadow AS SELECT * FROM hobby WHERE MOD(id, 2) = 0;
SELECT * FROM hobby_shadow;

-- 根据 id 执行插入或更新
MERGE INTO hobby_shadow t   -- 目标表
      USING (SELECT id, hobbyname, remark
             FROM hobby) s  -- 源表
      ON (t.id = s.id)      -- 匹配条件
  WHEN MATCHED THEN
UPDATE SET remark = CONCAT(s.remark, ' Merge / Update')
  WHEN NOT MATCHED THEN
INSERT (id, hobbyname, remark) VALUES (s.id, s.hobbyname, CONCAT(s.remark, ' Merge / Insert'));
COMMIT;

-- 查看结果
SELECT * FROM hobby_shadow;

此 MERGE 语句只需一次往返即可处理所有行。某些行通过 INSERT 插入，其他行通过 UPDATE 更新。可通过 remark 列的内容区分。

MERGE 的扩展

1. 条件化的 WHEN 子句
   WHEN MATCHED 和 WHEN NOT MATCHED 可通过附加条件进一步细化操作逻辑：

   WHEN MATCHED AND (t.hobby_name IN ('Fishing', 'Underwater Diving')) THEN
   UPDATE SET remark = CONCAT('Water sports: ', t.remark)
   WHEN MATCHED AND (t.hobby_name IN ('Astronomy', 'Microscopy', 'Literature')) THEN
   UPDATE SET remark = CONCAT('Semi-professional leisure activity: ', t.remark)
   

2. 删除操作
   在 WHEN MATCHED 中，可以使用 DELETE 而非 UPDATE 删除匹配行：

   WHEN MATCHED THEN
   DELETE;
   

注意事项

• 标准差异：虽然 MERGE 是 SQL 标准的一部分，但许多数据库管理系统（DBMS）实现了不同的语法和概念。例如：
  • 一些 DBMS 使用不同的关键字代替 MERGE INTO。
  • 扩展功能可能会因 DBMS 而异。
• 兼容性检查：在使用 MERGE 时，请先确认目标 DBMS 的支持情况。