MySQL 預存程序實現循環批次插入：WHILE 迴圈、游標與動態 SQL 進階應用

MySQL 預存程序實現循環批次插入

基礎範例：插入 100 筆測試資料

DELIMITER $$

CREATE PROCEDURE p3()
MODIFIES SQL DATA
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE max_val INT DEFAULT 100;
    
    WHILE i < max_val DO
        INSERT INTO user (id) VALUES (i);
        SET i = i + 1;
    END WHILE;
END $$

DELIMITER ;

關鍵語法要點

• DELIMITER $$：暫時變更結束符號，避免程序內 ; 衝突
• DECLARE：宣告區域變數 (僅在 BEGIN...END 內有效)
• MODIFIES SQL DATA：聲明修改資料特性 (MySQL 8.0+ 建議加上)
• WHILE ... DO ... END WHILE：迴圈結構

進階變體

1. 帶條件中斷的迴圈

CREATE PROCEDURE insert_with_break()
BEGIN
    DECLARE i INT DEFAULT 1;
    loop_label: WHILE i <= 100 DO
        INSERT INTO t1 VALUES (i, CONCAT('name_', i));
        IF i = 50 THEN
            LEAVE loop_label;  -- 跳出指定標籤迴圈
        END IF;
        SET i = i + 1;
    END WHILE;
END;

2. 使用 ITERATE 跳過當次迴圈

WHILE i <= 100 DO
    SET i = i + 1;
    IF i % 2 = 0 THEN
        ITERATE loop_label;  -- 跳過偶數
    END IF;
    INSERT INTO odd_numbers VALUES (i);
END WHILE;

3. 批次插入優化 (減少提交次數)

CREATE PROCEDURE batch_insert(IN batch_size INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    START TRANSACTION;
    WHILE i < batch_size DO
        INSERT INTO big_table (col1) VALUES (CONCAT('data_', i));
        SET i = i + 1;
        IF i % 1000 = 0 THEN
            COMMIT;
            START TRANSACTION;  -- 每 1000 筆提交一次
        END IF;
    END WHILE;
    COMMIT;
END;

替代方案對比

執行與管理

CALL p3();                    -- 執行預存程序
SHOW PROCEDURE STATUS;        -- 列出所有程序
DROP PROCEDURE IF EXISTS p3;  -- 刪除程序