MySQL 函式 – 字串函式教學

簡介

在日常的資料庫開發與維運工作中，字串處理往往是最常見的需求之一。無論是對使用者輸入的資料做清理、從欄位中抽取特定資訊，或是產生動態的報表文字，MySQL 所提供的字串函式都能大幅簡化程式碼、提升效能。
本篇文章針對 MySQL 字串函式 進行系統性說明，從基礎的 CONCAT()、SUBSTRING() 到較進階的正規表達式函式 REGEXP_REPLACE()，一步步帶你了解每個函式的語法、使用情境與常見陷阱，讓初學者能快速上手，也為中級開發者提供最佳實踐的參考。

核心概念

1. 連接與合併字串 – `CONCAT()`、`CONCAT_WS()`

CONCAT(str1, str2, ...)：將多個字串直接相接。若任一參數為 NULL，結果會是 NULL。
CONCAT_WS(separator, str1, str2, ...)：WS 代表 With Separator，在每個字串之間自動插入分隔符號，且會自動忽略 NULL。

-- 範例 1: 基本的字串連接
SELECT CONCAT('Hello', ', ', 'World!') AS greeting;
-- 結果: Hello, World!

-- 範例 2: 使用 CONCAT_WS() 合併欄位，並以逗號分隔
SELECT CONCAT_WS(', ', first_name, last_name) AS full_name
FROM employees;
-- 若 first_name 為 NULL，結果仍會是 "Doe"

技巧：在產生 CSV 或自訂分隔檔案時，CONCAT_WS() 是最省事的選擇，因為它會自動跳過 NULL，避免產生多餘的分隔符。

2. 取得子字串 – `SUBSTRING()`、`LEFT()`、`RIGHT()`

SUBSTRING(str, pos, len)：從 pos 位置開始，取長度為 len 的子字串。pos 可以是正數（從左往右）或負數（從右往左）。
LEFT(str, len)：取字串最左邊的 len 個字元。
RIGHT(str, len)：取字串最右邊的 len 個字元。

-- 範例 3: 使用 SUBSTRING 取得電話區碼
SELECT SUBSTRING(phone_number, 1, 3) AS area_code
FROM customers;

-- 範例 4: 用 LEFT 取前 5 個字元，常用於截斷過長的說明文字
SELECT LEFT(description, 5) AS short_desc FROM products;

-- 範例 5: 用 RIGHT 取得檔案副檔名
SELECT RIGHT(file_name, 3) AS ext FROM files;

注意：SUBSTRING() 的 pos 若為 0，MySQL 會視為 1（即第一個字元），但在其他資料庫（如 PostgreSQL）行為不同，跨平台開發時要特別留意。

3. 文字長度與字元計算 – `LENGTH()`、`CHAR_LENGTH()`、`CHARACTER_LENGTH()`

LENGTH(str)：回傳字串的 位元組 長度（對於 UTF‑8 多位元組字元會較長）。
CHAR_LENGTH(str) 或 CHARACTER_LENGTH(str)：回傳字串的字元數量，較適合處理多語系文字。

-- 範例 6: 判斷中文與英文的長度差異
SELECT 
    LENGTH('中文') AS byte_len,          -- 6 位元組 (UTF-8 每個中文 3 位元組)
    CHAR_LENGTH('中文') AS char_len;    -- 2 個字元

實務建議：在驗證「字數上限」時，應使用 CHAR_LENGTH()，避免因多位元組字元導致計算錯誤。

4. 大小寫與字串比對 – `UPPER()`、`LOWER()`、`LIKE`、`REGEXP`

UPPER(str) / LOWER(str)：將字串全部轉為大寫或小寫，常用於不區分大小寫的比對。
LIKE：支援 %（任意字元）與 _（單一字元）通配符的模糊搜尋。
REGEXP / REGEXP_REPLACE()：使用正規表達式進行更彈性的匹配與取代。

-- 範例 7: 不分大小寫搜尋使用者名稱
SELECT user_id FROM users
WHERE LOWER(username) = LOWER('Alice');

-- 範例 8: 用 REGEXP 抽取 Email 中的 domain
SELECT REGEXP_SUBSTR(email, '@(.+)$') AS domain
FROM contacts;

陷阱：LIKE 的搜尋會因為資料量大而導致全表掃描，若頻繁使用建議在欄位上建立 FULLTEXT 或 BINARY 索引，或改用 REGEXP 搭配索引。

5. 去除空白與字串清理 – `TRIM()`、`LTRIM()`、`RTRIM()`

TRIM([remstr FROM] str)：預設去除兩側空白，也可指定要移除的字元。
LTRIM(str) / RTRIM(str)：僅去除左側或右側的空白。

-- 範例 9: 清除資料列前後的空白與特殊符號
SELECT TRIM(' * ' FROM ' ***Hello*** ') AS cleaned;
-- 結果: Hello

最佳實踐：在資料寫入前（INSERT / UPDATE）就使用 TRIM() 處理，能減少之後查詢時的額外計算。

6. 文字替換 – `REPLACE()`、`INSERT()`、`ELT()`、`FIELD()`

