MySQL & MariaDB的世界

MySQL SUBSTRING 函數完整教學：語法、範例與最佳實務

函數
目次

1. SUBSTRING 函數是什麼？

SUBSTRING 函數是 MySQL 中用來從字串中擷取子字串的重要函數。使用這個函數可以從資料庫中的資料中，只取出所需的部分內容。舉例來說，可以用來從使用者的電子郵件地址中擷取網域部分，或從商品代碼中取得特定的段落，十分方便。

1.1 基本語法

SUBSTRING 函數的基本語法如下：

SUBSTRING(str, pos)
SUBSTRING(str, pos, len)
  • str：要擷取的原始字串。
  • pos：擷取開始位置（從 1 開始計算）。
  • len：要擷取的字元數（可選）。

當 pos 為正數時，會從字串開頭往後計算到指定位置；為負數時，則會從字串末尾往前計算。如果省略 len，則會從指定位置擷取到字串的最後。

1.2 SUBSTRING 函數的用途

此函數常用於格式化字串資料或擷取特定部分，能有效提升資料庫的資料查詢與處理效率。

2. SUBSTRING 函數的基本用法

為了理解基本用法，我們先來看一個簡單範例。

2.1 擷取字串的一部分

以下查詢會從字串 “Hello, World!” 的第 3 個字元開始擷取 6 個字元。

SELECT SUBSTRING('Hello, World!', 3, 6);

結果是 "llo, W"。由於 pos 為 3，表示從第 3 個字元開始，len 為 6，因此會擷取 6 個字元。

2.2 省略字元數

如果省略 len，會從指定位置一直擷取到字串末尾。

SELECT SUBSTRING('Hello, World!', 8);

結果為 "World!"，表示從第 8 個字元擷取到最後。

2.3 負數位置指定

使用負數可以從字串末尾開始計算位置。

SELECT SUBSTRING('Hello, World!', -5);

此查詢會返回 "orld!"，即從末尾向前數第 5 個字元開始擷取。

3. SUBSTRING 函數的實務應用

SUBSTRING 函數在實際資料處理中非常常見，以下是幾個應用案例。

3.1 從電子郵件擷取網域

結合 SUBSTRING 與 LOCATE，可以從電子郵件地址中擷取網域部分。

SELECT email, SUBSTRING(email, LOCATE('@', email) + 1) AS domain FROM users;

此查詢會擷取 “@” 之後的所有字元，即網域名稱。

3.2 擷取商品代碼的一部分

以下範例會從商品代碼中擷取特定部分。

SELECT product_code, SUBSTRING(product_code, 5, 4) AS product_id FROM products;

這會從第 5 個字元開始擷取 4 個字元，並將其顯示為 product_id 欄位。

3.3 在子查詢中使用

搭配子查詢可以進行更複雜的資料擷取。

SELECT id, SUBSTRING(description, 1, 10) AS short_desc FROM (SELECT * FROM products WHERE category = 'Electronics') AS sub;

此查詢會從分類為 ‘Electronics’ 的商品中，擷取 description 欄位的前 10 個字元。

4. 與其他字串操作函數的比較

除了 SUBSTRING，還有其他功能類似的函數，例如 LEFT、RIGHT、SUBSTR 等。

4.1 LEFT 與 RIGHT 函數

  • LEFT(str, len)：從字串開頭擷取指定長度的內容。
  • RIGHT(str, len)：從字串末尾擷取指定長度的內容。
SELECT LEFT('Hello, World!', 5);  -- "Hello"
SELECT RIGHT('Hello, World!', 6); -- "World!"

這些函數在擷取特定位數的字串時非常有用。

4.2 SUBSTR 函數

SUBSTR 是 SUBSTRING 的別名，功能完全相同。

SELECT SUBSTR('Hello, World!', 8); -- "World!"

此查詢與 SUBSTRING 的結果相同，會返回 "World!"。

5. SUBSTRING 函數的進階應用與最佳化

接下來說明一些進階用法與最佳化技巧。

5.1 效能最佳化

在大型資料集上使用 SUBSTRING 可能影響查詢效能，因此建議必要時建立索引，並檢視查詢計劃。如果經常擷取相同的子字串，可考慮將結果快取。

5.2 在 WHERE 子句中使用

將 SUBSTRING 用於 WHERE 子句，可以根據子字串進行條件篩選。

SELECT * FROM products WHERE SUBSTRING(product_code, 1, 3) = 'ABC';

此查詢會找出 product_code 開頭 3 個字元為 ‘ABC’ 的商品。

6. SUBSTRING 函數的案例與最佳實務

以下是實務上 SUBSTRING 函數的應用案例與最佳實務。

6.1 範例程式碼

此範例將客戶姓名中的姓與名分開。

SELECT name, SUBSTRING(name, 1, LOCATE(' ', name) - 1) AS first_name,
       SUBSTRING(name, LOCATE(' ', name) + 1) AS last_name
FROM customers;

此查詢會以空格為分隔符，擷取全名中的姓與名。

6.2 最佳實務

  • 最小化擷取範圍：只擷取必要的子字串，減少效能影響。
  • 注意資料型態：對數值資料使用 SUBSTRING 前，請先轉為字串。
  • 索引使用：在 WHERE 子句中使用 SUBSTRING 可能導致索引失效，需注意查詢效能。

7. 錯誤處理與版本差異

最後來看看使用 SUBSTRING 時的錯誤處理與 MySQL 版本差異。

7.1 錯誤處理

當指定的位置超出字串範圍時，SUBSTRING 會返回空字串而非錯誤，因此建議在查詢前加入檢查邏輯。

7.2 版本差異

不同版本的 MySQL 對 SUBSTRING 的處理可能略有差異，例如部分舊版本在處理多位元組字元時會有不同的行為。建議確認版本間的相容性，並在需要時採取相應措施。