MySQL 資料表遍歷完整指南：游標、WHILE 迴圈與集合思維對比

為什麼需要在 MySQL 內部遍歷資料表？

雖然現代開發多建議在應用層（PHP、Python、Java）處理迴圈邏輯，但在以下場景必須或宜在資料庫層遍歷：

• 存儲程序/觸發器中執行逐列邏輯（如發送通知、同步異構表）
• 跨表複雜業務邏輯，需原子性保證（單一事務內完成）
• 定時任務、資料修正腳本，避免應用層頻繁往返網路

核心機制：游標

MySQL 游標（Cursor）是唯一在存儲程序中逐行處理結果集的方式。特性：

• 唯讀、單向、不可捲動
• 需在 DECLARE 區塊定義，搭配 OPEN、FETCH、CLOSE
• 需宣告 NOT FOUND 條件處理器避免迴圈異常終止

完整範例：為所有用戶建立與管理員的雙向好友關係

DELIMITER $$

CREATE PROCEDURE `MakeFriends`()
BEGIN
    -- 1. 宣告變數
    DECLARE v_adminId BIGINT(12) DEFAULT 1;
    DECLARE v_currentUserId BIGINT(12);
    DECLARE v_existRel INT;
    DECLARE v_done INT DEFAULT 0;

    -- 2. 宣告游標：查詢所有用戶 ID
    DECLARE user_cursor CURSOR FOR
        SELECT `userId` FROM `hw_user`;

    -- 3. 宣告 NOT FOUND 處理器：游標讀完時將 v_done 設為 1
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;

    -- 4. 開啟游標
    OPEN user_cursor;

    -- 5. 讀取第一筆
    FETCH NEXT FROM user_cursor INTO v_currentUserId;

    -- 6. 迴圈遍歷
    read_loop: REPEAT
        IF NOT v_done THEN
            -- 檢查是否已存在關係（避免重複插入）
            SELECT COUNT(*)
            INTO v_existRel
            FROM `hw_relationship`
            WHERE `sourceId` = v_currentUserId
              AND `targetId` = v_adminId;

            -- 非管理員本身且關係不存在時，建立雙向關係
            IF v_currentUserId != v_adminId AND v_existRel = 0 THEN
                INSERT INTO `hw_relationship` (`createTime`, `relationType`, `sourceId`, `targetId`)
                VALUES (NOW(), 1, v_currentUserId, v_adminId);

                INSERT INTO `hw_relationship` (`createTime`, `relationType`, `sourceId`, `targetId`)
                VALUES (NOW(), 1, v_adminId, v_currentUserId);
            END IF;

            -- 讀取下一筆
            FETCH NEXT FROM user_cursor INTO v_currentUserId;
        END IF;
    UNTIL v_done END REPEAT;

    -- 7. 關閉游標
    CLOSE user_cursor;
END$$

DELIMITER ;

關鍵語法解析

替代方案：WHILE 迴圈 + 變數計數器（無游標）

若僅需按主鍵順序處理且 ID 連續/可預測：

CREATE PROCEDURE `BatchProcess`()
BEGIN
    DECLARE v_max_id INT;
    DECLARE v_cur_id INT DEFAULT 1;

    SELECT MAX(`id`) INTO v_max_id FROM `target_table`;

    WHILE v_cur_id <= v_max_id DO
        -- 處理單筆
        UPDATE `target_table` SET `status` = 2 WHERE `id` = v_cur_id;
        SET v_cur_id = v_cur_id + 1;
    END WHILE;
END

缺點：ID 不連續會空轉、無法處理複雜查詢結果集、效能通常不如集合操作。

集合思維 vs 逐列處理：效能天壤之別

核心原則：能用單一 SQL 解決（集合操作），絕不寫迴圈（逐列處理）。

❌ 逐列處理（慢、鎖久、難維護）

-- 想把所有 status=1 的改為 2
DECLARE cur CURSOR FOR SELECT id FROM t WHERE status=1;
-- 迴圈 UPDATE ... WHERE id = v_id;  -- 每次 UPDATE 都有網路往返、鎖開銷

✅ 集合操作（快、原子、簡潔）

UPDATE `t` SET `status` = 2 WHERE `status` = 1;  -- 單條 SQL 完成

何時「不得不」用游標？

1. 需呼叫外部 UDF/存儲程序處理每列（如發送 HTTP 請求、寫檔案）
2. 邏輯極其複雜，無法用 SQL 表達（如遞迴樹狀結構處理）
3. 需在觸發器中逐行驗證/轉換資料

最佳實踐建議

1. 優先重構為集合操作：INSERT ... SELECT、UPDATE ... JOIN、CASE WHEN、窗口函數
2. 若必須用游標：
   • 盡量縮小游標查詢範圍（加 WHERE、LIMIT）
   • 在迴圈內避免再開子游標（巢狀游標極難除錯）
   • 批次提交：每 N 筆 COMMIT 一次，避免長事務
3. 考慮移至應用層：分頁讀取（LIMIT offset, size），應用層並行處理，再批次寫回
4. 監控長執行存儲程序：SHOW PROCESSLIST 觀察 Time 欄位，設定 max_execution_time

總結

MySQL 游標是處理逐列邏輯的標準工具，但90% 的遍歷需求可用集合操作替代並獲得數量級效能提升。掌握游標語法是為了知道「何時不該用它」，真正的高效能來自集合思維與索引設計。