MySQL SUBSTRING 與 SUBSTRING_INDEX 字符串截取函數全解析

MySQL SUBSTRING 與 SUBSTRING_INDEX 字符串截取函數全解析

MySQL 提供 SUBSTRING (別名 SUBSTR、MID) 與 SUBSTRING_INDEX 兩大字符串截取函數，前者按位置/長度切片，後者按分隔符切分。本文詳細說明語法變體、參數行為、負數索引語義、實戰範例與效能注意事項。

SUBSTRING 函數族

語法變體 (完全等價)

SUBSTRING(str, pos, len)
SUBSTRING(str FROM pos FOR len)      -- 標準 SQL 風格
SUBSTRING(str, pos)
SUBSTRING(str FROM pos)
SUBSTR(str, pos, len)
MID(str, pos, len)

參數語義

• str： 來源字符串。
• pos： 起始位置，**1-based** (1 為第一字元)。負數表示從末尾倒數 (-1 為最後一字元)。
• len： 截取長度。省略則截取至字符串結尾。

實戰範例

SELECT SUBSTRING('mysql database', 2);        -- 'ysql database' (第 2 字元到結尾)
SELECT SUBSTRING('mysql database', 2, 3);     -- 'ysq' (第 2 字元起 3 字元)
SELECT SUBSTRING('Quadratically', 5);         -- 'ratically'
SELECT SUBSTRING('foobarbar' FROM 4);         -- 'barbar'
SELECT SUBSTRING('Quadratically', 5, 6);      -- 'ratica'
SELECT SUBSTRING('Sakila', -3);               -- 'ila' (倒數 3 字元)
SELECT SUBSTRING('Sakila', -5, 3);            -- 'aki' (倒數 5 起 3 字元)
SELECT SUBSTRING('Sakila' FROM -4 FOR 2);     -- 'ki'

進階用法：配合其他函數

-- 取得域名 (從 '@' 後到結尾)
SELECT SUBSTRING(email, LOCATE('@', email) + 1) FROM users;

-- 取得檔案副檔名 (最後一個 '.' 後)
SELECT SUBSTRING(filename, LOCATE('.', REVERSE(filename)) + 1) FROM files;  -- 錯誤示範，正確見下文

-- 正確取副檔名：SUBSTRING_INDEX 更適合

SUBSTRING_INDEX 函數

語法

SUBSTRING_INDEX(str, delim, count)

參數語義

• str： 來源字符串。
• delim： 分隔符 (可為多字元字符串)。
• count： 分隔符出現次數。
  • 正數： 返回第 count 個分隔符**之前**的子串 (含分隔符前內容)。
  • 負數： 返回第 |count| 個分隔符**之後**的子串 (不含分隔符)。

實戰範例

SELECT SUBSTRING_INDEX('www.example.com', '.', 2);   -- 'www.example' (第 2 個 '.' 之前)
SELECT SUBSTRING_INDEX('www.example.com', '.', -2);  -- 'example.com' (倒數 2 個 '.' 之後)

-- 經典應用：解析 URL、域名、路徑、參數
SELECT SUBSTRING_INDEX('https://api.example.com/v1/users?id=1', '://', -1);  -- 'api.example.com/v1/users?id=1'
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(url, '://', -1), '/', 1);  -- 域名 'api.example.com'

-- 解析 CSV 欄位 (第 3 欄)
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(csv_line, ',', 3), ',', -1);

效能與注意事項

• 字符集影響： 多字節字符集 (utf8mb4) 下，pos、len 以**字元**為單位而非字節，效能略低於二進制字符集。
• 大字符串截取： 對 LONGTEXT 大字段頻繁截取會產生大量臨時對象，建議在應用層處理或使用 SUBSTRING_INDEX 定位後再截取。
• 索引無法直接用於函數列： WHERE SUBSTRING(col,1,3)='abc' 不走索引，需建立函數索引 (MySQL 8.0+) 或冗餘計算列。
• NULL 輸入： 任一參數為 NULL 返回 NULL。

對比總結

最佳實踐： 簡單切片用 SUBSTRING，分隔符結構化資料用 SUBSTRING_INDEX，複雜模式下推至應用層 (Go/Python/PHP 正則庫更強、更快、可單測)。避免在 SQL 中疊加多層嵌套字符串函數，既難讀又難優化。