MySQL 存儲過程入門實戰：游標循環實現字符級統計

MySQL 存儲過程入門實戰：游標循環實現字符級統計

本文通過一個完整案例，帶領初學者掌握 MySQL 存儲過程、游標聲明、異常處理、雙層循環控制等核心技能。

業務需求

表 spam_keyword 含 629 條記錄，需將 word 字段逐行、逐字符拆分，統計每個字符（含中文、英文、標點、數字）出現總次數，結果存入表 t(id, t, cnt)。

存儲過程完整實現

DROP PROCEDURE IF EXISTS proc134f;
CREATE PROCEDURE proc134f()
BEGIN
  -- 變量聲明區
  DECLARE kidoo INT;
  DECLARE tid INT DEFAULT 0;
  DECLARE stroo INT;
  DECLARE str VARCHAR(255);
  DECLARE strlength INT;
  DECLARE istr VARCHAR(3);
  DECLARE istr_cnt INT;
  DECLARE done INT DEFAULT 0;

  -- 游標聲明：外層遍歷 kid
  DECLARE cur_kid CURSOR FOR SELECT kid FROM spam_keyword;
  -- 異常處理器：游標無數據時觸發
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  -- 初始化
  DELETE FROM t;
  OPEN cur_kid;

  -- 外層循環：逐行處理
  loop1: LOOP
    FETCH cur_kid INTO kidoo;
    IF done = 1 THEN LEAVE loop1; END IF;

    SELECT word INTO str FROM spam_keyword WHERE kid = kidoo;
    SET str = REPLACE(str, ' ', '');      -- 清洗空格
    SET strlength = CHAR_LENGTH(str);     -- 字符長度（非字節）
    SET stroo = 0;

    -- 內層循環：逐字符處理
    loop2: LOOP
      SET stroo = stroo + 1;
      SET istr = SUBSTR(str, stroo, 1);   -- 截取單字符

      -- 統計邏輯：存在則累加，不存在則插入
      SELECT COUNT(*) INTO istr_cnt FROM t WHERE t = istr;
      IF istr_cnt > 0 THEN
        UPDATE t SET cnt = cnt + 1 WHERE t = istr;
      ELSE
        SET tid = tid + 1;
        INSERT INTO t SET id = tid, t = istr, cnt = 1;
      END IF;

      IF stroo >= strlength THEN LEAVE loop2; END IF;
    END LOOP loop2;

    SET done = 0;  -- 關鍵：重置標誌供下一行使用
  END LOOP loop1;

  CLOSE cur_kid;
  SELECT * FROM t ORDER BY cnt DESC;  -- 輸出結果
END;

執行與驗證

CALL proc134f();

結果表 t 共 1023 行，高頻字符：「院」(42)、「醫」(40)、「小」(37)……涵蓋中英標數。

核心知識點拆解

1. 變量聲明：DECLARE var_name type [DEFAULT value]，必須在 BEGIN...END 塊開頭集中聲明。
2. 游標四步法：DECLARE → OPEN → FETCH → CLOSE。
3. NOT FOUND 處理器：DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1，優雅終止循環的標準模式。
4. 雙層循環控制：標籤 loop1: loop2: 配合 LEAVE 實現多層跳出；內層結束後必須重置 done=0，否則外層直接退出。
5. 字符處理函數：CHAR_LENGTH（字符數）、SUBSTR(str, pos, len)、REPLACE。

性能提示與避坑

• 逐行逐字處理屬 RBAR（Row By Agonizing Row）模式，大數據量極慢。
• 生產環境建議：應用層批量處理、或 MySQL 8.0+ 使用 REGEXP_SUBSTR/JSON_TABLE 集合操作、或導出至 Spark/Flink 分布式計算。
• 存儲過程調試困難，複雜邏輯建議配合 SELECT ... INTO @var 打印中間值。

此案例為存儲過程入門絕佳練手項目，掌握其模式可應對多數過程式邏輯需求。