MySQL on delete cascade語句

2019-10-16 22:57:27

在本教學中,您將學習如何使用MySQL ON DELETE CASCADE參照操作來執行外來鍵從多個相關表中刪除資料。

在上一個教學中,我們學習了如何使用單個DELETE語句從一個或多個相關表中刪除資料。但是,MySQL提供了一種更為有效的方法,稱為ON DELETE CASCADE對於外來鍵的參照操作,可以實現在從父表中刪除資料時自動刪除子表中的資料。

1. 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=22個房間。

第七步, 刪除編號為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的外來鍵,因此應該在使用MySQL ON 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參照操作從外來鍵自動從子表中刪除相關聯的資料。