萬盛學電腦網

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

MySQL 字串函數完整參考:操作、比較、轉換與實戰技巧

字串函數分類總覽

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('中文')6UTF-8 一中文 3 bytes
BIT_LENGTH(str)位元數BIT_LENGTH('AB')162 字元 × 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 替換為 toREPLACE('www.mysql.com', 'w', 'Ww') → 'WwWwWw.mysql.com'
INSERT()INSERT(str, pos, len, newstr)從 pos 長度 len 替換為 newstrINSERT('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),找不到回 0FIELD('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 層高效完成!

copyright © 萬盛學電腦網 all rights reserved