MySQL 存儲過程實現循環批量插入數據：WHILE/REPEAT/LOOP 三種寫法詳解

MySQL 存儲過程實現循環批量插入數據：WHILE/REPEAT/LOOP 三種寫法詳解

背景痛點

Oracle 支持匿名塊 BEGIN ... END; 直接循環插入，MySQL **不支持匿名塊**，必須通過**存儲過程**實現批量寫入。

核心機制：DELIMITER 重定義

DELIMITER //  -- 將語句結束符從 ; 改為 //
-- 存儲過程體內部可安全使用 ;
CREATE PROCEDURE proc_name() BEGIN ... END //
DELIMITER ;   -- 恢復默認結束符

若不修改 DELIMITER，存儲過程體內的 ; 會被誤判為過程創建結束，導致語法錯誤。

完整示例：插入 999 條測試數據

DELIMITER //
CREATE PROCEDURE batch_insert_demo()
BEGIN
    DECLARE num INT DEFAULT 1;
    WHILE num < 1000 DO
        INSERT INTO per2(name) VALUES(CONCAT('fan', num));
        SET num = num + 1;
    END WHILE;
END //
DELIMITER ;

-- 調用執行
CALL batch_insert_demo();

關鍵點：

• CONCAT('fan', num) 替代 Oracle 的 'fan' || num
• DECLARE 變量必須放在 BEGIN 後首行
• 調用時若 DELIMITER 未恢復，需輸入 // 而非 ;

查看與管理存儲過程

-- 查看當前庫所有過程
SELECT name FROM mysql.proc WHERE db='fandb' AND type='procedure';

-- 查看創建代碼
SHOW CREATE PROCEDURE per2\G

三種循環結構對比

1. WHILE 循環 —— 先判斷後執行

DELIMITER //
CREATE PROCEDURE proc_while()
BEGIN
    DECLARE v INT DEFAULT 0;
    WHILE v < 6 DO
        INSERT INTO t VALUES(v);
        SET v = v + 1;
    END WHILE;
END //
DELIMITER ;

適用於：循環次數明確、需預先判斷條件的場景。

2. REPEAT 循環 —— 先執行後判斷 (至少執行一次)

DELIMITER //
CREATE PROCEDURE proc_repeat()
BEGIN
    DECLARE v INT DEFAULT 0;
    REPEAT
        INSERT INTO t VALUES(v);
        SET v = v + 1;
    UNTIL v >= 5
    END REPEAT;
END //
DELIMITER ;

適用於：至少執行一次、條件在循環體內產生的場景。

3. LOOP 循環 —— 無預設條件，配合 LEAVE 跳出

DELIMITER //
CREATE PROCEDURE proc_loop()
BEGIN
    DECLARE v INT DEFAULT 0;
    loop_label: LOOP
        INSERT INTO t VALUES(v);
        SET v = v + 1;
        IF v >= 5 THEN
            LEAVE loop_label;
        END IF;
    END LOOP;
END //
DELIMITER ;

適用於：複雜跳出邏輯、多重退出條件的場景。

標號 (LABEL) 進階用法

• 可用於 BEGIN、WHILE、REPEAT、LOOP 前
• LEAVE label 跳出指定標號塊
• ITERATE label 直接進入下一次迭代 (類似 continue)

最佳實踐建議

1. 批量提交：大量插入考慮每 1000-5000 行 COMMIT 一次，避免超大事務
2. 參數化：將循環次數、表名作為過程參數提升復用性
3. 異常處理：添加 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION 捕獲錯誤
4. 權限控制：僅授予應用賬號 EXECUTE 權限，禁止直接 DML