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);。