MySQL 從字串提取多個數值取最大值：使用 REGEXP 提取數字再排序，或寫自定義函數

資料範例：`YJV-3*50+1*25` → 最大值 50；`JKLGYJ-1-120/20` → 120。需從字串中解析所有數字並取最大值。

方案一：MySQL 8.0+ 使用 REGEXP_SUBSTR + 遞歸 CTE（純 SQL）

WITH RECURSIVE nums AS (
  SELECT id, spec,
         CAST(REGEXP_SUBSTR(spec, '[0-9]+') AS UNSIGNED) AS val,
         REGEXP_REPLACE(spec, '^[^0-9]*[0-9]+', '') AS rest
  FROM cables
  UNION ALL
  SELECT id, spec,
         CAST(REGEXP_SUBSTR(rest, '[0-9]+') AS UNSIGNED),
         REGEXP_REPLACE(rest, '^[^0-9]*[0-9]+', '')
  FROM nums
  WHERE rest != ''
)
SELECT id, spec, MAX(val) AS max_val
FROM nums
GROUP BY id, spec;

方案二：建立自定義函數（MySQL 5.7+ 相容）

CREATE FUNCTION `max_num_in_str`(input_str TEXT) RETURNS INT
DETERMINISTIC
BEGIN
  DECLARE max_val INT DEFAULT 0;
  DECLARE pos INT DEFAULT 1;
  DECLARE len INT;
  DECLARE num_str VARCHAR(20);
  SET len = CHAR_LENGTH(input_str);
  WHILE pos <= len DO
    IF SUBSTRING(input_str, pos, 1) BETWEEN '0' AND '9' THEN
      SET num_str = '';
      WHILE pos <= len AND SUBSTRING(input_str, pos, 1) BETWEEN '0' AND '9' DO
        SET num_str = CONCAT(num_str, SUBSTRING(input_str, pos, 1));
        SET pos = pos + 1;
      END WHILE;
      IF CAST(num_str AS UNSIGNED) > max_val THEN
        SET max_val = CAST(num_str AS UNSIGNED);
      END IF;
    ELSE
      SET pos = pos + 1;
    END IF;
  END WHILE;
  RETURN max_val;
END;

使用：`SELECT max_num_in_str(spec) FROM cables;`

方案三：應用層處理（推薦生產環境）

在 Java/Python/Go 等服務層用正則 `\\d+` 全域匹配轉數字取最大值，再寫回資料庫或直接回傳前端。資料庫層面處理字串解析效能差、難維護，應用層靈活且效能高。

效能建議

• 若頻繁查詢，考慮**新增欄位 `max_spec_value`** 在寫入時由應用層計算好存入，查詢直接用索引。
• 避免在 `WHERE`/`JOIN` 中使用正則/函數，導致全表掃描。

MySQL 8.0 的 `REGEXP_SUBSTR`/`REGEXP_REPLACE` 讓純 SQL 解析成為可能，但生產環境仍建議**應用層預計算 + 存欄位**最穩健。