MySQL 從字串中提取數字的多種實現方案

場景

欄位值如 '新會員必須購買350元產品'，需提取 350；或 'ss8989ss8989ss8989' 提取所有數字片段。

方案一：用戶定義函數 (UDF) - 迴圈提取 (通用)

CREATE FUNCTION fn_GetNum(s VARCHAR(8000)) RETURNS VARCHAR(8000)
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE ch CHAR(1);
  DECLARE result VARCHAR(8000) DEFAULT '';
  WHILE i <= LENGTH(s) DO
    SET ch = SUBSTRING(s, i, 1);
    IF ch BETWEEN '0' AND '9' THEN
      SET result = CONCAT(result, ch);
    END IF;
    SET i = i + 1;
  END WHILE;
  RETURN result;
END;

用法：SELECT fn_GetNum('abc123def456'); -- 返回 '123456'

方案二：REGEXP 過濾 (MySQL 8.0+ / MariaDB 10.0+)

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

-- 提取第一組連續數字
SELECT SUBSTRING('abc123def456', 
       LOCATE(REGEXP_SUBSTR('abc123def456', '[0-9]+'), 'abc123def456'),
       LENGTH(REGEXP_SUBSTR('abc123def456', '[0-9]+')));

方案三：遞歸 CTE (MySQL 8.0+)

WITH RECURSIVE nums AS (
  SELECT 1 AS pos, '' AS out_str, 'abc123def456' AS src
  UNION ALL
  SELECT pos+1,
         CASE WHEN SUBSTRING(src, pos, 1) BETWEEN '0' AND '9' 
              THEN CONCAT(out_str, SUBSTRING(src, pos, 1)) 
              ELSE out_str END,
         src
  FROM nums WHERE pos <= LENGTH(src)
)
SELECT out_str FROM nums WHERE pos = (SELECT MAX(pos) FROM nums);

方案四：舊版本技巧 (數字表 + REPLACE)

-- 建立 0-9 數字表
CREATE TABLE digits (d CHAR(1));
INSERT INTO digits VALUES ('0'),('1'),...('9');

-- 利用 REPLACE 逐個移除非數字 (效能差，僅示意)
SELECT REPLACE(REPLACE(REPLACE(col, 'a',''), 'b','')...); -- 不推薦生產使用

方案五：應用層處理 (強烈建議)

SQL 不擅長複雜字串解析。在 PHP/Python/Java/Go 中用正則 \d+ 一行搞定，效能、可讀性、維護性全勝。

-- PHP 範例
preg_match_all('/\d+/', $str, $matches);
$all_digits = implode('', $matches[0]);

結語

MySQL 8.0 以前純 SQL 提取數字寫法繁瑣且效能低。推薦：MySQL 8.0+ 用 REGEXP_REPLACE/REGEXP_SUBSTR；舊版本建 UDF 或交給應用層。避免在查詢關鍵路徑做複雜字串運算。