MySql级联删除和更新

约定不等于承诺〃 2022-06-06 09:14 334阅读 0赞

（一）利用外键实现级联删除

1、先建立测试数据库

\[sql\] [view plain][] [copy][view plain]

1.  **CREATE** **TABLE** \`roottb\` (  
2.    \`id\` **INT**(11) UNSIGNED AUTO\_INCREMENT NOT NULL,  
3.    \`data\` **VARCHAR**(100) NOT NULL **DEFAULT** '',  
4.    **PRIMARY** **KEY** (\`id\`)  
5.  ) TYPE=InnoDB;  
6.    
7.  **CREATE** **TABLE** \`subtb\` (  
8.    \`id\` **INT**(11) UNSIGNED AUTO\_INCREMENT NOT NULL,  
9.    \`rootid\` **INT**(11) UNSIGNED NOT NULL **DEFAULT** '0',  
10.   \`data\` **VARCHAR**(100) NOT NULL **DEFAULT** '',  
11.   **PRIMARY** **KEY** (\`id\`),  
12.   **INDEX** (\`rootid\`),  
13.   **FOREIGN** **KEY** (\`rootid\`) **REFERENCES** roottb(\`id\`) **ON** **DELETE** **CASCADE**  
14. ) TYPE=InnoDB;

注意：数据表必须使用InnoDB引擎。

外键必须建立索引

外键绑定关系使用了“ on delete cascade ”

2、插入测试数据

\[sql\] [view plain][] [copy][view plain]

1.  **INSERT** **INTO** \`roottb\` (\`id\`,\`data\`)  
2.    **VALUES** ('1', 'test root line 1'),  
3.           ('2', 'test root line 2'),  
4.           ('3', 'test root line 3');  
5.    
6.  **INSERT** **INTO** \`subtb\` (\`id\`,\`rootid\`,\`data\`)  
7.    **VALUES** ('1', '1', 'test sub line 1 for root 1'),  
8.           ('2', '1', 'test sub line 2 for root 1'),  
9.           ('3', '1', 'test sub line 3 for root 1'),  
10.          ('4', '2', 'test sub line 1 for root 2'),  
11.          ('5', '2', 'test sub line 2 for root 2'),  
12.          ('6', '2', 'test sub line 3 for root 2'),  
13.          ('7', '3', 'test sub line 1 for root 3'),  
14.          ('8', '3', 'test sub line 2 for root 3'),  
15.          ('9', '3', 'test sub line 3 for root 3');

3、查看数据表的状态

4、试验级联删除功能

只删除roottb表中id为2的数据记录，看看subtb表中rootid为2的相关子纪录是否会自动删除！

\[sql\] [view plain][] [copy][view plain]

1.  mysql>; **delete** **from** \`roottb\` **where** \`id\`='2';  
2.  Query OK, 1 row affected (0.03 sec)  
3.    
4.  mysql>; **select** \* **from** \`roottb\`;  
5.  \+\----+------------------+  
6.  | id | data             |  
7.  \+\----+------------------+  
8.  |  1 | test root line 1 |  
9.  |  3 | test root line 3 |  
10. \+\----+------------------+  
11. 2 **rows** in **set** (0.00 sec)  
12.   
13. mysql>; **select** \* **from** \`subtb\`;  
14. \+\----+--------+----------------------------+  
15. | id | rootid | data                       |  
16. \+\----+--------+----------------------------+  
17. |  1 |      1 | test sub line 1 **for** root 1 |  
18. |  2 |      1 | test sub line 2 **for** root 1 |  
19. |  3 |      1 | test sub line 3 **for** root 1 |  
20. |  7 |      3 | test sub line 1 **for** root 3 |  
21. |  8 |      3 | test sub line 2 **for** root 3 |  
22. |  9 |      3 | test sub line 3 **for** root 3 |  
23. \+\----+--------+----------------------------+  
24. 6 **rows** in **set** (0.01 sec)

以上部分转自于：[bbs.chinaunix.net/forum.php?mod=viewthread&tid=462977][bbs.chinaunix.net_forum.php_mod_viewthread_tid_462977]

（二）利用触发器实现级联删除

下面给出实例

1、建立测试数据库

\[sql\] [view plain][] [copy][view plain]

1.  **CREATE** **TABLE** \`root\_trigger\` (  
2.    \`id\` **INT**(11) UNSIGNED AUTO\_INCREMENT NOT NULL,  
3.    \`data\` **VARCHAR**(100) NOT NULL **DEFAULT** '',  
4.    **PRIMARY** **KEY** (\`id\`)  
5.  );  
6.    
7.  **CREATE** **TABLE** \`sub\_trigger\` (  
8.    \`id\` **INT**(11) UNSIGNED AUTO\_INCREMENT NOT NULL,  
9.    \`rootid\` **INT**(11) UNSIGNED NOT NULL **DEFAULT** '0',  
10.   \`data\` **VARCHAR**(100) NOT NULL **DEFAULT** '',  
11.   **PRIMARY** **KEY** (\`id\`)    
12. );

2、插入测试数据

\[sql\] [view plain][] [copy][view plain]

1.  **INSERT** **INTO** \`root\_trigger\` (\`id\`,\`data\`)  
2.    **VALUES** ('1', 'test root line 1'),  
3.           ('2', 'test root line 2'),  
4.           ('3', 'test root line 3');  
5.    
6.  **INSERT** **INTO** \`sub\_trigger\` (\`id\`,\`rootid\`,\`data\`)  
7.    **VALUES** ('1', '1', 'test sub line 1 for root 1'),  
8.           ('2', '1', 'test sub line 2 for root 1'),  
9.           ('3', '1', 'test sub line 3 for root 1'),  
10.          ('4', '2', 'test sub line 1 for root 2'),  
11.          ('5', '2', 'test sub line 2 for root 2'),  
12.          ('6', '2', 'test sub line 3 for root 2'),  
13.          ('7', '3', 'test sub line 1 for root 3'),  
14.          ('8', '3', 'test sub line 2 for root 3'),  
15.          ('9', '3', 'test sub line 3 for root 3');

3、建立（级联/同步）删除的触发器

\[sql\] [view plain][] [copy][view plain]

1.  **drop** **trigger** if exists t\_afterdelete\_on\_sub;  
2.  **create** **trigger** t\_afterdelete\_on\_sub  
3.  **after** **delete** **on** root\_trigger  
4.  **for** each row  
5.  **begin**   
6.        **delete** **from** sub\_trigger **where** rootid=old.id;        
7.  **end**;

4、删除root\_trigger表中的id=2的记录，再查看sub\_trigger中rootid=2的记录是否删除

删除成功即实现了，触发器的级联删除！

转载网址：[http://blog.csdn.net/doupei2006/article/details/7949077][http_blog.csdn.net_doupei2006_article_details_7949077]

[view plain]: http://blog.csdn.net/doupei2006/article/details/7949077#
[bbs.chinaunix.net_forum.php_mod_viewthread_tid_462977]: http://blog.csdn.net/doupei2006/article/details/bbs.chinaunix.net/forum.php?mod=viewthread&tid=462977
[http_blog.csdn.net_doupei2006_article_details_7949077]: http://blog.csdn.net/doupei2006/article/details/7949077