MySQL 多表删除

示例

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
WHEREp2.ownerIdin (
    SELECT p1.id
    FROM people p1
    WHERE p1.name = 'Paul');

可以重写为：

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

已删除1行
现货从宠物中删除

p1和p2是表名的别名，对于长表名和易于阅读尤其有用。

移除人和宠物：

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

2行删除
点从宠物
中删除保罗从人中删除

外键

当DELETE语句用前键约束表时，优化器可能以不遵循关系的顺序处理表。例如，将外键添加到pets

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

引擎可能会尝试从peoplebefore删除条目pets，从而导致以下错误：

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行
Paul已从People
Spot中删除从Pets中级联删除

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

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