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的ONDELETE能力来传播删除内容：
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;