mysql級聯操作（實例）

mysql級聯操作（實例）

MySQL支持外鍵的存儲引擎只有InnoDB，在創建外鍵的時候，要求父表必須有對應的索引，子表在創建外鍵的時候也會自動創建對應的索引。在創建索引的時候，可以指定在刪除、更新父表時，對子表進行的相應操作，包括RESTRICT、NOACTION、SET NULL和CASCADE。其中RESTRICT和NO ACTION相同，是指在子表有關聯記錄的情況下父表不能更新；CASCADE表示父表在更新或者刪除時，更新或者刪除子表對應記錄；SET NULL則是表示父表在更新或者刪除的時候，子表的對應字段被SET NULL。下面以一個新聞表說明，該新聞數據庫的結構如下：

create database yynews;
use yynews;
#新聞類別表
create table categories(
catId int AUTO_INCREMENT primary key,
catName varchar(40) not null unique
)charset utf8;
#新聞表：
create table news(
newsId int AUTO_INCREMENT primary key,
title varchar(100) not null unique,
content text not null,
createTime timestamp not null,
catId int
)charset utf8;
#添加外鍵的引用
alter table news add constraint foreign key(catid) references categories(catid);
#評論表：
create table comments(
commId int AUTO_INCREMENT primary key,
content text not null,
createTime timestamp not null,
newsId int not null,
userIP char(15) not null
)charset utf8;
#添加外鍵的引用
alter table comments add constraint foreign key(newsid) references news(newsid);
#插入測試數據
insert into categories(catname) values("娛樂新聞");
insert into categories(catname) values("國際新聞");
insert into news(title,content,createTime,catId) values('test1','test1',now(),1);
insert into news(title,content,createTime,catId) values('test2','test2',now(),2);
insert into news(title,content,createTime,catId) values('test3','test3',now(),1);
insert into comments(content,createTime,newsId,userIP) values('you',now(),1,'127.0.0.1');
insert into comments(content,createTime,newsId,userIP) values('you',now(),2,'127.0.0.1');
insert into comments(content,createTime,newsId,userIP) values('you',now(),3,'127.0.0.1');
insert into comments(content,createTime,newsId,userIP) values('you',now(),1,'127.0.0.1');

如下：

mysql> select * from categories;
+-------+--------------+
| catId | catName |
+-------+--------------+
| 2 | 國際新聞 |
| 1 | 娛樂新聞 |
+-------+--------------+
2 rows in set (0.00 sec)

mysql> select * from news;
+--------+-------+---------+---------------------+-------+
| newsId | title | content | createTime | catId |
+--------+-------+---------+---------------------+-------+
| 1 | test1 | test1 | 2015-05-19 15:22:53 | 1 |
| 2 | test2 | test2 | 2015-05-19 15:22:53 | 2 |
| 3 | test3 | test3 | 2015-05-19 15:22:53 | 1 |
+--------+-------+---------+---------------------+-------+
3 rows in set (0.00 sec)

mysql> select * from comments;
+--------+---------+---------------------+--------+-----------+
| commId | content | createTime | newsId | userIP |
+--------+---------+---------------------+--------+-----------+
| 1 | you | 2015-05-19 15:22:53 | 1 | 127.0.0.1 |
| 2 | you | 2015-05-19 15:22:53 | 2 | 127.0.0.1 |
| 3 | you | 2015-05-19 15:22:53 | 3 | 127.0.0.1 |
| 4 | you | 2015-05-19 15:22:54 | 1 | 127.0.0.1 |
+--------+---------+---------------------+--------+-----------+
4 rows in set (0.00 sec)

在還沒有添加任何的級聯操作的時，刪除有關聯的數據會報錯。

mysql> delete from categories where catid=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`yynews`.
`comments`, CONSTRAINT `comments_ibfk_1` FOREIGN KEY (`newsId`) REFERENCES `news` (`newsId`))

數據庫報錯告訴你有個外鍵阻止了你的操作。所以我們可以添加級聯操作。也可以再創建數據庫的時候就指定級聯操作
如下：

#級聯操作
alter table news add constraint foreign key(catid) references categories(catid) on delete cascade
on update cascade;
alter table comments add constraint foreign key(newsid) references news(newsid) on delete cascade
on update cascade;
#上面這句的這兩個語句就是在添加外鍵的時候為該表和表之間添加級聯操作，即，數據表在刪除或更新數據表時，相

關連的表也會同時更新或刪除。
例如：
mysql> delete from categories where catid=1;
Query OK, 1 row affected (0.03 sec)
我們刪除了類別catid為1的數據即：娛樂新聞，那麼有關娛樂新聞的news中的數據double將會被刪除，新聞被刪除的同時，新聞下的評論也會被同時刪除。
如下所示：

mysql> select * from news;
+--------+-------+---------+---------------------+-------+
| newsId | title | content | createTime | catId |
+--------+-------+---------+---------------------+-------+
| 2 | test2 | test2 | 2015-05-19 15:17:03 | 2 |
+--------+-------+---------+---------------------+-------+
1 row in set (0.00 sec)

mysql> select * from comments;
+--------+---------+---------------------+--------+-----------+
| commId | content | createTime | newsId | userIP |
+--------+---------+---------------------+--------+-----------+
| 2 | you | 2015-05-19 15:17:03 | 2 | 127.0.0.1 |
+--------+---------+---------------------+--------+-----------+
1 row in set (0.00 sec)