萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 字串函數完全指南:連接、剪接、進制轉換、定位、格式化、比較 (LIKE/REGEXP/STRCMP)

MySQL 提供豐富的字串函數支援資料清洗、轉換、比較與搜尋。本文系統整理 CONCAT/CONCAT_WS、INSERT、CONV/HEX、ELT/FIELD/FIND_IN_SET、FORMAT、LIKE/REGEXP/STRCMP 等核心函數語法、行為細節、實戰範例與 PHP 對照,構建完整字串操作知識體系。

1. 字串連接:CONCAT / CONCAT_WS

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)。

2. 字串剪接/替換:INSERT(str, pos, len, newstr)

行為規則 (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 超出)

3. 進制轉換:CONV / HEX

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)。

4. 字串定位與集合查找:ELT / FIELD / FIND_IN_SET

函數用途回傳範例
ELT(N, str1, str2...)按索引取值 (1-based)字串/NULLELT(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。

5. 數字格式化:FORMAT(X, D)

SELECT FORMAT(12332.123456, 4);  -- '12,332.1235' (千分位分隔、四捨五入)
SELECT FORMAT(12332.1, 4);       -- '12,332.1000' (補零)
SELECT FORMAT(12332.2, 0);       -- '12,332' (無小數)

回傳 **字串**,適合顯示層直接使用;計算請用原始數值。

6. 字串比較四大規則

  1. 數字 ⇄ 字串 **隱式雙向轉換** ('123' = 123 為真)。
  2. 明確轉字串用 CAST(x AS CHAR) 或 CONCAT(x)。
  3. 參數含二進位字串 (BINARY/BLOB) → 結果為二進位字串 (區分大小寫、按位元組比較)。
  4. 任一運算元為 **區分大小寫** (二進位或 COLLATE ..._cs) → 整體比較區分大小寫。

7. 模式匹配:LIKE / REGEXP / STRCMP

LIKE (簡單模式)

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 (數字隱式轉字串)

REGEXP / RLIKE (正則表達式)

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 (字典序比較)

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)

PHP 對照快查

MySQLPHP備註
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()千分位、小數位
LIKEfnmatch() / preg_match()LIKE 更簡單
REGEXPpreg_match()PCRE 功能更強
STRCMP()strcmp()

熟練掌握 MySQL 字串函數族群,能在 SQL 層完成大量資料清洗、轉換、比較邏輯,減少應用層往返,提升開發效率與效能。

copyright © 萬盛學電腦網 all rights reserved