今天产品有个操作,要求将满足某个条件的主表和相关联的几个子表的数据全部删除,其实这个要求很简单,如果子表在创建外键的时候指定了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,防止这些无谓的麻烦。
接着想到了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,防止这些无谓的麻烦。
转载于:https://blog.51cto.com/zhaizhenxing/134530