多表刪除

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;