MySQL 預存程序實戰：游標循環重排序號優化與單條 SQL 替代方案

業務場景

表 T_PRO_PRODUCT 按商家 MERCHANT_ID 分組，需依 sequence（可為 NULL）與 create_dt 重新分配連續自然數序號，處理重複序號衝突。

方案一：標準游標循環（教學示範）

DROP PROCEDURE IF EXISTS P_PRODUCT_SEQUENCE;

CREATE PROCEDURE P_PRODUCT_SEQUENCE(IN v_merchar_id VARCHAR(100))
BEGIN
  DECLARE v_id VARCHAR(100);
  DECLARE v_rowNo INT;
  DECLARE done INT DEFAULT 0;
  DECLARE e_error INT DEFAULT 0;

  -- 遊標：按 sequence NULL 排後、sequence 升序、建立時間升序
  DECLARE c_cur CURSOR FOR
    SELECT a.id, (@rowNum := @rowNum + 1) AS rowNo
    FROM T_PRO_PRODUCT a
    JOIN (SELECT @rowNum := 0) b
    WHERE a.MERCHANT_ID = v_merchar_id
    ORDER BY ISNULL(a.sequence), a.sequence, a.create_dt;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET e_error = 1;

  OPEN c_cur;

  REPEAT
    FETCH c_cur INTO v_id, v_rowNo;
    IF NOT done THEN
      UPDATE T_PRO_PRODUCT SET sequence = v_rowNo WHERE ID = v_id;
    END IF;
  UNTIL done END REPEAT;

  CLOSE c_cur;

  IF e_error = 1 THEN
    ROLLBACK;
  ELSE
    COMMIT;
  END IF;
END;

方案二：單條 SQL 變數累加（生產推薦，效能提升 10x+）

SET @colNo = 0;
UPDATE T_PRO_PRODUCT
SET SEQUENCE = (@colNo := @colNo + 1)
WHERE MERCHANT_ID = 'TEST66'
ORDER BY ISNULL(SEQUENCE), SEQUENCE, CREATE_DT DESC;

效能對比

適用場景建議

• 單條 UPDATE：批量重排序、排名計算、連續編號生成 — 首選。
• 游標/預存程序：需逐行複雜邏輯判斷、跨表聯動、動態 SQL 構建 — 必要時使用。
• MySQL 8.0+ 可直接使用 ROW_NUMBER() OVER (PARTITION BY merchant_id ORDER BY ...) 窗口函數，更標準、可讀性更強。

注意事項

1. 變數 @colNo 為會話級，併發環境下需確保單線程執行或加表鎖。
2. ORDER BY 子句必須與業務排序規則完全一致，否則序號分配錯誤。
3. 大表操作建議分批（如每批 5000 行）配合 LIMIT，避免長事務阻塞。