DML 目标与冲突处理语义

即使 CTE 与表别名同名,DELETE 也会正确解析目标。自动生成主键的插入和 ON DUPLICATE KEY UPDATE 会使用正确的冲突与行锁行为。

语法

WITH cte_name AS (SELECT ...)
DELETE target_alias FROM table_name AS target_alias WHERE ...;

INSERT INTO table_with_auto_increment (other_column) VALUES (...);

INSERT INTO table_name VALUES (...)
ON DUPLICATE KEY UPDATE column_name = VALUES(column_name);

这些语句的 SQL 语法没有变化。

参数

  • 对于 DELETE,target_alias 是被删除表的别名。同名 CTE 不会替代该目标。多目标 DELETE 会在删除行前验证所有目标;实际 CTE 不能作为删除目标。

  • 对于 AUTO_INCREMENT 主键,在 INSERT 中省略主键列即可自动生成。冲突重试修复也覆盖包含大量小型存储对象的表,不改变生成主键的语法。

  • ON DUPLICATE KEY UPDATE 将 UNIQUE 键冲突解析到已有基表行并锁定该行。如果另一事务对该行持有 SELECT ... FOR UPDATE 锁,更新会等待该行锁。与已有行不同的新输入主键不是锁定目标。

示例

DROP DATABASE IF EXISTS docs_dml_conflicts;
CREATE DATABASE docs_dml_conflicts;
USE docs_dml_conflicts;
CREATE TABLE target_rows (id INT PRIMARY KEY);
INSERT INTO target_rows VALUES (1), (2);
WITH x AS (SELECT 99 AS id) DELETE x FROM target_rows AS x WHERE x.id = 1;
SELECT id FROM target_rows ORDER BY id;
CREATE TABLE generated_ids (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, payload INT);
INSERT INTO generated_ids (payload) VALUES (1), (2);
SELECT id, payload FROM generated_ids ORDER BY id;
CREATE TABLE upsert_target (id INT PRIMARY KEY, u INT UNIQUE, v INT);
INSERT INTO upsert_target VALUES (1, 10, 100);
SELECT id, u, v FROM upsert_target ORDER BY id;
DROP DATABASE docs_dml_conflicts;

DELETE 从 target_rows 中删除 id 1;自动生成主键的插入分配两个不同 ID。要通过 UNIQUE 键更新 id 1 的已有行,可在最后的 SELECT 前执行 INSERT INTO upsert_target VALUES (2, 10, 200) ON DUPLICATE KEY UPDATE v = VALUES(v);。