注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

帅小伙的博客

希望能在这里交到更多的朋友

 
 
 

日志

 
 
 
 

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

2008-07-31 18:28:40|  分类: mysql |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
        今天产品有个操作,要求将满足某个条件的主表和相关联的几个子表的数据全部删除,其实这个要求很简单,如果子表在创建外键的时候指定了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,防止这些无谓的麻烦。

  评论这张
 
阅读(1619)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018