函數語法對照

函數	語法	標準	說明
LOCATE	LOCATE(substr, str[, pos])	MySQL 擴展	支援指定起始位置
POSITION	POSITION(substr IN str)	SQL 標準	不支援起始位置
INSTR	INSTR(str, substr)	MySQL 擴展	參數順序相反，無起始位置

基礎用法

-- 返回首次出現位置 (1-based)，未找到返回 0
SELECT LOCATE('bar', 'foobarbar');      -- 4
SELECT LOCATE('xbar', 'foobar');        -- 0
SELECT POSITION('bar' IN 'foobarbar');  -- 4

-- 指定起始位置 (LOCATE 獨有)
SELECT LOCATE('bar', 'foobarbar', 5);   -- 7 (從第 5 字元後找)
SELECT LOCATE('bar', 'foobarbar', 8);   -- 0

大小寫敏感度

• 非二進制字串：不區分大小寫 (依排序規則，如 utf8mb4_general_ci)
• 二進制字串或任一參數為 BINARY：區分大小寫

  SELECT LOCATE('BAR', 'foobarbar');           -- 4 (不區分)
  SELECT LOCATE(BINARY 'BAR', 'foobarbar');    -- 0 (區分)
  SELECT LOCATE('BAR', BINARY 'foobarbar');    -- 0

實戰案例

1. 判斷字串是否包含關鍵字

SELECT * FROM articles 
WHERE LOCATE('MySQL', title) > 0;
-- 等價於 title LIKE '%MySQL%'，但 LOCATE 可用於動態關鍵字

2. 截取關鍵字前/後內容

-- 取 'error:' 之後的訊息
SELECT 
    log_line,
    SUBSTRING(log_line, LOCATE('error:', log_line) + 6) AS error_msg
FROM error_logs
WHERE LOCATE('error:', log_line) > 0;

3. 分隔符第 N 次出現位置

-- 取第 3 個 '/' 位置 (URL 路徑分段)
SELECT LOCATE('/', url, LOCATE('/', url, LOCATE('/', url) + 1) + 1) AS pos3
FROM urls;

4. 模糊匹配 FID (原文範例優化)

-- 原: POSITION(CONCAT(substr('00000',1,5-LENGTH(info.fid)),info.fid) IN a.fid) <> 0
-- 優化: 直接用 LIKE 或 FIND_IN_SET
SELECT info.* 
FROM info 
LEFT JOIN attribute a 
  ON a.fid LIKE CONCAT('%', LPAD(info.fid, 5, '0'), '%');
-- 或標準化 fid 寬度後用 =

字符串截取函數家族總覽

效能建議

• LOCATE/POSITION/INSTR 在 WHERE 中使用**不走索引** (除非功能性索引 8.0+)
• 大表模糊匹配優先考慮 全文索引 (FULLTEXT) 或 Elasticsearch
• 固定前綴查詢用 LIKE 'prefix%' 可走索引
• 頻繁截取解析的欄位建議**冗餘存儲解析結果**或**虛擬欄位**

函數	用途	範例
LEFT(str, len)	左側定長	LEFT('abcdef', 3) → 'abc'
RIGHT(str, len)	右側定長	RIGHT('abcdef', 3) → 'def'
SUBSTRING(str, pos[, len])	通用截取 (支援負位置)	SUBSTRING('abcdef', 3, 2) → 'cd'
SUBSTRING_INDEX(str, delim, count)	按分隔符截取	SUBSTRING_INDEX('a.b.c', '.', 2) → 'a.b'
MID(str, pos, len)	SUBSTRING 別名	同上
SUBSTR(str, pos, len)	SUBSTRING 別名	同上