您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 游標批量 DDL 實戰：動態構建 SQL 實現百表同步加索引

編輯：MySQL 綜合教學

MySQL 游標批量 DDL 實戰：動態構建 SQL 實現百表同步加索引

面對數百張同結構分表（如 student_001~student_300）需同步添加多個索引，手工執行極易出錯且效率低。本文展示「游標 + 預處理 + 動態 SQL」組合拳，實現批量 DDL 安全、可控、可重試執行。

核心技術棧

游標： 遍歷 information_schema.TABLES 篩選目標表名
預處理： 解決「變量作表名」無法直接執行的限制
動態拼接： CONCAT 構建帶反引號的合法 DDL
條件處理： CONTINUE HANDLER 實現單表失敗不中斷全局流程

完整存儲過程範本


DROP PROCEDURE IF EXISTS batch_add_indexes;
DELIMITER $$
CREATE PROCEDURE batch_add_indexes()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE v_tbl VARCHAR(64);
  DECLARE v_sql TEXT;
  
  -- 1. 定義游標：篩選前綴為 student_ 的表
  DECLARE cur CURSOR FOR 
    SELECT TABLE_NAME 
    FROM information_schema.TABLES 
    WHERE TABLE_SCHEMA = 'school_db' 
      AND TABLE_NAME LIKE 'student_%';
  
  -- 2. 綁定 NOT FOUND 條件：游標結束置 done=1
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  
  OPEN cur;
  
  read_loop: LOOP
    FETCH cur INTO v_tbl;
    IF done = 1 THEN
      LEAVE read_loop;
    END IF;
    
    -- 3. 動態構建 5 條 CREATE INDEX 語句
    SET @sql1 = CONCAT('CREATE INDEX idx_flag ON `', v_tbl, '` (`flag`)');
    SET @sql2 = CONCAT('CREATE INDEX idx_state ON `', v_tbl, '` (`state`)');
    SET @sql3 = CONCAT('CREATE INDEX idx_upload ON `', v_tbl, '` (`upload`)');
    SET @sql4 = CONCAT('CREATE INDEX idx_lockflag ON `', v_tbl, '` (`lockFlag`)');
    SET @sql5 = CONCAT('CREATE INDEX idx_comes ON `', v_tbl, '` (`comes`)');
    
    -- 4. 預處理 + 執行 + 釋放（逐條獨立，便於定位失敗）
    PREPARE s1 FROM @sql1; EXECUTE s1; DEALLOCATE PREPARE s1;
    PREPARE s2 FROM @sql2; EXECUTE s2; DEALLOCATE PREPARE s2;
    PREPARE s3 FROM @sql3; EXECUTE s3; DEALLOCATE PREPARE s3;
    PREPARE s4 FROM @sql4; EXECUTE s4; DEALLOCATE PREPARE s4;
    PREPARE s5 FROM @sql5; EXECUTE s5; DEALLOCATE PREPARE s5;
    
    -- 可選：記錄執行日誌
    -- INSERT INTO ddl_log (tbl, sql_text, exec_time) VALUES (v_tbl, CONCAT(@sql1,';',@sql2), NOW());
    
  END LOOP;
  
  CLOSE cur;
END$$
DELIMITER ;

-- 執行
CALL batch_add_indexes();

關鍵細節避坑指南

反引號包裹識別符： CONCAT('... `', v_tbl, '` ...') 防止表名含保留字或特殊字符報錯。
預處理單條執行： 不要 CONCAT(@sql1, @sql2...) 合併為一條多語句，MySQL 預處理不支持多語句，且合併後失敗難以定位具體索引。
條件處理器選型：
- CONTINUE HANDLER FOR SQLEXCEPTION：單表單索引失敗記錄日誌並繼續下一張（推薦）
- EXIT HANDLER：遇錯即停，適合強一致性要求場景
權限最小化： 執行帳號僅需目標庫的 INDEX、SELECT（查元數據）權限。
大表並發控制： 若單表 > 1000 萬行，建議分批次執行（如按表名哈希分 10 組並行），避免長鎖表阻塞業務。

進階擴展：幂等性與重試


-- 檢查索引是否已存在，避免重複報錯
SET @chk = CONCAT('SELECT COUNT(*) INTO @exist FROM information_schema.STATISTICS 
  WHERE TABLE_SCHEMA=''school_db'' AND TABLE_NAME=''', v_tbl, ''' AND INDEX_NAME=''idx_flag''');
PREPARE p_chk FROM @chk; EXECUTE p_chk; DEALLOCATE PREPARE p_chk;

IF @exist = 0 THEN
  -- 執行 CREATE INDEX
END IF;