今天产品有个操作，要求将满足某个条件的主表和相关联的几个子表的数据全部删除，其实这个要求很简单，如果子表在创建外键的时候指定了ON DELETE CASCADE，则直接从主表中删除相关记录，子表中数据也会一起删除。但是现在的子表外键创建时候没有加此语句，看来此方法不通；
        接着想到了mysql里面支持一次进行多表删除，即delete a,b from a,b where ...这种语法，尝试如下：

mysql> delete a,b from parent a,child2 b where a.id=b.parent_id and a.id=2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test/child2`, CONSTRAINT `child2_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))

 显然，违反了外键约束而报错，也就是说上述语句是按照先主后从的的顺序进行删除操作，而从表中由于存在相应记录而导致删除失败；那么，如果将表的删除顺序颠倒一下就可以按照先从后主的顺序删除呢？我们测试一下：

mysql> delete a,b from child2 a,parent b where a.parent_id=b.id and b.id=2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test/child2`, CONSTRAINT `child2_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))

看来还是不行，母子表的关联删除看来和表的连接顺序无关，我们再次验证一下：
mysql> set profiling=1;
Query OK, 0 rows affected (0.03 sec)

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

mysql> show profiles;
+----------+------------+--------------------+
| Query_ID | Duration   | Query              |
+----------+------------+--------------------+
|        1 | 0.00011900 | select @@profiling |
+----------+------------+--------------------+
1 row in set (0.00 sec)

mysql> delete a,b from child2 a,parent b where a.parent_id=b.id and b.id=2;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test/child2`, CONSTRAINT `child2_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`))
mysql> show profiles;
+----------+------------+---------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                               |
+----------+------------+---------------------------------------------------------------------+
|        1 | 0.00011900 | select @@profiling                                                  |
|        2 | 0.03273800 | delete a,b from child2 a,parent b where a.parent_id=b.id and b.id=2 |
+----------+------------+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> show profile for query 2;
+--------------------------+----------+
| Status                   | Duration |
+--------------------------+----------+
| (initialization)         | 0.000077 |
| init                     | 0.000011 |
| Opening tables           | 0.000018 |
| System lock              | 0.000009 |
| Table lock               | 0.00002  |
| init                     | 0.000027 |
| deleting from main table | 0.000007 |
| optimizing               | 0.000016 |
| statistics               | 0.000106 |
| preparing                | 0.000024 |
| executing                | 0.000007 |
| Sending data             | 0.000615 |
| end                      | 0.000017 |
| query end                | 0.000008 |
| freeing items            | 0.000015 |
| closing tables           | 0.031749 |
| logging slow query       | 0.000012 |
+--------------------------+----------+
17 rows in set (0.03 sec)

可以看到，执行步骤中有一句“deleting from main table”，也就是从主表删除，而没有删除子表的语句，看来子表的数据按照这种方式是无法删除的。

        那么如果指定了on delete cascade，执行步骤又是什么样呢？
mysql> delete parent from parent where id=2;;
Query OK, 1 row affected (0.03 sec)
mysql> show profile for query 14
    -> ;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| (initialization)               | 0.000062 |
| init                           | 0.000008 |
| Opening tables                 | 0.000018 |
| System lock                    | 0.000008 |
| Table lock                     | 0.000018 |
| init                           | 0.000023 |
| deleting from main table       | 0.000007 |
| optimizing                     | 0.000012 |
| statistics                     | 0.000068 |
| preparing                      | 0.000015 |
| executing                      | 0.000007 |
| Sending data                   | 0.000145 |
| deleting from reference tables | 0.000007 |
| end                            | 0.02809  |
| query end                      | 0.000014 |
| freeing items                  | 0.000016 |
| closing tables                 | 0.000017 |
| logging slow query             | 0.000006 |
+--------------------------------+----------+
18 rows in set (0.00 sec)

这次，多了一步“deleting from reference tables ”，显然主从表都会进行删除操作的。

最后，只能按照最笨的办法，先删除子表，再删除主表：
mysql> delete from child2 where id=3;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from parent where id=3;
Query OK, 1 row affected (0.03 sec)

如果有多层的母子表，则按照从最内层的子表开始，一直到最外层的主表的顺利来完成删除操作，如下：
delete d
from a,b,c,d
where a.id=b.order_id  and b.id=c.order_record_id  and c.id=d.ORDER_FACTORY_ID
and a.old_id>=20171059 and (a.card_type_id=21 or a.card_type_id=22) and a.guangzhou_order_id is not null;

delete c
from a,b,c
where a.id=b.order_id  and b.id=c.order_record_id
and a.old_id>=20171059 and (a.card_type_id=21 or a.card_type_id=22) and a.guangzhou_order_id is not null;

delete b
from a,b
where a.id=b.order_id
and a.old_id>=20171059 and (a.card_type_id=21 or a.card_type_id=22) and a.guangzhou_order_id is not null;

delete a
from order_tab a
where a.old_id>=20171059 and (a.card_type_id=21 or a.card_type_id=22) and a.guangzhou_order_id is not null;

看来设置外键的时候还是应该考虑好on delete 和on udpate的操作action，防止这些无谓的麻烦。