定义外键级联删除属性就可以了, 如FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE
[转贴] 在MySQL中利用外键实现级联删除以下为原创,转载请注明出处! 作者:Dirk (dirk.ye AT gmail.com) Url:http://dirk.pdx.cn 日期:2004/12/08 首先,目前在产品环境可用的MySQL版本(指4.0.x和4.1.x)中,只有InnoDB引擎才允许使用外键,所以,我们的数据表必须使用InnoDB引擎。 下面,我们先创建以下测试用数据库表: CREATE TABLE `roottb` ( `id` INT(11) UNSIGNED AUTO_INCREMENT NOT NULL, `data` VARCHAR(100) NOT NULL DEFAULT '', PRIMARY KEY (`id`) ) TYPE=InnoDB;CREATE TABLE `subtb` ( `id` INT(11) UNSIGNED AUTO_INCREMENT NOT NULL, `rootid` INT(11) UNSIGNED NOT NULL DEFAULT '0', `data` VARCHAR(100) NOT NULL DEFAULT '', PRIMARY KEY (`id`), INDEX (`rootid`), FOREIGN KEY (`rootid`) REFERENCES roottb(`id`) ON DELETE CASCADE ) TYPE=InnoDB;注意: 1、必须使用InnoDB引擎; 2、外键必须建立索引(INDEX); 3、外键绑定关系这里使用了“ ON DELETE CASCADE”,意思是如果外键对应数据被删除,将关联数据完全删除,更多信息请参考MySQL手册中关于InnoDB的文档; 好,接着我们再来插入测试数据: INSERT INTO `roottb` (`id`,`data`) VALUES ('1', 'test root line 1'), ('2', 'test root line 2'), ('3', 'test root line 3');INSERT INTO `subtb` (`id`,`rootid`,`data`) VALUES ('1', '1', 'test sub line 1 for root 1'), ('2', '1', 'test sub line 2 for root 1'), ('3', '1', 'test sub line 3 for root 1'), ('4', '2', 'test sub line 1 for root 2'), ('5', '2', 'test sub line 2 for root 2'), ('6', '2', 'test sub line 3 for root 2'), ('7', '3', 'test sub line 1 for root 3'), ('8', '3', 'test sub line 2 for root 3'), ('9', '3', 'test sub line 3 for root 3'); 我们先看一下当前数据表的状态: mysql>; show tables; +----------------+ | Tables_in_test | +----------------+ | roottb | | subtb | +----------------+ 2 rows in set (0.00 sec)mysql>; select * from `roottb`; +----+------------------+ | id | data | +----+------------------+ | 1 | test root line 1 | | 2 | test root line 2 | | 3 | test root line 3 | +----+------------------+ 3 rows in set (0.05 sec)mysql>; select * from `subtb`; +----+--------+----------------------------+ | id | rootid | data | +----+--------+----------------------------+ | 1 | 1 | test sub line 1 for root 1 | | 2 | 1 | test sub line 2 for root 1 | | 3 | 1 | test sub line 3 for root 1 | | 4 | 2 | test sub line 1 for root 2 | | 5 | 2 | test sub line 2 for root 2 | | 6 | 2 | test sub line 3 for root 2 | | 7 | 3 | test sub line 1 for root 3 | | 8 | 3 | test sub line 2 for root 3 | | 9 | 3 | test sub line 3 for root 3 | +----+--------+----------------------------+ 9 rows in set (0.01 sec)嗯,一切都正常,好,下面我们要试验我们的级联删除功能了。 我们将只删除roottb表中id为2的数据记录,看看subtb表中rootid为2的相关子纪录是否会自动删除: mysql>; delete from `roottb` where `id`='2'; Query OK, 1 row affected (0.03 sec)mysql>; select * from `roottb`; +----+------------------+ | id | data | +----+------------------+ | 1 | test root line 1 | | 3 | test root line 3 | +----+------------------+ 2 rows in set (0.00 sec)mysql>; select * from `subtb`; +----+--------+----------------------------+ | id | rootid | data | +----+--------+----------------------------+ | 1 | 1 | test sub line 1 for root 1 | | 2 | 1 | test sub line 2 for root 1 | | 3 | 1 | test sub line 3 for root 1 | | 7 | 3 | test sub line 1 for root 3 | | 8 | 3 | test sub line 2 for root 3 | | 9 | 3 | test sub line 3 for root 3 | +----+--------+----------------------------+ 6 rows in set (0.01 sec)嗯,看subtb表中对应数据确实自动删除了,测试成功。 结论:在MySQL中利用外键实现级联删除成功!
1. 使用用支持外键的存储引擎,比如innodb 2. 创建外键的时候指定 ON DELETE CASCADE 如一楼所给出的。
我的是MYSQL5.0版本你写的是网上版本为4.1-4.2之间的 -。0、
贴出你的测试用代码,如果是MYSQL5,完全应该可以啊。
CREATE TABLE `roottb` ( `id` INT(11) UNSIGNED AUTO_INCREMENT NOT NULL, `data` VARCHAR(100) NOT NULL DEFAULT '', PRIMARY KEY (`id`) ) TYPE=InnoDB; CREATE TABLE `subtb` ( `id` INT(11) UNSIGNED AUTO_INCREMENT NOT NULL, `rootid` INT(11) UNSIGNED NOT NULL DEFAULT '0', `data` VARCHAR(100) NOT NULL DEFAULT '', PRIMARY KEY (`id`), INDEX (`rootid`), FOREIGN KEY (`rootid`) REFERENCES roottb(`id`) ON DELETE CASCADE ) TYPE=InnoDB; INSERT INTO `roottb` (`id`,`data`) VALUES ('1', 'test root line 1'), ('2', 'test root line 2'), mysql>; delete from `roottb` where `id`='2'; Query OK, 1 row affected (0.03 sec) mysql>; select * from `roottb`; +----+------------------+ | id | data | +----+------------------+ | 1 | test root line 1 | | 3 | test root line 3 | +----+------------------+ 2 rows in set (0.00 sec) mysql>; select * from `subtb`; ('3', 'test root line 3'); INSERT INTO `subtb` (`id`,`rootid`,`data`) VALUES ('1', '1', 'test sub line 1 for root 1'), ('2', '1', 'test sub line 2 for root 1'), ('3', '1', 'test sub line 3 for root 1'), ('4', '2', 'test sub line 1 for root 2'), ('5', '2', 'test sub line 2 for root 2'), ('6', '2', 'test sub line 3 for root 2'), ('7', '3', 'test sub line 1 for root 3'), ('8', '3', 'test sub line 2 for root 3'), ('9', '3', 'test sub line 3 for root 3'); mysql>; select * from `subtb`; +----+--------+----------------------------+ | id | rootid | data | +----+--------+----------------------------+ | 1 | 1 | test sub line 1 for root 1 | | 2 | 1 | test sub line 2 for root 1 | | 3 1 | test sub line 3 for root 1 | 4 2 | test sub line 1 for root 2 | | 5 | 2 | test sub line 2 for root 2| | 6 | 2 | test sub line 3 for root 2 | | 7 3 | test sub line 1 for root 3 | 8 3 | test sub line 2 for root 3 9 3 | test sub line 3 for root 3 | +----+--------+----------------------------+
用你的代码测试的,没有任何问题!mysql> use csdn; Database changed mysql> CREATE TABLE `roottb` ( -> `id` INT(11) UNSIGNED AUTO_INCREMENT NOT NULL, -> `data` VARCHAR(100) NOT NULL DEFAULT '', -> PRIMARY KEY (`id`) -> ) TYPE=InnoDB; Query OK, 0 rows affected, 1 warning (0.11 sec)mysql> CREATE TABLE `subtb` ( -> `id` INT(11) UNSIGNED AUTO_INCREMENT NOT NULL, -> `rootid` INT(11) UNSIGNED NOT NULL DEFAULT '0', -> `data` VARCHAR(100) NOT NULL DEFAULT '', -> PRIMARY KEY (`id`), -> INDEX (`rootid`), -> FOREIGN KEY (`rootid`) REFERENCES roottb(`id`) ON DELETE CASCADE -> ) TYPE=InnoDB; Query OK, 0 rows affected, 1 warning (0.06 sec) mysql> INSERT INTO `roottb` (`id`,`data`) -> VALUES ('1', 'test root line 1'), -> ('2', 'test root line 2'), -> ('3', 'test root line 3'); Query OK, 3 rows affected (0.06 sec) Records: 3 Duplicates: 0 Warnings: 0mysql> select * from `roottb`; +----+------------------+ | id | data | +----+------------------+ | 1 | test root line 1 | | 2 | test root line 2 | | 3 | test root line 3 | +----+------------------+ 3 rows in set (0.00 sec)mysql> INSERT INTO `subtb` (`id`,`rootid`,`data`) -> VALUES ('1', '1', 'test sub line 1 for root 1'), -> ('2', '1', 'test sub line 2 for root 1'), -> ('3', '1', 'test sub line 3 for root 1'), -> ('4', '2', 'test sub line 1 for root 2'), -> ('5', '2', 'test sub line 2 for root 2'), -> ('6', '2', 'test sub line 3 for root 2'), -> ('7', '3', 'test sub line 1 for root 3'), -> ('8', '3', 'test sub line 2 for root 3'), -> ('9', '3', 'test sub line 3 for root 3'); Query OK, 9 rows affected (0.08 sec) Records: 9 Duplicates: 0 Warnings: 0mysql> select * from `subtb`; +----+--------+----------------------------+ | id | rootid | data | +----+--------+----------------------------+ | 1 | 1 | test sub line 1 for root 1 | | 2 | 1 | test sub line 2 for root 1 | | 3 | 1 | test sub line 3 for root 1 | | 4 | 2 | test sub line 1 for root 2 | | 5 | 2 | test sub line 2 for root 2 | | 6 | 2 | test sub line 3 for root 2 | | 7 | 3 | test sub line 1 for root 3 | | 8 | 3 | test sub line 2 for root 3 | | 9 | 3 | test sub line 3 for root 3 | +----+--------+----------------------------+ 9 rows in set (0.00 sec)mysql> mysql> delete from `roottb` where `id`='2'; Query OK, 1 row affected (0.06 sec)mysql> select * from `roottb`; +----+------------------+ | id | data | +----+------------------+ | 1 | test root line 1 | | 3 | test root line 3 | +----+------------------+ 2 rows in set (0.00 sec)mysql> select * from `subtb`; +----+--------+----------------------------+ | id | rootid | data | +----+--------+----------------------------+ | 1 | 1 | test sub line 1 for root 1 | | 2 | 1 | test sub line 2 for root 1 | | 3 | 1 | test sub line 3 for root 1 | | 7 | 3 | test sub line 1 for root 3 | | 8 | 3 | test sub line 2 for root 3 | | 9 | 3 | test sub line 3 for root 3 | +----+--------+----------------------------+ 6 rows in set (0.00 sec)mysql> select version(); +----------------------+ | version() | +----------------------+ | 5.1.33-community-log | +----------------------+ 1 row in set (0.02 sec)mysql> show variables like 'foreign_key_checks'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | foreign_key_checks | ON | +--------------------+-------+ 1 row in set (0.00 sec)mysql> 检查一下你的 foreign_key_checks 是什么。
mysql> set @@foreign_key_checks =1; Query OK, 0 rows affected (0.00 sec)mysql> show variables like 'foreign_key_checks'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | foreign_key_checks | ON | +--------------------+-------+ 1 row in set (0.00 sec)
是不是要mysql5.1才支持外键约束啊?我的也没有唉!mysql> select version(); +---------------------+ | version() | +---------------------+ | 5.0.45-community-nt | +---------------------+ 1 row in set (0.02 sec)mysql> show variables like 'foreign_key_checks'; Empty set (0.00 sec)
如FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE
Url:http://dirk.pdx.cn
日期:2004/12/08
首先,目前在产品环境可用的MySQL版本(指4.0.x和4.1.x)中,只有InnoDB引擎才允许使用外键,所以,我们的数据表必须使用InnoDB引擎。 下面,我们先创建以下测试用数据库表:
CREATE TABLE `roottb` (
`id` INT(11) UNSIGNED AUTO_INCREMENT NOT NULL,
`data` VARCHAR(100) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) TYPE=InnoDB;CREATE TABLE `subtb` (
`id` INT(11) UNSIGNED AUTO_INCREMENT NOT NULL,
`rootid` INT(11) UNSIGNED NOT NULL DEFAULT '0',
`data` VARCHAR(100) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
INDEX (`rootid`),
FOREIGN KEY (`rootid`) REFERENCES roottb(`id`) ON DELETE CASCADE
) TYPE=InnoDB;注意:
1、必须使用InnoDB引擎;
2、外键必须建立索引(INDEX);
3、外键绑定关系这里使用了“ ON DELETE CASCADE”,意思是如果外键对应数据被删除,将关联数据完全删除,更多信息请参考MySQL手册中关于InnoDB的文档; 好,接着我们再来插入测试数据:
INSERT INTO `roottb` (`id`,`data`)
VALUES ('1', 'test root line 1'),
('2', 'test root line 2'),
('3', 'test root line 3');INSERT INTO `subtb` (`id`,`rootid`,`data`)
VALUES ('1', '1', 'test sub line 1 for root 1'),
('2', '1', 'test sub line 2 for root 1'),
('3', '1', 'test sub line 3 for root 1'),
('4', '2', 'test sub line 1 for root 2'),
('5', '2', 'test sub line 2 for root 2'),
('6', '2', 'test sub line 3 for root 2'),
('7', '3', 'test sub line 1 for root 3'),
('8', '3', 'test sub line 2 for root 3'),
('9', '3', 'test sub line 3 for root 3'); 我们先看一下当前数据表的状态:
mysql>; show tables;
+----------------+
| Tables_in_test |
+----------------+
| roottb |
| subtb |
+----------------+
2 rows in set (0.00 sec)mysql>; select * from `roottb`;
+----+------------------+
| id | data |
+----+------------------+
| 1 | test root line 1 |
| 2 | test root line 2 |
| 3 | test root line 3 |
+----+------------------+
3 rows in set (0.05 sec)mysql>; select * from `subtb`;
+----+--------+----------------------------+
| id | rootid | data |
+----+--------+----------------------------+
| 1 | 1 | test sub line 1 for root 1 |
| 2 | 1 | test sub line 2 for root 1 |
| 3 | 1 | test sub line 3 for root 1 |
| 4 | 2 | test sub line 1 for root 2 |
| 5 | 2 | test sub line 2 for root 2 |
| 6 | 2 | test sub line 3 for root 2 |
| 7 | 3 | test sub line 1 for root 3 |
| 8 | 3 | test sub line 2 for root 3 |
| 9 | 3 | test sub line 3 for root 3 |
+----+--------+----------------------------+
9 rows in set (0.01 sec)嗯,一切都正常,好,下面我们要试验我们的级联删除功能了。 我们将只删除roottb表中id为2的数据记录,看看subtb表中rootid为2的相关子纪录是否会自动删除:
mysql>; delete from `roottb` where `id`='2';
Query OK, 1 row affected (0.03 sec)mysql>; select * from `roottb`;
+----+------------------+
| id | data |
+----+------------------+
| 1 | test root line 1 |
| 3 | test root line 3 |
+----+------------------+
2 rows in set (0.00 sec)mysql>; select * from `subtb`;
+----+--------+----------------------------+
| id | rootid | data |
+----+--------+----------------------------+
| 1 | 1 | test sub line 1 for root 1 |
| 2 | 1 | test sub line 2 for root 1 |
| 3 | 1 | test sub line 3 for root 1 |
| 7 | 3 | test sub line 1 for root 3 |
| 8 | 3 | test sub line 2 for root 3 |
| 9 | 3 | test sub line 3 for root 3 |
+----+--------+----------------------------+
6 rows in set (0.01 sec)嗯,看subtb表中对应数据确实自动删除了,测试成功。 结论:在MySQL中利用外键实现级联删除成功!
2. 创建外键的时候指定 ON DELETE CASCADE 如一楼所给出的。
贴出你的测试用代码,如果是MYSQL5,完全应该可以啊。
`id` INT(11) UNSIGNED AUTO_INCREMENT NOT NULL,
`data` VARCHAR(100) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) TYPE=InnoDB; CREATE TABLE `subtb` (
`id` INT(11) UNSIGNED AUTO_INCREMENT NOT NULL,
`rootid` INT(11) UNSIGNED NOT NULL DEFAULT '0',
`data` VARCHAR(100) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
INDEX (`rootid`),
FOREIGN KEY (`rootid`) REFERENCES roottb(`id`) ON DELETE CASCADE
) TYPE=InnoDB;
INSERT INTO `roottb` (`id`,`data`)
VALUES ('1', 'test root line 1'),
('2', 'test root line 2'),
mysql>; delete from `roottb` where `id`='2';
Query OK, 1 row affected (0.03 sec) mysql>; select * from `roottb`;
+----+------------------+
| id | data |
+----+------------------+
| 1 | test root line 1 |
| 3 | test root line 3 |
+----+------------------+
2 rows in set (0.00 sec) mysql>; select * from `subtb`;
('3', 'test root line 3'); INSERT INTO `subtb` (`id`,`rootid`,`data`)
VALUES ('1', '1', 'test sub line 1 for root 1'),
('2', '1', 'test sub line 2 for root 1'),
('3', '1', 'test sub line 3 for root 1'),
('4', '2', 'test sub line 1 for root 2'),
('5', '2', 'test sub line 2 for root 2'),
('6', '2', 'test sub line 3 for root 2'),
('7', '3', 'test sub line 1 for root 3'),
('8', '3', 'test sub line 2 for root 3'),
('9', '3', 'test sub line 3 for root 3');
mysql>; select * from `subtb`;
+----+--------+----------------------------+
| id | rootid | data |
+----+--------+----------------------------+
| 1 | 1 | test sub line 1 for root 1 |
| 2 | 1 | test sub line 2 for root 1 |
| 3 1 | test sub line 3 for root 1
| 4 2 | test sub line 1 for root 2 |
| 5 | 2 | test sub line 2 for root 2|
| 6 | 2 | test sub line 3 for root 2 |
| 7 3 | test sub line 1 for root 3
| 8 3 | test sub line 2 for root 3
9 3 | test sub line 3 for root 3 |
+----+--------+----------------------------+
Database changed
mysql> CREATE TABLE `roottb` (
-> `id` INT(11) UNSIGNED AUTO_INCREMENT NOT NULL,
-> `data` VARCHAR(100) NOT NULL DEFAULT '',
-> PRIMARY KEY (`id`)
-> ) TYPE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.11 sec)mysql> CREATE TABLE `subtb` (
-> `id` INT(11) UNSIGNED AUTO_INCREMENT NOT NULL,
-> `rootid` INT(11) UNSIGNED NOT NULL DEFAULT '0',
-> `data` VARCHAR(100) NOT NULL DEFAULT '',
-> PRIMARY KEY (`id`),
-> INDEX (`rootid`),
-> FOREIGN KEY (`rootid`) REFERENCES roottb(`id`) ON DELETE CASCADE
-> ) TYPE=InnoDB;
Query OK, 0 rows affected, 1 warning (0.06 sec)
mysql> INSERT INTO `roottb` (`id`,`data`)
-> VALUES ('1', 'test root line 1'),
-> ('2', 'test root line 2'),
-> ('3', 'test root line 3');
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0mysql> select * from `roottb`;
+----+------------------+
| id | data |
+----+------------------+
| 1 | test root line 1 |
| 2 | test root line 2 |
| 3 | test root line 3 |
+----+------------------+
3 rows in set (0.00 sec)mysql> INSERT INTO `subtb` (`id`,`rootid`,`data`)
-> VALUES ('1', '1', 'test sub line 1 for root 1'),
-> ('2', '1', 'test sub line 2 for root 1'),
-> ('3', '1', 'test sub line 3 for root 1'),
-> ('4', '2', 'test sub line 1 for root 2'),
-> ('5', '2', 'test sub line 2 for root 2'),
-> ('6', '2', 'test sub line 3 for root 2'),
-> ('7', '3', 'test sub line 1 for root 3'),
-> ('8', '3', 'test sub line 2 for root 3'),
-> ('9', '3', 'test sub line 3 for root 3');
Query OK, 9 rows affected (0.08 sec)
Records: 9 Duplicates: 0 Warnings: 0mysql> select * from `subtb`;
+----+--------+----------------------------+
| id | rootid | data |
+----+--------+----------------------------+
| 1 | 1 | test sub line 1 for root 1 |
| 2 | 1 | test sub line 2 for root 1 |
| 3 | 1 | test sub line 3 for root 1 |
| 4 | 2 | test sub line 1 for root 2 |
| 5 | 2 | test sub line 2 for root 2 |
| 6 | 2 | test sub line 3 for root 2 |
| 7 | 3 | test sub line 1 for root 3 |
| 8 | 3 | test sub line 2 for root 3 |
| 9 | 3 | test sub line 3 for root 3 |
+----+--------+----------------------------+
9 rows in set (0.00 sec)mysql>
mysql> delete from `roottb` where `id`='2';
Query OK, 1 row affected (0.06 sec)mysql> select * from `roottb`;
+----+------------------+
| id | data |
+----+------------------+
| 1 | test root line 1 |
| 3 | test root line 3 |
+----+------------------+
2 rows in set (0.00 sec)mysql> select * from `subtb`;
+----+--------+----------------------------+
| id | rootid | data |
+----+--------+----------------------------+
| 1 | 1 | test sub line 1 for root 1 |
| 2 | 1 | test sub line 2 for root 1 |
| 3 | 1 | test sub line 3 for root 1 |
| 7 | 3 | test sub line 1 for root 3 |
| 8 | 3 | test sub line 2 for root 3 |
| 9 | 3 | test sub line 3 for root 3 |
+----+--------+----------------------------+
6 rows in set (0.00 sec)mysql> select version();
+----------------------+
| version() |
+----------------------+
| 5.1.33-community-log |
+----------------------+
1 row in set (0.02 sec)mysql> show variables like 'foreign_key_checks';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| foreign_key_checks | ON |
+--------------------+-------+
1 row in set (0.00 sec)mysql>
检查一下你的 foreign_key_checks 是什么。
Query OK, 0 rows affected (0.00 sec)mysql> show variables like 'foreign_key_checks';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| foreign_key_checks | ON |
+--------------------+-------+
1 row in set (0.00 sec)
+---------------------+
| version() |
+---------------------+
| 5.0.45-community-nt |
+---------------------+
1 row in set (0.02 sec)mysql> show variables like 'foreign_key_checks';
Empty set (0.00 sec)