MySQL 自定義函數：從字符串中提取所有數字

需求場景

業務中常遇到「從混雜字符串提取純數字」的需求，如訂單號 ORD-20240501-001 提取 20240501001，或地址 123號樓4單元 提取 1234。MySQL 內建無現成函數，需自行編寫存儲函數。

函數定義：GetNum

DELIMITER //

CREATE FUNCTION `GetNum`(Varstring VARCHAR(50))
RETURNS VARCHAR(30)
DETERMINISTIC
BEGIN
    DECLARE v_length INT DEFAULT 0;
    DECLARE v_Tmp VARCHAR(50) DEFAULT '';
    
    SET v_length = CHAR_LENGTH(Varstring);
    
    WHILE v_length > 0 DO
        -- ASCII 48-57 對應 '0'-'9'
        IF ASCII(MID(Varstring, v_length, 1)) BETWEEN 48 AND 57 THEN
            SET v_Tmp = CONCAT(v_Tmp, MID(Varstring, v_length, 1));
        END IF;
        SET v_length = v_length - 1;
    END WHILE;
    
    -- 逆序還原原始順序
    RETURN REVERSE(v_Tmp);
END //

DELIMITER ;

使用範例

表字段直接調用

-- 查詢訂單表，提取 order_sn 中的純數字部分
SELECT order_id, GetNum(order_sn) AS pure_num
FROM `order`
WHERE order_sn REGEXP '[0-9]';

性能與優化建議

• 字符集影響：中文環境下 CHAR_LENGTH 計算字符數而非字節，函數邏輯正確。
• 大數據量慎用：逐字符循環屬行級操作，百萬行以上查詢會明顯拖慢。建議：
  ① 應用層預處理存純數字字段；
  ② 或使用 MySQL 8.0 REGEXP_REPLACE(str, '[^0-9]', '') 替代（需 8.0.4+）。
• 索引無效：函數作用於列上會導致全表掃描，無法利用索引。

MySQL 8.0+ 現代寫法（推薦）

-- 無需自定義函數，單條 SQL 完成
SELECT REGEXP_REPLACE('dfdd111fd666', '[^0-9]', '') AS pure_num;
-- 結果：111666

根據版本選擇方案：5.7 及以下用自定義函數，8.0+ 直接用正則替換，更簡潔高效。