mysql删除关联表_MySQL主外键表关联表数据的同时删除

今天做了实验，要求将满足某个条件的主表和相关联的几个子表的数据全部删除，其实这个要求很简单，如果子表在创建外键的时候指定了ON DELETE CASCADE，则直接从主表中删除相关记录，子表中数据也会一起删除。但是现在的子表外键创建时候没有加此语句，如何来实现呢？

条件：p(父表)没有ON DELETE CASCADEc(子表)

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)

另一种方式： 先删除最外层的子表，一层一层向里删除，最后删除父表。