MySQL 批量更新計數器實戰：遊標 + 臨時表 + 變量模擬 ROW_NUMBER 遞增賦值

場景：流水表需按 (產品、類型、時間) 分組遞增計數器，MySQL 8.0 前無窗口函數，需遊標+臨時表模擬

表結構：

CREATE TABLE t_product (
  record_id INT PRIMARY KEY AUTO_INCREMENT,
  product_id INT NOT NULL,
  product_type INT NOT NULL,
  sell_date DATETIME NOT NULL,
  counter INT DEFAULT 0
);

目標：按 (product_id, product_type) 分組，按 sell_date ASC 排序，counter 從 0 遞增。

方案：存儲過程 + 遊標 + 臨時表 + 用戶變量

DELIMITER $$

DROP PROCEDURE IF EXISTS sp_reset_counter $$
CREATE PROCEDURE sp_reset_counter()
BEGIN
  -- 1. 變量聲明
  DECLARE v_product_id INT;
  DECLARE v_type INT;
  DECLARE v_group_cnt INT;
  DECLARE v_done INT DEFAULT 0;
  
  -- 2. 遊標：分組統計
  DECLARE cur CURSOR FOR
    SELECT COUNT(*), product_id, product_type
    FROM t_product
    GROUP BY product_id, product_type;
  
  -- 3. 遊標結束處理器
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;
  
  -- 4. 臨時表：存儲 (record_id, 新counter)
  CREATE TEMPORARY TABLE tmp_counter (
    record_id INT NOT NULL PRIMARY KEY,
    new_counter INT NOT NULL
  ) ENGINE=MEMORY;
  
  -- 5. 打開遊標循環
  OPEN cur;
  read_loop: LOOP
    FETCH cur INTO v_group_cnt, v_product_id, v_type;
    IF v_done = 1 THEN
      LEAVE read_loop;
    END IF;
    
    -- 6. 用戶變量模擬 ROW_NUMBER
    SET @rn := -1;
    INSERT INTO tmp_counter (record_id, new_counter)
    SELECT record_id, (@rn := @rn + 1)
    FROM t_product
    WHERE product_id = v_product_id AND product_type = v_type
    ORDER BY sell_date ASC;
  END LOOP;
  CLOSE cur;
  
  -- 7. 批量更新回原表
  UPDATE t_product p
  JOIN tmp_counter t ON p.record_id = t.record_id
  SET p.counter = t.new_counter;
  
  -- 8. 清理
  DROP TEMPORARY TABLE tmp_counter;
END $$

DELIMITER ;

-- 執行
CALL sp_reset_counter();

關鍵技術點解析

1. DECLARE 順序：變量 → 遊標 → Handler → 臨時表，順序固定，否則報錯。
2. Handler 捕獲 NOT FOUND：遊標 FETCH 結束時觸發，設置標誌位退出循環。
3. 用戶變量 @rn 遞增：SET @rn := -1; SELECT ..., (@rn := @rn + 1) 模擬 ROW_NUMBER()，MySQL 8.0+ 可直接用窗口函數。
4. MEMORY 引擎臨時表：極快，數據量大時防 OOM 可改 InnoDB。
5. JOIN 更新：UPDATE t1 JOIN t2 ON ... SET t1.col = t2.col 單條 SQL 完成批量回寫。

MySQL 8.0+ 現代寫法 (窗口函數)

WITH ranked AS (
  SELECT record_id,
         ROW_NUMBER() OVER (PARTITION BY product_id, product_type ORDER BY sell_date) - 1 AS new_counter
  FROM t_product
)
UPDATE t_product p
JOIN ranked r ON p.record_id = r.record_id
SET p.counter = r.new_counter;

單條 CTE + UPDATE，無需存儲過程、遊標、臨時表，性能碾壓舊版方案。

性能與避坑

• 大數據量 (百萬行) 分組遊標循環慢，建議：應用層分批處理、或用 pt-online-schema-change 思路分塊更新。
• ORDER BY sell_date 必須有索引 INDEX idx_pid_type_date (product_id, product_type, sell_date)，否則 Filesort 極慢。
• 事務包裹：整個過程建議在事務中執行 (或分組提交)，保證一致性。
• 並發安全：執行期間鎖表或業務層停寫，避免新數據插入導致計數器不連續。