MySQL 存儲過程實戰：雙層游標嵌套拆分大表為帖子表與回覆表

MySQL 存儲過程實戰：雙層游標嵌套拆分大表

面對千萬級單表需按業務鍵拆分為主表（帖子）與子表（回覆），利用雙層游標在存儲過程中分批處理，避免單次事務過大、鎖衝突與內存溢出。本文解析核心邏輯與優化要點。

一、業務場景與表結構

• 源表 bbs_fromask_importask_tmp：臨時導入表，含 pkid（帖子分組鍵）、qtitle、qdesc、answer（多行回覆）等字段。
• 目標表：
  • pre_data_thread_tmp(id, title, age, rule, param) —— 帖子主表
  • pre_data_post_tmp(tid, text, sort) —— 回覆子表，sort=1 為樓主內容，>=2 為回覆

二、核心存儲過程邏輯

DELIMITER $$
CREATE PROCEDURE `bbs_split_thread_post`()
BEGIN
    DECLARE v_source_count INT DEFAULT 0;
    DECLARE v_thread_pkid INT;
    DECLARE v_thread_pkid_uni_count INT;
    DECLARE done1 INT DEFAULT 0;
    DECLARE done2 INT DEFAULT 0;

    -- 外層游標：按 pkid 分組，獲取每組行數
    DECLARE v_cur_thread_pkid CURSOR FOR
        SELECT pkid, COUNT(*) FROM bbs_fromask_importask_tmp GROUP BY pkid;

    -- 外層 NOT FOUND 處理器
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = 1;

    -- 清空目標臨時表
    TRUNCATE TABLE pre_data_thread_tmp;
    TRUNCATE TABLE pre_data_post_tmp;

    -- 源表無數據直接返回
    SELECT COUNT(*) INTO v_source_count FROM bbs_fromask_importask_tmp;
    IF v_source_count = 0 THEN
        LEAVE;
    END IF;

    -- 獲取目標主表當前最大 ID 作為起始值
    SELECT IFNULL(MAX(id), 0) INTO @thread_max_id FROM yaolanbbs.pre_data_thread;

    OPEN v_cur_thread_pkid;
    REPEAT
        FETCH v_cur_thread_pkid INTO v_thread_pkid, v_thread_pkid_uni_count;
        IF NOT done1 THEN
            SET @thread_max_id = @thread_max_id + 1;

            -- 1. 寫入帖子主表（取每組第一條作為標題）
            INSERT INTO pre_data_thread_tmp (id, title, age, rule, param)
            SELECT @thread_max_id, t1.qtitle, t1.age, 'age', t1.age
            FROM bbs_fromask_importask_tmp t1
            WHERE t1.pkid = v_thread_pkid
            LIMIT 1;

            -- 2. 內層游標：遍歷該 pkid 下所有 answer 寫入回覆表
            BEGIN
                DECLARE v_post_answer TEXT;
                DECLARE v_cur_post_record CURSOR FOR
                    SELECT t1.answer FROM bbs_fromask_importask_tmp t1 WHERE t1.pkid = v_thread_pkid;
                DECLARE EXIT HANDLER FOR NOT FOUND BEGIN END;  -- 內層結束不影響外層

                -- 首條回覆（樓主內容 qdesc）
                INSERT INTO pre_data_post_tmp (tid, text, sort)
                SELECT @thread_max_id, t1.qdesc, 1
                FROM bbs_fromask_importask_tmp t1
                WHERE t1.pkid = v_thread_pkid
                LIMIT 1;

                SET @sort = 2;
                OPEN v_cur_post_record;
                REPEAT
                    FETCH v_cur_post_record INTO v_post_answer;
                    IF NOT done2 THEN
                        INSERT INTO pre_data_post_tmp (tid, text, sort) VALUES (@thread_max_id, v_post_answer, @sort);
                        SET @sort = @sort + 1;
                    END IF;
                UNTIL done2 END REPEAT;
                CLOSE v_cur_post_record;
            END;
        END IF;
    UNTIL done1 END REPEAT;
    CLOSE v_cur_thread_pkid;
END$$
DELIMITER ;

三、關鍵技術點解析

1. 雙層 NOT FOUND 處理器隔離：外層用 CONTINUE HANDLER 設置 done1=1；內層用 EXIT HANDLER 直接結束 BEGIN...END 塊，避免內層結束觸發外層退出。
2. 用戶變量 @thread_max_id、@sort 跨游標傳遞：會話級變量，無需聲明，靈活計算自增鍵。
3. 分批提交：示例中未顯式提交，生產環境建議每處理 N 個 pkid 執行 COMMIT，避免超大事務鎖住目標表、Undo Log 膨脹。
4. 臨時表中轉：先寫 _tmp 表，驗證無誤後再 INSERT ... SELECT 進正式表，降低風險。

四、性能優化建議

五、改進版：純 SQL 批量寫入（推薦生產）

-- 1. 帖子主表：按 pkid 分組取第一行
INSERT INTO pre_data_thread (id, title, age, rule, param, create_time)
SELECT
    @rownum := @rownum + 1 AS id,
    t.qtitle, t.age, 'age', t.age, NOW()
FROM (
    SELECT pkid, qtitle, age
    FROM bbs_fromask_importask_tmp
    GROUP BY pkid
) t
CROSS JOIN (SELECT @rownum := (SELECT IFNULL(MAX(id),0) FROM pre_data_thread)) r;

-- 2. 回覆表：窗口函數生成 sort (MySQL 8.0+)
INSERT INTO pre_data_post (tid, text, sort, create_time)
SELECT
    thread_map.id,
    src.text,
    ROW_NUMBER() OVER (PARTITION BY src.pkid ORDER BY src.id) AS sort,
    NOW()
FROM bbs_fromask_importask_tmp src
JOIN (
    SELECT pkid, id FROM pre_data_thread  -- 假設 pkid 已冗余或通過映射表關聯
) thread_map ON src.pkid = thread_map.pkid;

優勢：無游標開銷、向量化執行、可並行、代碼簡潔。MySQL 5.7 可用變量模擬 ROW_NUMBER()。

1. 上一篇文章： MySQL 跨平台遷移實戰：Windows 到 Linux 數據零停機遷移指南
2. 下一篇文章： MySQL 運維日誌：MHA 故障切換、Event 復制陷阱與深分頁優化實戰

MySQL 綜合教學

MySQL Workbench 5.2 入門：服務器實例、建庫建表、數據操作全流程

核心概念：服務器實例 一個 MySQL 服務進程 = 一個實

MySQL 新增外鍵報錯 1215：Cannot add foreign key constraint 根因與修復實戰

MySQL ERROR 1215 外鍵創建失敗：資料型別不一

修改 MySQL root 密碼後 phpMyAdmin 無法登入？配置檔同步更新指南

WAMP 環境下 MySQL 密碼修改後 phpMyAdmi

Windows 下 MySQL 服務啟動與關閉命令完全指南

前言 在 Windows 環境部署 MySQL 時，透過服務