MySQL 存儲過程實現雙游標巢狀循環：計算關鍵詞週流量增長的實戰案例

MySQL 存儲過程雙游標巢狀循環實戰

業務背景

統計網站關鍵詞本週 Google 流量與上週差值。日誌分析程序寫入周統計表，需對比最近兩週同關鍵詞的 s_gv（Google 流量），計算差值更新 d_value。

表結構

CREATE TABLE mobile_keywords_weeklog (
  id INT AUTO_INCREMENT PRIMARY KEY,
  topdate DATE,              -- 統計週日期
  keywords_id INT,           -- 關鍵詞ID
  s_gv INT,                  -- Google 流量
  d_value INT DEFAULT 0      -- 與上週差值
) ENGINE=MyISAM;

存儲過程核心邏輯

1. 游標 all_week：取最近兩週日期（curr_week, last_week）。
2. 外層游標 cur1：遍歷本週所有記錄（a1=id, b1=keywords_id, c1=s_gv）。
3. 內層游標 cur2：遍歷上週所有記錄（a2, b2, c2）。
4. 巢狀循環匹配 keywords_id，計算 d = c1 - c2 更新本週 d_value；無匹配則直接用本週值。

完整代碼

DELIMITER $$
CREATE PROCEDURE `pro_week_stat`()
top: BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE curr_week, last_week DATE;
  DECLARE a1,b1,c1, a2,b2,c2, d, is_update INT;
  DECLARE all_week CURSOR FOR
    SELECT topdate FROM mobile_keywords_weeklog GROUP BY topdate ORDER BY topdate DESC LIMIT 2;
  DECLARE cur1 CURSOR FOR
    SELECT id, keywords_id, s_gv FROM mobile_keywords_weeklog WHERE topdate = curr_week;
  DECLARE cur2 CURSOR FOR
    SELECT id, keywords_id, s_gv FROM mobile_keywords_weeklog WHERE topdate = last_week;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

  OPEN all_week;
  FETCH all_week INTO curr_week;
  FETCH all_week INTO last_week;
  CLOSE all_week;
  IF IFNULL(curr_week,'')='' OR IFNULL(last_week,'')='' THEN LEAVE top; END IF;

  OPEN cur1;
  out_repeat: REPEAT
    FETCH cur1 INTO a1,b1,c1;
    BEGIN
      DECLARE done1 INT DEFAULT 0;
      DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done1 = 1;
      OPEN cur2;
      inner_repeat: REPEAT
        FETCH cur2 INTO a2,b2,c2;
        IF NOT done1 THEN
          SET is_update=0;
          IF b1 = b2 THEN
            SET d = c1-c2; SET is_update=1;
            UPDATE mobile_keywords_weeklog SET d_value=d WHERE id=a1;
            LEAVE inner_repeat;
          END IF;
        END IF;
      UNTIL done1 END REPEAT inner_repeat;
      CLOSE cur2;
    END;
    IF is_update <> 1 THEN
      UPDATE mobile_keywords_weeklog SET d_value=c1 WHERE id=a1;
    END IF;
  UNTIL done END REPEAT out_repeat;
  CLOSE cur1;
END$$
DELIMITER ;

調用方式

CALL pro_week_stat();

關鍵點解析

• 雙游標需分別聲明 CONTINUE HANDLER，內層用局部變量 done1 避免衝突。
• LEAVE inner_repeat 跳出內層循環，LEAVE top 跳出整個過程。
• 若上週無對應關鍵詞，is_update 保持 0，直接寫入本週值。
• 存儲過程在 DB 端完成聚合，避免應用層頻繁查詢，大幅降低資料庫壓力。