多表删除

MySQL 的 DELETE 语句可以使用 JOIN 构造，也允许指定要删除的表。这对于避免嵌套查询很有用。鉴于架构：

create table people
(    id int primary key,
    name varchar(100) not null,
    gender char(1) not null
);
insert people (id,name,gender) values
(1,'Kathy','f'),(2,'John','m'),(3,'Paul','m'),(4,'Kim','f');

create table pets
(    id int auto_increment primary key,
    ownerId int not null,
    name varchar(100) not null,
    color varchar(100) not null
);
insert pets(ownerId,name,color) values 
(1,'Rover','beige'),(2,'Bubbles','purple'),(3,'Spot','black and white'),
(1,'Rover2','white');

如果我们想删除保罗的宠物，声明

DELETE p2
FROM pets p2
WHERE p2.ownerId in (
    SELECT p1.id
    FROM people p1
    WHERE p1.name = 'Paul');

可以改写为：

DELETE p2    -- remove only rows from pets
FROM people p1
JOIN pets p2
ON p2.ownerId = p1.id
WHERE p1.name = 'Paul';

1 行已删除
Spot 已从 Pets 中删除

p1 和 p2 是表名的别名，特别适用于长表名和易读性。

要删除人和宠物：

DELETE p1, p2     -- remove rows from both tables
FROM people p1
JOIN pets p2
ON p2.ownerId = p1.id
WHERE p1.name = 'Paul';

2 行已删除
Spot 已从 Pets
中删除保罗已从 People 中删除

外键

当 DELETE 语句调用具有 foreing 键约束的表时，优化器可以按照不遵循该关系的顺序处理表。例如，将一个外键添加到 pets 的定义中

ALTER TABLE pets ADD CONSTRAINT `fk_pets_2_people` FOREIGN KEY (ownerId) references people(id) ON DELETE CASCADE;

引擎可能会尝试在 pets 之前删除 people 中的条目，从而导致以下错误：

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`pets`, CONSTRAINT `pets_ibfk_1` FOREIGN KEY (`ownerId`) REFERENCES `people` (`id`))

这种情况下的解决方案是从 people 中删除行，并依靠 InnoDB 的 ON DELETE 功能来传播删除：

DELETE FROM people
WHERE name = 'Paul';

删除 2 行
保罗从 People
Spot 中删除从 Pets 中删除级联

另一个解决方案是暂时禁用对外键的检查：

SET foreign_key_checks = 0;
DELETE p1, p2 FROM people p1 JOIN pets p2 ON p2.ownerId = p1.id WHERE p1.name = 'Paul';
SET foreign_key_checks = 1;