MySQL主外鍵表關聯表資料的同時刪除

今天做了實驗，要求將滿足某個條件的主表和相關聯的幾個子表的資料全部刪除，其實這個要求很簡單，如果子表在建立外鍵的時候指定了ON DELETE CASCADE，則直接從主表中刪除相關記錄，子表中資料也會一起刪除。但是現在的子表外鍵建立時候沒有加此語句，如何來實現呢？

條件：p（父表）沒有ON DELETE CASCADE c（子表）  

mysql> delete a,b from p a,c b where a.id=b.id;

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`hhl`.`c`, CONSTRAINT `FK_P_ID` FOREIGN KEY (`id`) REFERENCES `p` (`id`))

mysql> show profiles ;

+----------+------------+------------------------------------------+

| Query_ID | Duration | Query |

+----------+------------+------------------------------------------+

| 1 | 0.00875600 | delete a,b from p a,c b where a.id=b.id |

| 2 | 0.01294200 | delete a,b from p a,c b where a.id=b.id |

+----------+------------+------------------------------------------+

mysql> show profile for query 2 ;

+--------------------------+----------+

| Status | Duration |

+--------------------------+----------+

| starting | 0.000314 |

| checking permissions | 0.000026 |

| checking permissions | 0.000014 |

| checking permissions | 0.000009 |

| checking permissions | 0.000010 |

| init | 0.000033 |

| Opening tables | 0.000082 |

| System lock | 0.000047 |

| init | 0.000050 |

| deleting from main table | 0.000016 |

| optimizing | 0.000019 |

| statistics | 0.000056 |

| preparing | 0.000042 |

| executing | 0.000054 |

| Sending data | 0.005026 |

| end | 0.000050 |

| query end | 0.003456 |

| closing tables | 0.000143 |

| freeing items | 0.003430 |

| logging slow query | 0.000047 |

| cleaning up | 0.000021 |

+--------------------------+----------+

21 rows in set (0.00 sec)

看出上面沒有刪除子表的操作。

mysql> delete a,b from c a,p b where a.id=b.id;

Query OK, 6 rows affected (0.04 sec)

from 後面 子表在前，刪除成功！！

mysql> show profile for query 3 ;

+--------------------------------+----------+

| Status | Duration |

+--------------------------------+----------+

| starting | 0.000307 |

| checking permissions | 0.000019 |

| checking permissions | 0.000017 |

| checking permissions | 0.000009 |

| checking permissions | 0.000010 |

| init | 0.000021 |

| Opening tables | 0.000091 |

| System lock | 0.000036 |

| init | 0.000047 |

| deleting from main table | 0.000016 |

| optimizing | 0.000125 |

| statistics | 0.000084 |

| preparing | 0.000042 |

| executing | 0.000013 |

| Sending data | 0.000572 |

| deleting from reference tables | 0.000103 |

| end | 0.000015 |

| Waiting for query cache lock | 0.000009 |

| end | 0.000010 |

| Waiting for query cache lock | 0.000008 |

| end | 0.000160 |

| end | 0.000022 |

| query end | 0.030033 |

| closing tables | 0.000081 |

| freeing items | 0.001465 |

| logging slow query | 0.000052 |

| cleaning up | 0.000011 |

+--------------------------------+----------+

27 rows in set (0.00 sec)

另一種方式： 先刪除最外層的子表，一層一層向裡刪除，最後刪除父表。