MySQL 資料遷移儲存程序實戰：分表寫入範例

場景說明

將單表 ol_answerresult_56 資料按 UserId % 100 遷移至 100 張分表 new_answerresult_00 ~ new_answerresult_99，採用游標逐行處理、動態 SQL 執行。

完整儲存程序碼

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_move_data`$$
CREATE PROCEDURE `sp_move_data`()
BEGIN
    DECLARE v_exit INT DEFAULT 0;
    DECLARE v_UniqueKey VARCHAR(57);
    DECLARE v_TagCatalogId INT;
    DECLARE v_RootCatalogId INT;
    DECLARE v_UserId BIGINT;
    DECLARE v_QuestionId CHAR(36);
    DECLARE v_CorrectCount INT;
    DECLARE v_ErrorCount INT;
    DECLARE v_LastIsCorrect INT;
    DECLARE v_LastAnswerXML TEXT CHARSET utf8;
    DECLARE v_TotalCostTime INT;
    DECLARE v_Reviews VARCHAR(200) CHARSET utf8;
    DECLARE v_AnswerResultCategory INT;
    DECLARE v_LastCostTime INT;
    DECLARE v_LastAnswerTime DATETIME;
    DECLARE v_IsPublic INT;
    DECLARE v_SUBJECT INT;
    DECLARE v_TotalCount INT;
    DECLARE v_AnswerMode SMALLINT(6);
    DECLARE v_ExerciseWeight FLOAT;
    DECLARE c_table INT;
    DECLARE i INT DEFAULT 0;

    -- 定義游標
    DECLARE c_ids CURSOR FOR
        SELECT UniqueKey, TagCatalogId, RootCatalogId, UserId, QuestionId,
               CorrectCount, ErrorCount, LastIsCorrect, LastAnswerXML,
               TotalCostTime, Reviews, AnswerResultCategory, LastCostTime,
               LastAnswerTime, IsPublic, SUBJECT, TotalCount, AnswerMode, ExerciseWeight
        FROM ol_answerresult_56;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_exit = 1;

    OPEN c_ids;

    REPEAT
        FETCH c_ids INTO v_UniqueKey, v_TagCatalogId, v_RootCatalogId, v_UserId, v_QuestionId,
                       v_CorrectCount, v_ErrorCount, v_LastIsCorrect, v_LastAnswerXML,
                       v_TotalCostTime, v_Reviews, v_AnswerResultCategory, v_LastCostTime,
                       v_LastAnswerTime, v_IsPublic, v_SUBJECT, v_TotalCount, v_AnswerMode, v_ExerciseWeight;

        IF v_exit = 0 THEN
            -- 計算目標分表編號
            SELECT MOD(v_UserId, 100) INTO c_table;

            -- 動態建構 INSERT 語句
            SET @SQL_CONTEXT = CONCAT(
                'INSERT INTO new_answerresult_', c_table, ' ',
                '(UniqueKey, TagCatalogId, RootCatalogId, UserId, QuestionId,',
                ' CorrectCount, ErrorCount, LastIsCorrect, LastAnswerXML,',
                ' TotalCostTime, Reviews, AnswerResultCategory, LastCostTime,',
                ' LastAnswerTime, IsPublic, SUBJECT, TotalCount, AnswerMode, ExerciseWeight)',
                ' VALUES (',
                QUOTE(v_UniqueKey), ',',
                v_TagCatalogId, ',',
                v_RootCatalogId, ',',
                v_UserId, ',',
                QUOTE(v_QuestionId), ',',
                v_CorrectCount, ',',
                v_ErrorCount, ',',
                v_LastIsCorrect, ',',
                QUOTE(v_LastAnswerXML), ',',
                v_TotalCostTime, ',',
                QUOTE(IFNULL(REPLACE(v_Reviews, '''', ''), '')), ',',
                v_AnswerResultCategory, ',',
                v_LastCostTime, ',',
                QUOTE(v_LastAnswerTime), ',',
                v_IsPublic, ',',
                v_SUBJECT, ',',
                v_TotalCount, ',',
                v_AnswerMode, ',',
                v_ExerciseWeight, ')'
            );

            PREPARE STMT FROM @SQL_CONTEXT;
            EXECUTE STMT;
            DEALLOCATE PREPARE STMT;
        END IF;

        SET i = i + 1;
        -- 可選：每 100 行提交一次
        -- IF MOD(i, 100) = 0 THEN COMMIT; END IF;

    UNTIL v_exit = 1
    END REPEAT;

    CLOSE c_ids;
    -- COMMIT;
END$$

DELIMITER ;

關鍵技術點解析

1. 游標 + Handler：標準逐行處理模式，NOT FOUND 觸發結束旗標。
2. 分表路由：MOD(v_UserId, 100) 確保同一用戶資料落在同一分表。
3. 動態 SQL (PREPARE/EXECUTE)：表名無法用參數綁定，須拼接字串執行。
4. QUOTE() 防注入：字串欄位自動轉義單引號，避免 SQL 注入與語法錯誤。
5. 批次提交 (註解部分)：大量資料建議每 N 行 COMMIT，避免長事務鎖表、Undo Log 膨脹。

優化建議

• 改用 INSERT ... SELECT 配合 WHERE MOD(UserId,100)=N 分批執行，效率遠高於游標。
• 若資料量極大 (億級)，考慮 pt-archiver、mysqldump --where + 並行匯入。
• 遷移前關閉目標表索引 (ALTER TABLE ... DISABLE KEYS)，完成後重建。
• 監控 Innodb_buffer_pool_wait_free、Innodb_log_waits 避免寫入阻塞。