MySQL 自定義函數提取字串中數字：GetNum 實作與效能考量

MySQL 提取字串數字自定義函數 GetNum 完整實作

需求：從 VARCHAR 欄位中提取所有數字字符 (0-9)，合併輸出。例如 'abc123def456' → '123456'。

函數完整定義

DELIMITER //
CREATE FUNCTION GetNum(Varstring VARCHAR(50))
RETURNS VARCHAR(30)
DETERMINISTIC
NO SQL
BEGIN
    DECLARE v_length INT DEFAULT 0;
    DECLARE v_Tmp VARCHAR(50) DEFAULT '';
    DECLARE v_char CHAR(1);
    
    SET v_length = CHAR_LENGTH(Varstring);
    
    WHILE v_length > 0 DO
        SET v_char = MID(Varstring, v_length, 1);
        -- ASCII '0'=48, '9'=57
        IF ASCII(v_char) BETWEEN 48 AND 57 THEN
            SET v_Tmp = CONCAT(v_Tmp, v_char);
        END IF;
        SET v_length = v_length - 1;
    END WHILE;
    
    RETURN REVERSE(v_Tmp);
END //
DELIMITER ;

使用範例

SELECT GetNum('dfdfd666');        -- 返回 '666'
SELECT GetNum('dfdd111fd666');    -- 返回 '111666' (非連續數字也能合併)
SELECT GetNum(column_name) FROM table_name;

邏輯解析

1. 從字串末尾向前逐字符檢查 (避免正向迴圈需預知長度)。
2. ASCII() 判斷是否為數字字符 (48-57)。
3. 符合則拼接到暫存變數 (倒序)。
4. 最後 REVERSE() 恢復原序。

效能與局限性分析

進階：MySQL 8.0 正則替換 (REGEXP_REPLACE)

-- MySQL 8.0+ 僅保留數字
SELECT REGEXP_REPLACE('abc123def456', '[^0-9]', '');
-- 返回 '123456'

若環境支援 8.0，強烈建議使用內建正則函數，效能與可讀性遠優於自定義函數。

最佳實踐建議

• 將提取邏輯下推到應用層 (Java/Python/Go 正則極快)。
• 若必須在 SQL 完成，考慮建立 VIRTUAL GENERATED COLUMN (5.7+) 物化提取結果並建索引。
• 避免在高頻查詢的 WHERE/JOIN 條件中直接呼叫此函數。

1. 上一篇文章： MySQL 大小寫敏感導致 Table doesn't exist 錯誤排查與永久修復
2. 下一篇文章： MySQL 忘記 root 密碼重置全平台指南 (Linux/Windows)

MySQL 綜合教學

MySQL 忘記 Root 密碼？跳過授權表重置密碼完整實戰指南

MySQL 忘記 Root 密碼？跳過授權表重置密碼完整實戰

擁抱 MySQL 資料庫技術：從玩具到核心基礎設施的演進與架構師必讀指南

MySQL 已從「玩具級資料庫」進化為互聯網架構不可或缺的核

InnoDB 單表多少行效能最優？B+ 樹三層結構推算與實戰經驗值

理論推算：B+ 樹三層極限 InnoDB 預設頁面大小 16

MySQL 整數類型詳解：TINYINT、SMALLINT、INT、BIGINT 的區別與顯示寬度誤區

MySQL 整數類型完整對比指南 在建表時，許多開發者對