字串函數分類總覽
MySQL 提供豐富字串函數,主要分為:大小寫轉換、長度計算、子字串擷取、搜尋定位、替換修改、格式化、編碼轉換、比較排序、聚合串聯 等類別。
核心函數詳細解析
1. 大小寫轉換
| 函數 | 說明 | 範例 | 結果 |
LOWER(str) / LCASE(str) | 轉小寫 | LOWER('MySQL') | mysql |
UPPER(str) / UCASE(str) | 轉大寫 | UPPER('mysql') | MYSQL |
✅ 支援多位元組字元(中文不影響)
2. 長度與位元組計算
| 函數 | 單位 | 中文範例 | 結果 | 說明 |
CHAR_LENGTH(str) / CHARACTER_LENGTH() | 字元數 | CHAR_LENGTH('中文') | 2 | 一個中文 = 1 字元 |
LENGTH(str) / OCTET_LENGTH() | 位元組數 | LENGTH('中文') | 6 | UTF-8 一中文 3 bytes |
BIT_LENGTH(str) | 位元數 | BIT_LENGTH('AB') | 16 | 2 字元 × 8 bits |
3. 子字串擷取
| 函數 | 語法 | 說明 | 範例結果 |
SUBSTRING() / SUBSTR() / MID() | SUBSTRING(str, pos[, len]) | 從 pos 開始(1-based),長度 len。pos 可為負(從尾算起) | SUBSTRING('MySQL', 2, 3) → 'ySQ'
SUBSTRING('MySQL', -3) → 'SQL' |
LEFT(str, len) | 左側 len 字元 | LEFT('MySQL', 2) → 'My' |
RIGHT(str, len) | 右側 len 字元 | RIGHT('MySQL', 3) → 'SQL' |
4. 搜尋與定位
| 函數 | 語法 | 說明 | 範例 |
LOCATE() / POSITION() / INSTR() | LOCATE(substr, str[, pos]) | 回傳 substr 在 str 中第一次出現位置(1-based),找不到回 0。INSTR 參數順序相反 | LOCATE('bar', 'foobarbar') → 4
LOCATE('bar', 'foobarbar', 5) → 7 |
FIND_IN_SET() | FIND_IN_SET(str, strlist) | strlist 為逗號分隔字串,回傳位置(1-based) | FIND_IN_SET('b', 'a,b,c') → 2 |
5. 替換與修改
| 函數 | 語法 | 說明 | 範例 |
REPLACE() | REPLACE(str, from, to) | 所有 from 替換為 to | REPLACE('www.mysql.com', 'w', 'Ww') → 'WwWwWw.mysql.com' |
INSERT() | INSERT(str, pos, len, newstr) | 從 pos 長度 len 替換為 newstr | INSERT('Quadratic', 3, 4, 'What') → 'QuWhattic' |
REVERSE() | REVERSE(str) | 字串反轉 | REVERSE('abc') → 'cba' |
6. 去除空白與填充
| 函數 | 語法 | 說明 | 範例 |
TRIM() | TRIM([BOTH|LEADING|TRAILING] [remstr] FROM str) | 預設去首尾空白,可指定字元 | TRIM(' bar ') → 'bar'
TRIM(LEADING 'x' FROM 'xxxbar') → 'bar' |
LTRIM() | LTRIM(str) | 去前導空白 | LTRIM(' abc') → 'abc' |
RTRIM() | RTRIM(str) | 去尾隨空白 | RTRIM('abc ') → 'abc' |
LPAD() | LPAD(str, len, padstr) | 左側填充至 len 長度 | LPAD('5', 3, '0') → '005' |
RPAD() | RPAD(str, len, padstr) | 右側填充至 len 長度 | RPAD('5', 3, '0') → '500' |
7. 字串串聯
-- CONCAT:任一參數為 NULL 結果為 NULL
SELECT CONCAT('My', 'SQL'); -- 'MySQL'
SELECT CONCAT('My', NULL, 'QL'); -- NULL
-- CONCAT_WS:第一參數為分隔符,忽略 NULL(但保留空字串)
SELECT CONCAT_WS(',', 'A', 'B', NULL, 'C'); -- 'A,B,C'
8. 編碼與進制轉換
| 函數 | 說明 | 範例 |
ASCII() | 最左字元 ASCII 碼 | ASCII('A') → 65 |
ORD() | 多位元組字元編碼值 | ORD('中') → 20013 |
CHAR() | 編碼值轉字元 | CHAR(77,121,83,81,76) → 'MySQL' |
HEX() / UNHEX() | 十六進制互轉 | HEX('MySQL') → '4D7953514C' |
CONV() | 進制轉換 | CONV('FF', 16, 10) → '255' |
BIN() / OCT() | 二進制/八進制字串 | BIN(12) → '1100' |
9. 特殊功能函數
| 函數 | 說明 | 範例 |
FIELD() | 回傳 str 在列表中位置(1-based),找不到回 0 | FIELD('b', 'a', 'b', 'c') → 2 |
ELT() | 回傳第 N 個參數(FIELD 反操作) | ELT(2, 'a', 'b', 'c') → 'b' |
EXPORT_SET() | 位元遮罩轉字串 | EXPORT_SET(5, 'Y', 'N', ',', 4) → 'Y,N,Y,N' |
MAKE_SET() | 位元遮罩建集合字串 | MAKE_SET(5, 'a', 'b', 'c') → 'a,c' |
SOUNDEX() | 英文發音編碼(4 碼) | SOUNDEX('Hello') → 'H400' |
SPACE() | 產生 N 個空白 | SPACE(4) → ' ' |
REPEAT() | 重複字串 N 次 | REPEAT('ab', 3) → 'ababab' |
QUOTE() | SQL 字串轉義加引號 | QUOTE("Don't") → ''Don\'t!'' |
10. 壓縮與檔案操作
-- 需編譯 zlib 支援
COMPRESS(str) -- 壓縮字串(BLOB 儲存)
UNCOMPRESS(str) -- 解壓縮
UNCOMPRESSED_LENGTH(compressed_str) -- 原始長度
LOAD_FILE('/path/file') -- 讀取伺服器檔案(需 FILE 權限)
字串比較與排序
STRCMP(str1, str2):比較兩字串,相等 0,str1<str2 為 -1,反之 1
- 比較遵循排序規則:
utf8mb4_general_ci 不分大小寫、utf8mb4_bin 二進位比較
BINARY str 或 CAST(str AS BINARY) 強制二進位比較
實戰技巧集錦
1. 產生固定長度代碼
SELECT LPAD(id, 8, '0') AS code FROM users; -- 00000001
2. 解析 CSV 欄位
-- 取第 3 個值
SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(csv_col, ',', 3), ',', -1))
FROM import_table;
3. 關鍵字高亮(搜尋結果)
SELECT REPLACE(content, '關鍵字', '關鍵字') AS highlighted
FROM articles
WHERE content LIKE '%關鍵字%';
4. 移除特殊字元(保留中英數)
-- MySQL 8.0+ REGEXP_REPLACE
SELECT REGEXP_REPLACE(dirty_text, '[^a-zA-Z0-9\u4e00-\u9fa5]', '');
-- 8.0 前:用 REPLACE 串聯或應用層處理
5. 字串聚合(GROUP_CONCAT 進階)
SELECT
category,
GROUP_CONCAT(DISTINCT product_name ORDER BY price DESC SEPARATOR ' | ') AS products
FROM products
GROUP BY category;
效能與注意事項
- max_allowed_packet:函數回傳長度超過此值回傳 NULL(預設 64MB)
- group_concat_max_len:GROUP_CONCAT 最大長度(預設 1024,可調大)
- 避免在 WHERE 大量使用字串函數(如
WHERE LOWER(name) = 'xxx'),阻礙索引 → 改用 WHERE name = 'xxx' COLLATE utf8mb4_general_ci 或功能性索引(MySQL 8.0+)
- 二進位字串 vs 非二進位:含 BLOB/二進位參數結果為二進位,影響比較行為
掌握字串函數組合拳,資料清洗、報表格式化、搜尋處理皆可在 SQL 層高效完成!