MySQL 遊標實戰：批量 DDL 操作（添加索引、字段）完整指南

MySQL 遊標實戰：批量 DDL 操作完整指南

當數據庫中存在大量結構相同但名稱不同的表（如分表 student_001、student_002...）時，逐個執行 DDL 極其低效。結合存儲過程、遊標與預處理語句，可實現批量自動化操作。

前置知識鏈接

• MySQL 變量與條件處理
• PREPARE 預處理語句詳解
• MySQL 存儲過程與函數

遊標核心語法

注意：需聲明 DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cursor_name; 捕獲遍歷結束異常，避免存儲過程報錯中斷。

實戰案例：批量為分表添加多個索引

場景：front 庫下所有 student% 表需添加 5 個索引。

DROP PROCEDURE IF EXISTS FountTable;
DELIMITER $$
CREATE PROCEDURE FountTable()
BEGIN
    DECLARE TableName VARCHAR(64);
    -- 1. 聲明遊標：查詢目標表名
    DECLARE cur_FountTable CURSOR FOR 
        SELECT TABLE_NAME FROM information_schema.TABLES 
        WHERE TABLE_SCHEMA='front' AND TABLE_NAME LIKE 'student%';
    -- 2. 異常處理：遍歷結束關閉遊標
    DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_FountTable;

    OPEN cur_FountTable;
    REPEAT
        FETCH cur_FountTable INTO TableName;
        -- 3. 動態構建 SQL（預處理必須）
        SET @SQLSTR1 = CONCAT('CREATE INDEX Flag ON `', TableName, '` (Flag);');
        SET @SQLSTR2 = CONCAT('CREATE INDEX State ON `', TableName, '` (State);');
        SET @SQLSTR3 = CONCAT('CREATE INDEX upload ON `', TableName, '` (upload);');
        SET @SQLSTR4 = CONCAT('CREATE INDEX ccFlag ON `', TableName, '` (lockFlag);');
        SET @SQLSTR5 = CONCAT('CREATE INDEX comes ON `', TableName, '` (comes);');

        -- 4. 預處理、執行、釋放
        PREPARE STMT1 FROM @SQLSTR1; EXECUTE STMT1; DEALLOCATE PREPARE STMT1;
        PREPARE STMT2 FROM @SQLSTR2; EXECUTE STMT2; DEALLOCATE PREPARE STMT2;
        PREPARE STMT3 FROM @SQLSTR3; EXECUTE STMT3; DEALLOCATE PREPARE STMT3;
        PREPARE STMT4 FROM @SQLSTR4; EXECUTE STMT4; DEALLOCATE PREPARE STMT4;
        PREPARE STMT5 FROM @SQLSTR5; EXECUTE STMT5; DEALLOCATE PREPARE STMT5;
    UNTIL 0 END REPEAT;
    CLOSE cur_FountTable;
END $$
DELIMITER ;
CALL FountTable();

關鍵細節解析

1. 動態 SQL 必須用 PREPARE：存儲過程中變量不能直接作為表名/列名，CONCAT 拼接後需通過 PREPARE 預編譯執行。
2. 遊標查詢條件靈活控制：修改 information_schema.TABLES 的 WHERE 子句即可精確匹配目標表（庫、前綴、正則等）。
3. 異常處理策略：示例用 EXIT HANDLER 遇錯中斷；生產環境可改 CONTINUE HANDLER 記錄錯誤日誌後繼續下一張表，避免單表失敗導致全量終止。
4. 預處理資源釋放：每個 PREPARE 必須配對 DEALLOCATE PREPARE，防止會話級預處理語句洩漏佔用內存。

風險警示與最佳實踐

批量 DDL 雙刃劍：雖大幅提升運維效率，但潛在風險極大——誤操作可能導致全庫鎖表、複製延遲、甚至數據損壞。生產環境執行前務必：

• 在測試環境全量驗證腳本邏輯與性能影響。
• 確認業務低峰期執行，評估鎖持有時間（考慮 ALGORITHM=INPLACE、LOCK=NONE 等 Online DDL 選項）。
• 備份核心數據，制定回滾預案。
• 嚴格限制執行賬號權限，避免誤刪誤改。

作者：pursuer.chen | 博客：https://www.cnblogs.com/chenmh