萬盛學電腦網

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

SQL LIKE 模糊查詢完整教學:萬用字元、轉義、索引優化與效能陷阱

LIKE 基礎語法

SELECT column_list
FROM table_name
WHERE column_name LIKE pattern
[ESCAPE escape_character];

萬用字元詳細說明

符號名稱匹配範例
%百分號任意長度字串 (含 0 字元)'abc' LIKE 'a%' ✓, 'a' LIKE 'a%' ✓
_底線單一字元'abc' LIKE 'a_c' ✓, 'ab' LIKE 'a_' ✗
[範圍]字元類別 (部分資料庫)單一字元在集合中MySQL 不支援,用 REGEXP

常見模式範例 (以 Persons 表 City 欄位為例)

模式說明匹配範例
'Taipei%'以 Taipei 開頭Taipei, Taipei City, Taipei101
'%wan'以 wan 結尾Taiwan, Kaohsiungwan
'%bei%'包含 beiBeijing, Taipei, Bei
'_aipei'第 1 字任意,後接 aipeiTaipei, Kaipai
'T_ipei'第 2 字任意Taipei, Txipei
'%'所有非 NULL 值所有城市

NOT LIKE 排除模式

-- 不包含 'bei' 的城市
SELECT * FROM Persons WHERE City NOT LIKE '%bei%';

-- 等同於
SELECT * FROM Persons WHERE NOT (City LIKE '%bei%');

轉義字元處理特殊符號

查詢資料本身包含 % 或 _ 時:

-- 使用 ESCAPE 指定轉義字元 (預設為 \)
SELECT * FROM products WHERE code LIKE '100\%%' ESCAPE '\';  -- 查詢以 100% 開頭
SELECT * FROM products WHERE code LIKE '100#%' ESCAPE '#';  -- 用 # 轉義

-- MySQL 預設 \ 為轉義字元
WHERE col LIKE '%\%%' ESCAPE '\'  -- 包含 % 字元
WHERE col LIKE '%\_%' ESCAPE '\'  -- 包含 _ 字元

LIKE 索引使用鐵律 (關鍵效能知識)

✅ 可使用索引 (最左前綴原則)

WHERE name LIKE 'John%'      -- 固定前綴
WHERE name LIKE 'John'       -- 等值查詢
WHERE name LIKE 'J%'         -- 單字元前綴

❌ 無法使用索引 (全表掃描)

WHERE name LIKE '%John'      -- 前導萬用字元
WHERE name LIKE '%John%'     -- 兩端萬用字元
WHERE name LIKE '_ohn'       -- 前導單字元萬用字元

驗證方法

EXPLAIN SELECT * FROM users WHERE name LIKE 'John%';
-- type=range, key=idx_name, rows=少

EXPLAIN SELECT * FROM users WHERE name LIKE '%John%';
-- type=ALL, key=NULL, rows=全表行數

效能優化方案 (前導萬用字元查詢)

方案一:全文索引 (FULLTEXT) - MyISAM/InnoDB 5.6+

ALTER TABLE articles ADD FULLTEXT INDEX ft_content (content);

-- 自然語言模式
SELECT * FROM articles WHERE MATCH(content) AGAINST('mysql tutorial');

-- 布林模式
SELECT * FROM articles WHERE MATCH(content) AGAINST('+mysql -tutorial' IN BOOLEAN MODE);

方案二:反向索引 / 儲存反轉字串

ALTER TABLE users ADD COLUMN name_reverse VARCHAR(100);
UPDATE users SET name_reverse = REVERSE(name);
CREATE INDEX idx_name_rev ON users(name_reverse);

-- 查詢以 'son' 結尾 → 查詢反轉字串以 'nos' 開頭
SELECT * FROM users WHERE name_reverse LIKE 'nos%';

方案三:外部搜尋引擎 (Elasticsearch / Meilisearch / Typesense)

專業全文搜尋、模糊匹配、高亮、分詞、同義詞、拼音支援。

方案四:應用層前綴匹配 (自動完成場景)

-- 前端輸入 "Tai" → 後端查詢
WHERE city LIKE 'Tai%'  -- 走索引,極快

字元集與大小寫敏感度

Collation 尾綴大小寫重音範例
_ci (case insensitive)不敏感依規則utf8mb4_general_ci, utf8mb4_unicode_ci
_cs (case sensitive)敏感依規則utf8mb4_bin, utf8mb4_0900_as_cs
_ai (accent insensitive)不敏感忽略重音utf8mb4_unicode_ci (預設 ai)
_as (accent sensitive)敏感區分重音utf8mb4_0900_as_cs

注意:utf8mb4_bin 二進制比較,區分大小寫且按字節碼值排序。

PHP 實作:安全的模糊搜尋

<?php
$mysqli = new mysqli('localhost', 'user', 'pass', 'db');
$mysqli->set_charset('utf8mb4');
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

$keyword = trim($_GET['q'] ?? '');
if ($keyword === '') {
    echo json_encode([]);
    exit;
}

// 1. 轉義 LIKE 特殊字元
$escaped = str_replace(
    ['%', '_', '\\'],  // 順序重要:先轉義 \
    ['\%', '\_', '\\\\'],
    $keyword
);

// 2. 建構模式 (前綴匹配可走索引)
$pattern = $escaped . '%';  // 或 '%' . $escaped . '%' (全文搜尋)

// 3. 預處理語句
$stmt = $mysqli->prepare("
    SELECT id, username, city 
    FROM members 
    WHERE city LIKE ? 
    ORDER BY city 
    LIMIT 20
");
$stmt->bind_param('s', $pattern);
$stmt->execute();
$result = $stmt->get_result();

$data = $result->fetch_all(MYSQLI_ASSOC);
echo json_encode($data, JSON_UNESCAPED_UNICODE);

$stmt->close();
$mysqli->close();
?>

總結

  1. LIKE 'prefix%' 走索引,LIKE '%suffix' 全表掃描
  2. 資料含 %/_ 時必須 ESCAPE 轉義
  3. 大表前導萬用字元查詢**必須**用全文索引或外部搜尋引擎
  4. 字元集決定大小寫敏感度,_ci 不敏感,_cs/_bin 敏感
  5. PHP 必須轉義使用者輸入的 %/_/\ 再拼接模式
copyright © 萬盛學電腦網 all rights reserved