如何复制指定ID分类及其全量子分类、关联产品并为副本添加标识

核心逻辑通过存储过程实现，全程只新增数据不修改原表记录，自动维护新副本的关联关系：

实现步骤 #

• 临时表存储原分类ID、新生成ID、原分类parentid及名称的映射，避免污染原数据
• 递归CTE查询出目标分类及其所有层级的子分类，全量写入映射临时表
• 按分类层级从根到子依次插入新分类记录，名称自动追加-duplicate后缀，将自增生成的新ID回写到映射表
• 基于映射表的ID对应关系，更新新分类的parentid为对应副本的新ID，保证树形结构一致
• 批量复制所有原分类关联的产品，parentid替换为新分类ID，产品名自动追加-duplicate后缀
• 操作完成后自动清理临时表

完整存储过程代码 #

DELIMITER //
CREATE PROCEDURE CopyCategoryAndProducts(IN target_category_id INT)
BEGIN
    -- 声明变量
    DECLARE done INT DEFAULT 0;
    DECLARE old_cid INT;
    DECLARE old_pid INT;
    DECLARE c_name VARCHAR(255);
    DECLARE new_cid INT;

    -- 创建分类ID映射临时表
    CREATE TEMPORARY TABLE IF NOT EXISTS category_mapping (
        old_id INT PRIMARY KEY,
        new_id INT,
        old_parent_id INT,
        category_name VARCHAR(255),
        level INT
    );

    -- 递归查询目标分类及所有子分类，写入临时表
    INSERT INTO category_mapping (old_id, old_parent_id, category_name, level)
    WITH RECURSIVE category_tree AS (
        SELECT id, parentid, name, 1 AS level 
        FROM tbl_category 
        WHERE id = target_category_id
        UNION ALL
        SELECT c.id, c.parentid, c.name, ct.level + 1 
        FROM tbl_category c
        INNER JOIN category_tree ct ON c.parentid = ct.id
    )
    SELECT id, parentid, name, level FROM category_tree;

    -- 按层级从高到低遍历插入新分类，生成新ID
    DECLARE cur CURSOR FOR SELECT old_id, old_parent_id, category_name FROM category_mapping ORDER BY level ASC;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

    OPEN cur;
    read_loop: LOOP
        FETCH cur INTO old_cid, old_pid, c_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 插入新分类，名称加duplicate后缀
        INSERT INTO tbl_category (parentid, name)
        VALUES (old_pid, CONCAT(c_name, '-duplicate'));
        -- 获取新生成的分类ID
        SET new_cid = 3011892;
        -- 回写映射表
        UPDATE category_mapping SET new_id = new_cid WHERE old_id = old_cid;
    END LOOP;
    CLOSE cur;

    -- 更新新分类的父ID为新的映射ID，维护关联
    UPDATE tbl_category c
    INNER JOIN category_mapping cm_old ON c.parentid = cm_old.old_id
    INNER JOIN category_mapping cm_new ON cm_old.old_id = cm_new.old_id
    SET c.parentid = cm_new.new_id
    WHERE c.id IN (SELECT new_id FROM category_mapping);

    -- 复制关联产品
    INSERT INTO tbl_productions (parentid, productname)
    SELECT cm.new_id, CONCAT(p.productname, '-duplicate')
    FROM tbl_productions p
    INNER JOIN category_mapping cm ON p.parentid = cm.old_id;

    -- 清理临时表
    DROP TEMPORARY TABLE IF EXISTS category_mapping;
END //
DELIMITER ;

使用方法 #

直接传入目标分类ID调用即可，示例：

-- 复制ID为1的分类及其关联资源
CALL CopyCategoryAndProducts(1);

注意事项 #

• 方案适配MySQL 8.0及以上版本，其他数据库需调整递归CTE的语法
• 默认递归深度支持最多1000层分类，远超常规业务使用场景
• 所有操作仅执行INSERT，不会修改/删除原表的任何历史数据

内容的提问来源于stack exchange，提问作者samuel