萬盛學電腦網

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

MySQL INSTR 函數實戰:字串定位、替代 LIKE 模糊查詢、效能優化技巧

MySQL INSTR(str, substr) 回傳 substr 在 str 中首次出現的位置 (1-based),找不到回傳 0。本文詳細解析語法、實戰用法、與 LIKE、LOCATE、FIND_IN_SET 的差異,並提供索引優化建議。

基本語法與範例

INSTR('www.111cn.net', '.')  → 4
INSTR('hello world', 'world')  → 7
INSTR('abc', 'd')              → 0
INSTR('abc', '')               → 1  -- 空字串視為位置 1

實戰場景

1. 替代 LIKE '%keyword%' 進行包含判斷

-- 等價邏輯
SELECT * FROM articles WHERE INSTR(title, 'MySQL') > 0;
SELECT * FROM articles WHERE title LIKE '%MySQL%';
  • INSTR 語意更清晰:明確表達「包含」而非「模式匹配」
  • 兩者效能相近,均無法使用索引 (前導萬用字元)

2. 字串截取輔助:取第一個分隔符前的內容

-- 取 '@' 前的用戶名部分
SELECT SUBSTRING_INDEX(email, '@', 1) 
FROM users;  -- 推薦用 SUBSTRING_INDEX

-- 或用 INSTR 定位再 SUBSTRING
SELECT SUBSTRING(email, 1, INSTR(email, '@') - 1) FROM users;

3. 統計某字元出現次數

-- 統計逗號數量 = 逗號分隔的欄位數 - 1
SELECT (LENGTH(csv_col) - LENGTH(REPLACE(csv_col, ',', ''))) AS comma_count
FROM import_log;

INSTR vs LOCATE vs FIND_IN_SET vs LIKE

效能優化建議

  • 避免全表掃描:INSTR、LIKE '%...%' 皆無法用索引
  • 前綴匹配用索引:WHERE col LIKE 'prefix%' 或 WHERE INSTR(col, 'prefix') = 1
  • 全文搜尋:大量文本包含查詢請用 FULLTEXT INDEX + MATCH() AGAINST()
  • 生成列索引:MySQL 8.0 可建立 INSTR(col, 'key') 生成列並索引

進階:大小寫敏感搜尋

-- 強制大小寫敏感
SELECT * FROM t WHERE INSTR(BINARY col, 'AbC') > 0;
-- 或指定 _bin 校對規則
SELECT * FROM t WHERE INSTR(col COLLATE utf8mb4_bin, 'AbC') > 0;
INSTR 是「定位」利器,LIKE 是「模式」利器。簡單包含判斷用 INSTR 可讀性更高;複雜模式、前綴匹配、全文搜尋請選對應工具。永遠記得:前導萬用字元 = 全表掃描。
copyright © 萬盛學電腦網 all rights reserved
函式語法回傳值支援萬用字元大小寫敏感典型用途
INSTRINSTR(haystack, needle)位置 (1-based)否依校對規則簡單包含判斷、定位
LOCATELOCATE(needle, haystack[, start])位置 (1-based)否依校對規則可指定起始位置搜尋
FIND_IN_SETFIND_IN_SET(str, strlist)位置 (1-based)否 (逗號分隔列表)依校對規則IN 列表排序、集合成員檢查
LIKEcol LIKE pattern布林是 (%, _)依校對規則模式匹配、前綴/後綴
REGEXPcol REGEXP pattern布林是 (正則)依校對規則複雜模式、驗證