MySQL 存儲過程實戰：游標雙層循環實現字符拆分統計

MySQL 存儲過程實戰：游標雙層循環實現字符拆分統計

本文通過一個實際案例，演示如何使用 MySQL 存儲過程、游標與雙層循環，將表中文本字段按字符拆分並統計每個字符的出現次數。

場景描述

源表 spam_keyword 包含 629 條記錄，需將每條記錄的 word 字段逐字符拆分，統計所有字符頻次，結果存入表 t（結構：id、t(字符)、cnt(計數)）。

表結構定義

CREATE TABLE `spam_keyword` (
  `kid` int(11) NOT NULL,
  `word` varchar(255) DEFAULT NULL,
  `styles` varchar(50) DEFAULT NULL,
  `cids` varchar(100) DEFAULT NULL,
  `is_active` smallint(6) DEFAULT NULL,
  `tm_add` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`kid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

存儲過程完整代碼

DROP PROCEDURE IF EXISTS proc134f;
CREATE PROCEDURE proc134f()
BEGIN
  DECLARE kidoo INT;           -- 當前 kid
  DECLARE tid INT DEFAULT 0;   -- 目標表自增 id
  DECLARE stroo INT;           -- 字符位置指針
  DECLARE str VARCHAR(255);    -- 當前 word 字符串
  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;
  -- 異常處理：未找到數據時置 done=1
  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 行不重複字符統計，最高頻為「醫」(40次)、「院」(42次)，涵蓋中文、英文、標點、數字。

關鍵技術點總結

• 游標聲明與處理器：DECLARE CURSOR + CONTINUE HANDLER FOR NOT FOUND 是遍歷結果集的標準模式。
• 雙層循環控制：外層遍歷行，內層遍歷字符，需妥善管理 done 標誌重置。
• 字符處理函數：CHAR_LENGTH 獲取字符數（非字節數）、SUBSTR 截取單字符、REPLACE 清洗數據。
• 統計邏輯：先查後更新/插入，實現計數器功能。

此案例展示了 MySQL 存儲過程處理複雜業務邏輯的能力，但需注意：大數據量下逐行逐字處理性能較差，生產環境建議考慮應用層處理或使用窗口函數（MySQL 8.0+）優化。