MySQL 預存程序實現雙游標巢狀循環計算週流量差值

在 PHP 開發中若需統計關鍵字本週與上週 Google 流量差值，若在應用層循環查詢資料庫會造成極大壓力。本文展示如何透過 MySQL 預存程序，利用雙游標巢狀循環在資料庫層完成計算，大幅降低網路往返與應用層負擔。

資料表結構

CREATE TABLE `mobile_keywords_weeklog` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `topdate` DATE DEFAULT NULL,
  `keywords_id` INT(11) DEFAULT '0',
  `s_pv` INT(11) DEFAULT '0',
  `s_gv` INT(11) DEFAULT '0',
  `s_bv` INT(11) DEFAULT '0',
  `d_value` INT(11) DEFAULT '0' COMMENT '與上一周的差值',
  PRIMARY KEY (`id`),
  KEY `topdate` (`topdate`),
  KEY `keywords_id` (`keywords_id`),
  KEY `s_gv` (`s_gv`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

預存程序邏輯

1. 取得最近兩週的日期（curr_week、last_week）。
2. 外層游標 cur1 讀取本週所有關鍵字流量（id, keywords_id, s_gv）。
3. 內層游標 cur2 讀取上週對應資料。
4. 巢狀循環比對 keywords_id，找到匹配則計算差值 d = c1 - c2 並更新 d_value；若上週無資料則直接以本週流量作為差值。
5. 使用 CONTINUE HANDLER FOR SQLSTATE '02000' 捕捉游標結束狀態，避免無限循環。

完整程式碼

DELIMITER $$
USE `brother_mobile`$$
DROP PROCEDURE IF EXISTS `pro_week_stat`$$
CREATE DEFINER=`root`@`%` PROCEDURE `pro_week_stat`()
top: BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE curr_week DATE;
  DECLARE last_week DATE;
  DECLARE a1,b1,c1 INT;
  DECLARE a2,b2,c2 INT;
  DECLARE d INT;
  DECLARE is_update INT DEFAULT 0;

  DECLARE all_week CURSOR FOR 
    SELECT topdate FROM brother_mobile.mobile_keywords_weeklog 
    GROUP BY topdate ORDER BY topdate DESC LIMIT 0,2;

  DECLARE cur1 CURSOR FOR 
    SELECT id, keywords_id, s_gv FROM brother_mobile.mobile_keywords_weeklog WHERE topdate = curr_week;

  DECLARE cur2 CURSOR FOR 
    SELECT id, keywords_id, s_gv FROM brother_mobile.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 done THEN
          SET is_update = 0;
          IF b1 = b2 THEN
            SET d = c1 - c2;
            SET is_update = 1;
            UPDATE brother_mobile.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 brother_mobile.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();

此模式可擴展至任何需「當前集合與歷史集合逐筆比對」的統計場景，將計算下推至資料庫層，效能提升顯著。