在本教學中,您將學習如何使用MySQL ON DELETE CASCADE
參照操作來執行外來鍵從多個相關表中刪除資料。
在上一個教學中,我們學習了如何使用單個DELETE語句從一個或多個相關表中刪除資料。但是,MySQL提供了一種更為有效的方法,稱為ON DELETE CASCADE
對於外來鍵的參照操作,可以實現在從父表中刪除資料時自動刪除子表中的資料。
下面來看一些使用MySQL ON DELETE CASCADE
的例子。
假設有兩張表:建築物(buildings
)和房間(rooms
)。 在這個資料庫模型中,每個建築物都有一個或多個房間。 然而,每個房間只屬於一個建築物。沒有建築物則房間是不會存在的。
建築物和房間表之間的關係是一對多(1:N),如下面的資料庫圖所示:
當我們從buildings
表中刪除一行時,還要刪除rooms
表中參照建築物表中行的行。 例如,當刪除建築編號(building_no
)為2
的行記錄時,在buildings
表上執行如下查詢:
DELETE FROM buildings
WHERE
building_no = 2;
我們希望rooms
表中涉及到建築物編號2
的行記錄也將被刪除(講得通俗一點:假設2
號樓倒塌了,那麼2
號樓的房間應該也就不存在了)。以下是演示MySQL ON DELETE CASCADE
參考操作如何工作的步驟。
第一步, 建立buildings
表,如下建立語句:
USE testdb;
CREATE TABLE buildings (
building_no INT PRIMARY KEY AUTO_INCREMENT,
building_name VARCHAR(255) NOT NULL,
address VARCHAR(255) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
第二步, 建立rooms
表,如下建立語句:
USE testdb;
CREATE TABLE rooms (
room_no INT PRIMARY KEY AUTO_INCREMENT,
room_name VARCHAR(255) NOT NULL,
building_no INT NOT NULL,
FOREIGN KEY (building_no)
REFERENCES buildings (building_no)
ON DELETE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
請注意,在外來鍵約束定義的末尾新增ON DELETE CASCADE
子句。
第三步, 將一些資料插入到buildings
表,如下插入語句:
INSERT INTO buildings(building_name,address)
VALUES('海南大廈','海口市國興大道1234號'),
('萬達水城','海口市大同路1200號');
第四步, 查詢buildings
表中的資料:
mysql> select * from buildings;
+-------------+---------------+----------------------+
| building_no | building_name | address |
+-------------+---------------+----------------------+
| 1 | 海南大廈 | 海口市國興大道1234號 |
| 2 | 萬達水城 | 海口市大同路1200號 |
+-------------+---------------+----------------------+
2 rows in set
現在可以看到,在建築物表中有兩行記錄。
第五步, 將一些資料插入到rooms
表,如下插入語句:
INSERT INTO rooms(room_name,building_no)
VALUES('Amazon',1),
('War Room',1),
('Office of CEO',1),
('Marketing',2),
('Showroom',2);
第六步, 查詢rooms
表中的資料:
mysql> select * from rooms;
+---------+---------------+-------------+
| room_no | room_name | building_no |
+---------+---------------+-------------+
| 1 | Amazon | 1 |
| 2 | War Room | 1 |
| 3 | Office of CEO | 1 |
| 4 | Marketing | 2 |
| 5 | Showroom | 2 |
+---------+---------------+-------------+
5 rows in set
從上面行記錄中可以看到,building_no=1的建築有3
個房間,以及building_no=2有2
個房間。
第七步, 刪除編號為2的建築物:
DELETE FROM buildings WHERE building_no = 2;
第八步, 查詢 rooms
表中的資料 -
mysql> DELETE FROM buildings WHERE building_no = 2;
Query OK, 1 row affected
mysql> SELECT * FROM rooms;
+---------+---------------+-------------+
| room_no | room_name | building_no |
+---------+---------------+-------------+
| 1 | Amazon | 1 |
| 2 | War Room | 1 |
| 3 | Office of CEO | 1 |
+---------+---------------+-------------+
3 rows in set
可以看到,表中只剩下參照building_no=1
的記錄了,參照building_no=2
的所有行記錄都被自動刪除了。
請注意,
ON DELETE CASCADE
僅支援使用儲存引擎支援外來鍵(如InnoDB
)的表上工作。 某些表型別不支援諸如MyISAM
的外來鍵,因此應該在使用MySQLON DELETE CASCADE
參照操作的表上選擇適當的儲存引擎。
查詢受MySQL ON DELETE CASCADE操作影響的表的技巧
有時,當要從表中刪除資料時,知道哪個表受到MySQL ON DELETE CASCADE
參考操作的影響是有用的。 可從information_schema
資料庫中的referential_constraints
表中查詢此資料,如下所示:
USE information_schema;
SELECT
table_name
FROM
referential_constraints
WHERE
constraint_schema = 'database_name'
AND referenced_table_name = 'parent_table'
AND delete_rule = 'CASCADE'
例如,要使用範例資料庫(testdb
,因為上面兩個表是建立在testdb
資料庫之上的)中的CASCADE
刪除規則查詢與建築表相關聯的表,請使用以下查詢:
USE information_schema;
SELECT
table_name
FROM
referential_constraints
WHERE
constraint_schema = 'testdb'
AND referenced_table_name = 'buildings'
AND delete_rule = 'CASCADE'
執行上面查詢語句,得到以下結果 -
+------------+
| table_name |
+------------+
| rooms |
+------------+
1 row in set
在本教學中,我們一步一步向您展示了如何在從父表中刪除資料時,使用MySQL ON DELETE CASCADE
參照操作從外來鍵自動從子表中刪除相關聯的資料。