萬盛學電腦網

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

MySQL WHERE 子句進階:條件構建、LIKE 模糊匹配、IN 集合查詢與子查詢實戰

WHERE 子句核心語法

SELECT select_list
FROM table_references
WHERE search_condition
[ORDER BY ...] [LIMIT ...];

search_condition 由一個或多個條件通過 AND、OR、NOT 組合,優先級:NOT > AND > OR,建議用括號明確分組。

比較運算符速查

運算符含義示例 (A=10, B=20)
=等於(A = B) → false
!= / <>不等於(A != B) → true
>大於(A > B) → false
<小於(A < B) → true
>=大於等於(A >= B) → false
<=小於等於(A <= B) → true
IS NULL為 NULLcol IS NULL
IS NOT NULL不為 NULLcol IS NOT NULL
BETWEEN ... AND ...閉區間範圍price BETWEEN 100 AND 500
IN (val1, val2, ...)集合成員cat_id IN (4, 11)
NOT IN非集合成員cat_id NOT IN (3, 11)

LIKE 模糊匹配

通配符

  • %:匹配任意長度字符(含 0 個)
  • _:匹配**單個**字符

實戰示例

-- 以「諾基亞」開頭
WHERE goods_name LIKE '諾基亞%'

-- 名稱為「諾基亞Nxx」(N 後兩位任意字符)
WHERE goods_name LIKE '諾基亞N__'

-- 包含「充電器」
WHERE goods_name LIKE '%充電器%'

-- 區分大小寫匹配(二進制比較)
WHERE BINARY tutorial_author = 'sanjay'  -- 不匹配 'Sanjay'
WHERE tutorial_author COLLATE utf8mb4_bin = 'sanjay'  -- 同效果,推薦

性能關鍵:索引利用

模式能否用索引說明
LIKE 'prefix%'✅ 是前綴匹配,B+Tree 範圍掃描
LIKE '%suffix'❌ 否前導通配符,全表掃描
LIKE '%middle%'❌ 否雙邊通配符,全表掃描
LIKE '_exact'❌ 否單字符通配符同樣阻礙索引

優化策略: 全文搜索需求請用 Fulltext Index (MyISAM/InnoDB 5.6+) 或 Elasticsearch;前綴查詢確保列有索引且無函數包裹。

IN / NOT IN 集合查詢

三種常見形式

-- 1. 靜態列表
WHERE status IN ('active', 'pending', 'review')

-- 2. 子查詢返回單列
WHERE user_id IN (SELECT user_id FROM vip_users WHERE expire_at > NOW())

-- 3. 應用層構建(防注入)
$ids = [4,2,3];
$placeholders = implode(',', array_fill(0, count($ids), '?'));
$sql = "SELECT * FROM test WHERE id IN ($placeholders) ORDER BY FIELD(id, " . implode(',', $ids) . ")";
$stmt = $mysqli->prepare($sql);
$stmt->bind_param(str_repeat('i', count($ids)), ...$ids);

IN vs EXISTS 性能選擇

  • 外表小、內表大、內表有索引 → IN / JOIN 通常更快。
  • 外表大、內表小 → EXISTS 往往更優(可短路)。
  • MySQL 8.0+ 優化器會自動將 IN (subquery) 轉為 SEMI JOIN,性能差異縮小。

子查詢分類與實戰

1. WHERE 型(標量/列/行子查詢)

-- 標量子查詢:返回單值
SELECT * FROM goods WHERE shop_price > (SELECT AVG(shop_price) FROM goods);

-- 列子查詢:返回單列多行,配合 IN / ANY / ALL
SELECT * FROM goods WHERE cat_id IN (SELECT cat_id FROM category WHERE parent_id = 0);

-- 行子查詢:返回一行多列
SELECT * FROM t1 WHERE (col1, col2) = (SELECT col1, col2 FROM t2 WHERE id = 1);

2. FROM 型(派生表 / 物化視圖)

-- 每個分類最新商品(MySQL 8.0+ 推薦窗口函數 ROW_NUMBER())
SELECT * FROM (
    SELECT g.*, ROW_NUMBER() OVER (PARTITION BY cat_id ORDER BY goods_id DESC) AS rn
    FROM ecs_goods g
) t WHERE rn = 1;

-- 兼容舊版:派生表 + GROUP BY 技巧
SELECT * FROM (
    SELECT goods_id, cat_id, goods_name FROM ecs_goods ORDER BY goods_id DESC
) AS t GROUP BY cat_id;

3. 相關子查詢(逐行執行,慎用)

-- 查詢每個分類價格高於該分類平均價的商品
SELECT * FROM ecs_goods g1
WHERE shop_price > (
    SELECT AVG(shop_price) FROM ecs_goods g2 WHERE g2.cat_id = g1.cat_id
);
優化: 相關子查詢可改寫為 JOIN + GROUP BY 或窗口函數,避免逐行執行的 N+1 問題。

綜合實戰:ECSHOP 商品多條件篩選

SELECT g.goods_id, g.goods_name, g.shop_price, g.click_count, c.cat_name
FROM ecs_goods g
LEFT JOIN ecs_category c ON g.cat_id = c.cat_id
WHERE g.cat_id = 3                                    -- 指定分類
  AND (g.shop_price < 1000 OR g.shop_price > 3000)    -- 價格區間 OR
  AND g.click_count > 5                               -- 點擊量閾值
  AND g.goods_name LIKE '諾基亞%'                      -- 品牌前綴
ORDER BY g.shop_price DESC
LIMIT 20;

總結

  1. WHERE 條件順序:高選擇性(過濾率高)條件前置,讓優化器更早縮小結果集。
  2. 索引友好:避免列上用函數/運算、避免前導 %、善用複合索引最左前綴。
  3. IN 列表長度:超過 1000 個值考慮臨時表/JOIN;NOT IN 含 NULL 會返回空集,改用 NOT EXISTS。
  4. 子查詢改寫:8.0+ 優先用 CTE (WITH) 和窗口函數,提升可讀性與性能。
copyright © 萬盛學電腦網 all rights reserved