如何在 Mysql 的同一张表中完成级联更新

情形：同一张表，“非主键列”定义了外键，引用该表中主键。

问题：
如果修改主键值，而该主键值恰好被其他行外键引用，此时无法完成级联更新，mysql 会报错。

又什么办法可以让主键修改时，作为外键引用该主键的字段级联更新吗？

我知道在不同表中，级联更新会工作顺利，同一张表中是否不可行？如果不可行，底层逻辑为什么？

级联

主键值

外键

主键

7 条回复 2023-04-20 11:59:42 +08:00

liaojl

2023-03-12 07:00:50 +08:00 via iPhone

对 MySQL 不熟，帮你问了 chatgpt ，下面是它的回答。

你的问题是关于 mysql 数据库中的主键和外键的级联更新。根据我搜索到的信息，有两种方法可以实现这个功能：
一种是在定义外键的时候，在最后加入 ON UPDATE CASCADE 的关键字，表示当主表更新时，子表也会产生连锁更新动作 12 。例如：
ALTER TABLE pc ADD CONSTRAINT fk_cpu_model FOREIGN KEY (cpumodel) REFERENCES parts (model) ON UPDATE CASCADE;
另一种是使用触发器来实现级联更新，即在主表上定义一个触发器，当主表更新时，触发器会自动执行相应的 SQL 语句来更新子表 3 。例如：
CREATE TRIGGER update_pc AFTER UPDATE ON parts FOR EACH ROW
BEGIN
UPDATE pc SET cpumodel = NEW.model WHERE cpumodel = OLD.model;
END;
这两种方法都可以达到你想要的效果，但是有一些区别和优缺点。你可以根据自己的需求和喜好来选择合适的方法。
如果你想了解更多关于 mysql 主键和外键的级联更新，你可以输入以下查询：
mysql 主键外键级联操作详解
mysql 触发器和外键哪个更好
mysql 级联更新性能优化
如果你想学习其他方面的 mysql 知识，你可以输入以下查询：
mysql 基本语法教程
mysql 数据库设计原则
mysql 存储过程和函数
希望这些信息对你有所帮助，祝你学习进步。

investor

2023-03-12 10:36:12 +08:00 via iPhone

@liaojl 哈哈，已经收到，不过呢，chartGPT 答非所问，他回答的情况适合主从在两张表。

jdOY

2023-03-12 12:38:17 +08:00

还有用 mysql 外键的，推翻重构吧

allegory

&nbp;2023-03-12 20:15:24 +08:00

办法肯定是有的，以多级菜单表的修改为例，你可以借助存储过程递归地从最下面的叶子结点依次向上逐层修改。如果引用层级不多的话（比如只有 2 层），则可以先改引用者中的外键字段，再改被引用项的主键字段，思想也是递归。

allegory

2023-03-12 20:32:25 +08:00

```SQL

DELIMITER $$
CREATE PROCEDURE update_menu(
IN old_menu_id INT,
IN new_menu_id INT
)
BEGIN
-- 递归查询当前菜单的所有子菜单
DECLARE done INT DEFAULT FALSE;
DECLARE cur CURSOR FOR
SELECT id, parent_id
FROM Menu
WHERE parent_id = old_menu_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET dOne= TRUE;

OPEN cur;
read_loop: LOOP
FETCH cur INTO child_menu_id, child_parent_id;
IF done THEN
LEAVE read_loop;
END IF;

-- 递归调用存储过程更新子菜单的引用值
CALL update_menu(child_menu_id, new_menu_id);
END LOOP;
CLOSE cur;

-- 更新当前菜单的主键和所有引用该菜单的子菜单的父菜单 ID
UPDATE Menu SET
id = new_menu_id,
parent_id = IF(parent_id = old_menu_id, new_menu_id, parent_id)
WHERE id = old_menu_id;
END$$
DELIMITER ;

```

investor

2023-03-16 19:59:10 +08:00 via iPhone

@allegory 收到，感谢回复，看来当前 mysql 是不支持在同一张表实现级联更新的。

wxyrrcj

2023-04-20 11:59:42 +08:00

-- 菜单 SQL
insert into sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
values('aaa', '2019', '1', 'a', 'a', 1, 0, 'C', '0', '0', 'env:a:list', '#', 'admin', sysdate(), '', null, 'aaa 菜单');

-- 按钮父菜单 ID
SELECT @parentId := LAST_INSERT_ID();

-- 按钮 SQL
insert into sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
values('aaa 查询', @parentId, '1', '#', '', 1, 0, 'F', '0', '0', 'env:a:query', '#', 'admin', sysdate(), '', null, '');

insert into sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
values('aaa 新增', @parentId, '2', '#', '', 1, 0, 'F', '0', '0', 'env:a:add', '#', 'admin', sysdate(), '', null, '');

insert into sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
values('aaa 修改', @parentId, '3', '#', '', 1, 0, 'F', '0', '0', 'env:a:edit', '#', 'admin', sysdate(), '', null, '');

insert into sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
values('aaa 删除', @parentId, '4', '#', '', 1, 0, 'F', '0', '0', 'env:a:remove', '#', 'admin', sysdate(), '', null, '');

insert into sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, update_by, update_time, remark)
values('aaa 导出', @parentId, '5', '#', '', 1, 0, 'F', '0', '0', 'env:a:export', '#', 'admin', sysdate(), '', null, '');