MySQL 提供豐富的字串函數支援資料清洗、轉換、比較與搜尋。本文系統整理 CONCAT/CONCAT_WS、INSERT、CONV/HEX、ELT/FIELD/FIND_IN_SET、FORMAT、LIKE/REGEXP/STRCMP 等核心函數語法、行為細節、實戰範例與 PHP 對照,構建完整字串操作知識體系。
CONCAT(str1, str2, ...) -- 任一參數為 NULL → 回傳 NULL
CONCAT_WS(separator, str1, str2, ...) -- 忽略 NULL 參數,分隔符優先
SELECT CONCAT('My', 'S', 'QL'); -- 'MySQL'
SELECT CONCAT('My', NULL, 'QL'); -- NULL
SELECT CONCAT_WS(',', 'First', 'Second', 'Last'); -- 'First,Second,Last'
SELECT CONCAT_WS(',', 'First', NULL, 'Last'); -- 'First,Last' (忽略 NULL)
數字自動轉字串: SELECT CONCAT(14.3); -- '14.3',若需控制格式用 CAST(x AS CHAR)。
行為規則 (1-based 索引):
pos 開始 (含第 pos 字元),刪除 len 字元,插入 newstr。pos > 長度 → 尾端拼接 newstr。len > 剩餘長度 → 從 pos 到末尾全替換。pos < 0 → 回傳原字串。len < 0 → 視為超出剩餘長度 (全替換)。SELECT INSERT('Quadratic', 3, 4, 'What'); -- 'QuWhattic' (adr → What)
SELECT INSERT('Quadratic', -1, 4, 'What'); -- 'Quadratic' (pos<0)
SELECT INSERT('Quadratic', 3, 100, 'What'); -- 'QuWhat' (len 超出)
CONV(N, from_base, to_base) -- 2~36 進制互轉,回傳字串
HEX(N_or_S) -- 數字→16進位字串;字串→每字元 2 位 16 進位
SELECT CONV('a', 16, 2); -- '1010'
SELECT CONV('6E', 18, 8); -- '172'
SELECT CONV(-17, 10, -18); -- '-H' (負數目標基數視為有號)
SELECT HEX(255); -- 'FF'
SELECT HEX('abc'); -- '616263'
SELECT 0x616263; -- 'abc' (16 進位字面值)
PHP 對應: base_convert($num, $from, $to)。
| 函數 | 用途 | 回傳 | 範例 |
|---|---|---|---|
ELT(N, str1, str2...) | 按索引取值 (1-based) | 字串/NULL | ELT(2, 'a','b','c') → 'b' |
FIELD(str, str1, str2...) | 找 str 在列表中位置 | 整數 (0=不存在) | FIELD('b', 'a','b','c') → 2 |
FIND_IN_SET(str, strlist) | 找 str 在逗號分隔列表中位置 | 整數 (0=不存在) | FIND_IN_SET('b', 'a,b,c') → 2 |
關鍵差異: FIND_IN_SET 專為 SET 類型/逗號分隔字串優化 (位元運算);FIELD 為通用列表查找;ELT 為索引取值。**互為反函數**:ELT(FIELD(str, ...), ...) = str。
SELECT FORMAT(12332.123456, 4); -- '12,332.1235' (千分位分隔、四捨五入)
SELECT FORMAT(12332.1, 4); -- '12,332.1000' (補零)
SELECT FORMAT(12332.2, 0); -- '12,332' (無小數)
回傳 **字串**,適合顯示層直接使用;計算請用原始數值。
'123' = 123 為真)。CAST(x AS CHAR) 或 CONCAT(x)。COLLATE ..._cs) → 整體比較區分大小寫。expr LIKE pat [ESCAPE 'escape_char']
-- 通配符:% (任意長度), _ (單字元)
-- 轉義:\% \_ \\ (或自訂 ESCAPE '|')
SELECT 'David!' LIKE 'David_'; -- 1
SELECT 'David_' LIKE 'David|_' ESCAPE '|'; -- 1
SELECT 'abc' LIKE 'ABC'; -- 1 (預設不區分大小寫)
SELECT 'abc' LIKE BINARY 'ABC'; -- 0 (區分大小寫)
SELECT 10 LIKE '1%'; -- 1 (數字隱式轉字串)
expr REGEXP pat
-- 不區分大小寫 (除非 BINARY)
SELECT 'Monty!' REGEXP '.*'; -- 1
SELECT 'new*n*line' REGEXP 'new\\*.*line'; -- 1 (轉義 *)
SELECT 'a' REGEXP 'A'; -- 1
SELECT 'a' REGEXP BINARY 'A'; -- 0
SELECT 'a' REGEXP '^[a-d]'; -- 1
注意: REGEXP **不支援多位元組字元** (UTF-8 中文按位元組匹配),MySQL 8.0+ 改用 ICU 正則引擎已支援 Unicode。
STRCMP(expr1, expr2) -- 相等 0, expr1 < expr2 → -1, expr1 > expr2 → 1
SELECT STRCMP('text', 'text2'); -- -1
SELECT STRCMP('text2', 'text'); -- 1
SELECT STRCMP('text', 'text'); -- 0
使用當前字符集校對規則比較;二進位字串按位元組值比較。
expr NOT LIKE pat [ESCAPE ...]
expr NOT REGEXP pat
expr NOT RLIKE pat
-- 等價於 NOT (expr LIKE/REGEXP/RLIKE pat)
| MySQL | PHP | 備註 |
|---|---|---|
CONCAT() | implode() / . | PHP 可用陣列 join |
CONCAT_WS() | implode($sep, $arr) | 完全對應 |
INSERT() | substr_replace() | 參數順序略異 |
CONV() | base_convert() | |
HEX() | bin2hex() | |
FIELD() | array_search() / in_array() | |
FIND_IN_SET() | in_array($needle, explode(',', $haystack)) | |
FORMAT() | number_format() | 千分位、小數位 |
LIKE | fnmatch() / preg_match() | LIKE 更簡單 |
REGEXP | preg_match() | PCRE 功能更強 |
STRCMP() | strcmp() |
熟練掌握 MySQL 字串函數族群,能在 SQL 層完成大量資料清洗、轉換、比較邏輯,減少應用層往返,提升開發效率與效能。