数据库|mysql教程
MySQL外键,MySQL主外键表关联表数据的同时删除,数据
数据库-mysql教程
asp 源码 解密,vscode自己制作主题,ubuntu查询用户,用tomcat运行java,scrapy 关闭爬虫,php打开页面跳转,石首seo关键字优化,php 视频网站系统源码下载,苹果cms8x仿爱奇艺模板lzw
要求将满足某个条件的主表和相关联的几个子表的数据全部删除，其实这个要求很简单，如果子表在创建外键的时候指定了ON DELETE CA
.net仓储管理系统源码下载,ubuntu安全加固脚本,tomcat太难配了怎么办,美团网站爬虫,php一维数组重复计数,兴宁seo托管lzw
免费asp论坛源码,ubuntu应用图标路径,养爬虫新手蜘蛛,php?url=,泰州seo监控lzw
条件：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)
另一种方式： 先删除最外层的子表，一层一层向里删除，最后删除父表。
