您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 預存程序實現批次插入測試資料：WHILE 迴圈與動態 SQL 完整範例

編輯：MySQL 資料庫相關知識

預存程序批次插入核心語法

MySQL 預存程序配合流程控制語句，能高效生成大量測試資料，避免應用層頻繁往返網路開銷。以下為標準範本，可直接調整表結構、欄位值與迴圈次數套用至實際專案。

完整可執行範例

DROP PROCEDURE IF EXISTS test_insert;

DELIMITER ;;

CREATE PROCEDURE test_insert()
BEGIN
    DECLARE i INT DEFAULT 0;

    WHILE i < 10 DO
        INSERT INTO CMS.CMS_Module (
            ModuleType,
            ModuleName,
            ModuleDescription,
            DesignHtml,
            QUERY
        ) VALUES (
            '2',
            CONCAT('活動', i),
            '測試加入',
            'aaaaaaaaaa',
            ''
        );
        SET i = i + 1;
    END WHILE;
END;;

DELIMITER ;

CALL test_insert();

關鍵語法解析

語法元素	說明
`DELIMITER ;;`	暫時變更語句分隔符，避免程序體內的 `;` 被誤判為結束
`DECLARE i INT DEFAULT 0`	宣告區域變數並初始化
`WHILE ... DO ... END WHILE`	迴圈結構，支援複雜條件判斷
`CONCAT('活動', i)`	字串串接，生成動態模組名稱
`SET i = i + 1`	迴圈計數器遞增
`DELIMITER ;`	恢復預設分隔符
`CALL test_insert()`	呼叫預存程序執行

進階擴展場景

大量資料插入：將迴圈上限調整為 10 萬、100 萬，配合 COMMIT 批次提交（預設自動提交，大量時建議關閉 autocommit 手動控制）
多表關聯測試資料：在迴圈中插入主表後，使用 LAST_INSERT_ID() 取得自增 ID 寫入從表
隨機化資料：結合 RAND()、FLOOR()、ELT() 生成多樣化測試值
參數化預存程序：加入 IN p_count INT 參數，呼叫時動態指定筆數：CALL test_insert(10000)

效能與注意事項

單筆 INSERT 成本：預存程序雖省去網路往返，但每次 INSERT 仍觸發索引維護、日誌寫入。百萬級以上建議改用 LOAD DATA INFILE 或批次 INSERT ... VALUES (...), (...), ... 語法
權限需求：建立預存程序需 CREATE ROUTINE 權限，執行需 EXECUTE 權限
除錯技巧：在程序中加入 SELECT i; 或寫入日誌表追蹤執行進度
版本相容：MySQL 5.0+ 完整支援，MariaDB、Percona Server 同樣適用

替代方案快速對比

方案	適用場景	優點	缺點
預存程序 WHILE 迴圈	邏輯複雜、需流程控制、中小量資料	邏輯封裝在 DB、可重複呼叫、支援條件判斷	單筆 INSERT 效能較低、難以並行化
批次 INSERT 多值語法	大量同結構資料、單次執行	單次 SQL 寫入成千上萬行、效能極佳	SQL 過長可能超過 `max_allowed_packet`、邏輯較死板
LOAD DATA INFILE	超大量資料匯入（百萬級+）	速度最快、繞過 SQL 層解析	需檔案系統存取權限、格式要求嚴格
應用層批次寫入	跨系統、需事務控制、ORM 框架	彈性最高、易除錯、支援分散式事務	網路往返開銷大、受應用語言效能限制