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

MySQL 游標批量處理實戰：動態遍歷百表同步建索引/加欄位

編輯：MySQL 資料庫相關知識

前置知識鏈接

游標四大核心操作


-- 1. 聲明
DECLARE cursor_name CURSOR FOR SELECT 语句;

-- 2. 打開
OPEN cursor_name;

-- 3. 推進/取值
FETCH cursor_name INTO var1, var2...;

-- 4. 關閉
CLOSE cursor_name;

注意：SELECT 不能含 INTO 子句；需聲明 NOT FOUND 條件處理器避免越界報錯。

實戰場景：百張同結構表批量加索引

業務按月分表：student_202301 ~ student_202312，需為每張表同步建立 5 條索引。

完整存儲過程


DROP PROCEDURE IF EXISTS BatchAddIndex;
DELIMITER $$
CREATE PROCEDURE BatchAddIndex()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE tbl_name VARCHAR(64);
    
    -- 聲明游標：查詢前綴為 student_ 的表
    DECLARE cur CURSOR FOR 
        SELECT TABLE_NAME 
        FROM information_schema.TABLES 
        WHERE TABLE_SCHEMA = 'front' 
          AND TABLE_NAME LIKE 'student%';
    
    -- 邊界處理：游標耗盡設置 done=TRUE
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO tbl_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- 動態構建 5 條建索引 SQL
        SET @sql1 = CONCAT('CREATE INDEX idx_flag ON `', tbl_name, '` (flag)');
        SET @sql2 = CONCAT('CREATE INDEX idx_state ON `', tbl_name, '` (state)');
        SET @sql3 = CONCAT('CREATE INDEX idx_upload ON `', tbl_name, '` (upload)');
        SET @sql4 = CONCAT('CREATE INDEX idx_lockflag ON `', tbl_name, '` (lockFlag)');
        SET @sql5 = CONCAT('CREATE INDEX idx_comes ON `', tbl_name, '` (comes)');
        
        -- 預處理 + 執行 + 釋放（逐條執行，便於定位失敗）
        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;
        
    END LOOP;
    
    CLOSE cur;
END $$
DELIMITER ;

CALL BatchAddIndex();