如何在 MySQL 中更新级联？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16779552/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to update on cascade in MySQL?
提问by Radek Wyroslak
Let's look at this example database:
让我们看看这个示例数据库：
As we can see, person depends on the city (person.city_id is a foreign key). I don't delete rows, I just set them inactive (active=0). After setting city inactive, how can I automatically set all persons who are dependent on this city inactive? Is there a better way than writing triggers?
正如我们所见，person 依赖于城市（person.city_id 是一个外键）。我不删除行，我只是将它们设置为非活动状态（活动 = 0）。设置城市不活动后，如何自动设置所有依赖该城市的人不活动？有没有比编写触发器更好的方法？
EDIT: I am interested only in setting person's rows inactive, not setting them active.
编辑：我只对将人的行设置为非活动状态感兴趣，而不是将它们设置为活动状态。
回答by Bill Karwin
Here's a solution that uses cascading foreign keys to do what you describe:
这是一个使用级联外键来执行您描述的操作的解决方案：
mysql> create table city (
id int not null auto_increment,
name varchar(45),
active tinyint,
primary key (id),
unique key (id, active));
mysql> create table person (
id int not null auto_increment,
city_id int,
active tinyint,
primary key (id),
foreign key (city_id, active) references city (id, active) on update cascade);
mysql> insert into city (name, active) values ('New York', 1);
mysql> insert into person (city_id, active) values (1, 1);
mysql> select * from person;
+----+---------+--------+
| id | city_id | active |
+----+---------+--------+
| 1 | 1 | 1 |
+----+---------+--------+
mysql> update city set active = 0 where id = 1;
mysql> select * from person;
+----+---------+--------+
| id | city_id | active |
+----+---------+--------+
| 1 | 1 | 0 |
+----+---------+--------+
Tested on MySQL 5.5.31.
在 MySQL 5.5.31 上测试。
回答by Atticus
Maybe you should reconsider how you define a person to be active.. Instead of having active defined twice, you should just keep it in the city table and have your SELECT statements return Person WHERE city.active = 1..
也许你应该重新考虑你如何定义一个人是活跃的..而不是将 active 定义两次，你应该把它保存在 city 表中，让你的 SELECT 语句返回 Person WHERE city.active = 1..
But if you must.. you could do something like:
但如果你必须......你可以这样做：
UPDATE city C
LEFT JOIN person P ON C.id = P.city
SET C.active = 0 AND P.active = 0
WHERE C.id = @id