REPLACE(str, from_str, to_str)：將 str 中所有出現的 from_str 替換成 to_str。
INSERT(str, pos, len, newstr)：在 str 的 pos 位置，先刪除 len 個字元，再插入 newstr。
ELT(N, str1, str2, ...)：根據 N 的值返回對應的字串，常與 FIELD() 搭配使用。

-- 範例 10: 把所有的換行符號換成空格
SELECT REPLACE(content, '\n', ' ') AS one_line FROM articles;

-- 範例 11: 在字串第 4 個位置插入子字串
SELECT INSERT('MySQL', 4, 0, ' is') AS result;  -- MySQL is

提醒：REPLACE() 與 REGEXP_REPLACE() 的差異在於前者只能做簡單的字面取代，後者支援正規表達式，處理複雜模式時請使用 REGEXP_REPLACE()（MySQL 8.0+）。

常見陷阱與最佳實踐

陷阱	說明	解決方案
NULL 參與運算	大多數字串函式在任一參數為 `NULL` 時會返回 `NULL`。	使用 `IFNULL()` 或 `COALESCE()` 先把 `NULL` 轉為空字串 (`''`)。
UTF‑8 位元組 vs. 字元	`LENGTH()` 與 `CHAR_LENGTH()` 行為不同，易導致字數驗證錯誤。	針對「顯示長度」使用 `CHAR_LENGTH()`；針對「儲存大小」使用 `LENGTH()`。
LIKE 效能問題	前置 `%`（如 `%abc`）會使索引失效，造成全表掃描。	盡量避免前置 `%`，改用全文索引 (`FULLTEXT`) 或 `REGEXP` 搭配可索引的前綴。
正規表達式相容性	MySQL 的正規表達式與 PCRE 有細微差異，某些語法不支援。	先在測試環境驗證正則式，必要時使用 `REGEXP_REPLACE()` 的 `flags` 參數。
字串長度截斷	`VARCHAR` 欄位在插入過長字串時會被自動截斷，未必拋出錯誤。	在應用層使用 `CHAR_LENGTH()` 檢查，或在資料庫層加上 `CHECK` 約束。

最佳實踐：

統一編碼：所有資料庫與應用程式皆使用 utf8mb4，避免因字符集不一致導致字串亂碼。
預先清理：寫入前使用 TRIM()、REPLACE() 進行基本清理，減少後續查詢的額外計算。
索引規劃：對於頻繁使用 LIKE 'prefix%' 的欄位，建立 前綴索引（BTREE）可提升效能。
使用參數化查詢：避免直接拼接字串產生 SQL Injection，尤其在使用 CONCAT() 產生動態查詢時。

實際應用場景

1. 產生客製化報表標題

在每月業績報表中，需要根據月份與部門自動產生標題：

SELECT CONCAT_WS(' - ',
        DATE_FORMAT(report_date, '%Y 年 %m 月'),
        department_name,
        '業績報表') AS report_title
FROM monthly_report
WHERE report_date = '2025-10-01';

結果會是 2025 年 10 月 - 銷售部 - 業績報表，直接供前端顯示。

2. 清理使用者輸入的電話號碼

使用者可能輸入帶有空格或破折號的電話號碼，需統一格式：

UPDATE customers
SET phone = REPLACE(REPLACE(TRIM(phone), '-', ''), ' ', '')
WHERE phone IS NOT NULL;

執行後，0912-345-678、0912 345 678 都會變成 0912345678。

3. 從網址中抽取域名

在分析網站流量時，需要把完整 URL 中的 domain 拿出來：

SELECT 
    REGEXP_SUBSTR(url, '^(?:https?://)?([^/]+)') AS domain,
    COUNT(*) AS hits
FROM web_logs
GROUP BY domain
ORDER BY hits DESC;

REGEXP_SUBSTR 只取出 http://example.com 之類的主機名稱，方便後續統計。

4. 動態搜尋支援多關鍵字

使用者在搜尋框輸入多個關鍵字，系統需要把空格轉成 % 讓 LIKE 支援任意順序匹配：

SET @keywords = 'MySQL 教學';
SET @pattern  = CONCAT('%', REPLACE(@keywords, ' ', '%'), '%');

SELECT article_id, title
FROM articles
WHERE title LIKE @pattern;

若關鍵字是 MySQL 教學，最終模式會是 %MySQL%教學%，可匹配「MySQL 入門教學」等變形。

總結

MySQL 的字串函式不只是簡單的文字拼接或切割，它們是 資料清理、文字分析、動態報表與搜尋優化 的關鍵工具。透過本文的六大核心概念：

CONCAT / CONCAT_WS – 靈活的字串合併
SUBSTRING / LEFT / RIGHT – 精準的子字串抽取
LENGTH / CHAR_LENGTH – 正確的長度計算
UPPER / LOWER / LIKE / REGEXP – 多樣的比對方式
TRIM / REPLACE / INSERT – 高效的字串清理與取代
正規表達式 – 強大的模式匹配

結合 最佳實踐（避免 NULL、使用適當索引、統一編碼）與 實務案例，你將能在開發過程中快速解決常見的字串問題，提升系統的可讀性與效能。希望這篇文章能成為你在 MySQL 世界裡的實用手冊，未來面對更複雜的文字處理需求時，也能游刃有餘。祝你玩得開心、寫得順利！ 🚀