V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
investor
V2EX  ›  MySQL

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

  •  
  •   investor · 2023-03-12 04:38:07 +08:00 via iPhone · 1814 次点击
    这是一个创建于 607 天前的主题,其中的信息可能已经有所发展或是发生改变。
    情形:同一张表,“非主键列”定义了外键,引用该表中主键。

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

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

    我知道在不同表中,级联更新会工作顺利,同一张表中是否不可行?如果不可行,底层逻辑为什么?
    7 条回复    2023-04-20 11:59:42 +08:00
    liaojl
        1
    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
        2
    investor  
    OP
       2023-03-12 10:36:12 +08:00 via iPhone
    @liaojl 哈哈,已经收到,不过呢,chartGPT 答非所问,他回答的情况适合主从在两张表。
    jdOY
        3
    jdOY  
       2023-03-12 12:38:17 +08:00
    还有用 mysql 外键的,推翻重构吧
    allegory
        4
    allegory  
       2023-03-12 20:15:24 +08:00
    办法肯定是有的,以多级菜单表的修改为例,你可以借助存储过程递归地从最下面的叶子结点依次向上逐层修改。如果引用层级不多的话(比如只有 2 层),则可以先改引用者中的外键字段,再改被引用项的主键字段,思想也是递归。
    allegory
        5
    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
        6
    investor  
    OP
       2023-03-16 19:59:10 +08:00 via iPhone
    @allegory 收到,感谢回复,看来当前 mysql 是不支持在同一张表实现级联更新的。
    wxyrrcj
        7
    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, '');
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2726 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 25ms · UTC 12:14 · PVG 20:14 · LAX 04:14 · JFK 07:14
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.