您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL SELECT 語句 10 大實用技巧：從基礎查詢到多表關聯的高效寫法

編輯：MySQL 資料庫相關知識

技巧 1：SQL 格式化 —— 可讀性即生產力


-- 壓縮寫法 (難維護)
SELECT id,name,email FROM users WHERE status=1 AND created_at>'2023-01-01' ORDER BY id DESC LIMIT 20;

-- 結構化寫法 (推薦)
SELECT 
    `id`, 
    `name`, 
    `email`
FROM `users`
WHERE `status` = 1
  AND `created_at` > '2023-01-01'
ORDER BY `id` DESC
LIMIT 20;

關鍵字大寫、列名反引號、子句換行、條件對齊
團隊統一風格，配合 sql-formatter / IDE 插件自動格式化

技巧 2：IN 替代多個 OR —— 批量值查詢


-- ❌ 多個 OR
SELECT * FROM articles WHERE category_id = 1 OR category_id = 3 OR category_id = 5;

-- ✅ IN 列表 (優化器可優化為 range 訪問)
SELECT * FROM articles WHERE category_id IN (1, 3, 5);

-- ✅ 子查詢 (動態值)
SELECT * FROM articles WHERE category_id IN (SELECT id FROM categories WHERE parent_id = 0);

IN 列表常數會優化為二分查找，大列表 (＞1000) 建議用臨時表/關聯
避免 NOT IN 含 NULL 陷阱，改用 NOT EXISTS

技巧 3：CONCAT 拼接輸出 —— 減少應用層組裝


-- 單字段拼接
SELECT CONCAT(`id`, '-', `title`) AS `id_title` FROM articles WHERE id = 1;
-- 結果: "1-MySQL 優化指南"

-- 多字段構造 JSON 片段 (MySQL 5.7+ JSON_OBJECT 更好)
SELECT CONCAT('{"id":', `id`, ',"name":"', `name`, '"}') AS user_json FROM users LIMIT 1;

技巧 4：LOCATE / POSITION / INSTR —— 字符串搜索


-- 返回子串首次出現位置 (1-based)，不存在返回 0
SELECT LOCATE('hello', 'hello world');  -- 1
SELECT LOCATE('world', 'hello world');  -- 7
SELECT LOCATE('mysql', 'hello world');  -- 0

-- 應用：篩選包含關鍵詞的文章
SELECT * FROM articles WHERE LOCATE('MySQL', `content`) > 0;
-- 注意：不走索引，大表需全文索引

技巧 5：GROUP BY 分組聚合 —— 掌握聚合函數配合


-- 基礎：每城市用戶數
SELECT `city`, COUNT(*) AS user_count 
FROM `customers` 
GROUP BY `city`;

-- 進階：多維聚合
SELECT 
    `city`,
    COUNT(*) AS total,
    AVG(`age`) AS avg_age,
    MIN(`created_at`) AS first_user,
    MAX(`balance`) AS max_balance
FROM `customers`
GROUP BY `city`
HAVING COUNT(*) > 100;  -- 過濾聚合結果

GROUP BY 列必須出現在 SELECT 或聚合函數中 (ONLY_FULL_GROUP_BY 模式)
HAVING 針對聚合後結果過濾，WHERE 針對原始行

技巧 6：HAVING 過濾聚合組 —— 靈活篩選統計結果


-- 找出訂單金額超過 10000 的用戶
SELECT `user_id`, SUM(`amount`) AS total_amount
FROM `orders`
GROUP BY `user_id`
HAVING SUM(`amount`) > 10000;

-- 結合多條件
HAVING COUNT(*) > 10 AND AVG(`score`) >= 80

HAVING 可使用別名 (MySQL 擴展)：HAVING total_amount > 10000
性能提示：先 WHERE 過濾行，再 GROUP BY，最後 HAVING 過濾組

技巧 7：子句執行順序 —— 理解邏輯流向


SELECT select_list          -- 5. 選擇輸出列
FROM table                  -- 1. 確定數據源
WHERE condition             -- 2. 行級過濾
GROUP BY group_cols         -- 3. 分組
HAVING group_condition      -- 4. 組級過濾
ORDER BY order_cols         -- 6. 排序
LIMIT offset, count;        -- 7. 限制輸出

編寫順序 ≠ 執行順序，理解順序助於優化 (如 WHERE 先於 GROUP BY 減少參與聚合行數)

技巧 8：DISTINCT 去重 —— 單列/多列去重


-- 單列去重
SELECT DISTINCT `city` FROM `customers` ORDER BY `id` DESC;

-- 多列去重 (組合唯一)
SELECT DISTINCT `city`, `province` FROM `customers`;

-- 計數去重
SELECT COUNT(DISTINCT `city`) FROM `customers`;

DISTINCT 作用於**所有 SELECT 列的組合**，非單列
大數據集 DISTINCT 需排序/哈希去重，消耗大，考慮近似算法 (HyperLogLog)

技巧 9：LIMIT 分頁與截取 —— 高效取子集


-- 前 10 條
SELECT * FROM articles LIMIT 10;

-- 第 3 頁 (每頁 10 條)
SELECT * FROM articles LIMIT 20, 10;  -- 偏移 20，取 10
-- 或標準寫法
SELECT * FROM articles LIMIT 10 OFFSET 20;

-- 取第 100 條之後所有 (大偏移慎用)
SELECT * FROM articles LIMIT 100, 18446744073709551615;  -- MAX ULLONG
-- 更好：游標分頁 WHERE id > last_id LIMIT 10

大偏移 LIMIT 1000000, 10 需掃描棄取 100 萬行，**極慢**
**游標分頁 (Keyset Pagination)**：WHERE id > last_seen_id ORDER BY id LIMIT 10，利用索引定位，性能恆定

技巧 10：多表關聯查詢 —— 別名、ON 條件、索引匹配


SELECT 
    u.`user_name`,
    o.`order_no`,
    o.`amount`
FROM `users` u
INNER JOIN `orders` o 
    ON u.`id` = o.`user_id`  -- 關聯條件
WHERE o.`created_at` > '2023-01-01'
ORDER BY u.`id` DESC;

必須指定表別名：列名衝突時消歧，簡化寫法
ON vs WHERE：ON 為關聯條件，WHERE 為業務過濾；LEFT JOIN 時區別巨大
索引匹配：關聯列 (user_id) 必須有索引，否則 Hash Join / Block Nested Loop 慢
選擇 JOIN 類型：INNER (交集)、LEFT (左表全保留)、RIGHT (右表全保留)

進階組合：CTE 簡化複雜查詢 (MySQL 8.0+)


WITH `active_users` AS (
    SELECT `id`, `name` FROM `users` WHERE `status` = 1
),
`recent_orders` AS (
    SELECT `user_id`, `order_no`, `amount` 
    FROM `orders` 
    WHERE `created_at` > DATE_SUB(NOW(), INTERVAL 30 DAY)
)
SELECT 
    u.`name`,
    o.`order_no`,
    o.`amount`
FROM `active_users` u
JOIN `recent_orders` o ON u.`id` = o.`user_id`;

CTE 可見性僅當前語句，自動清理，無臨時表殘留
優化器可選擇物化或內聯，比手工臨時表更智能

總結

10 大 SELECT 技巧核心：**規範格式化、IN 批量查詢、CONCAT 字符串構造、LOCATE 文本搜索、GROUP BY/HAVING 聚合分析、理解執行順序、DISTINCT 去重、LIMIT 分頁優化 (游標分頁)、多表關聯別名索引、CTE 邏輯解耦**。熟練組合這些模式，能解決 90% 業務查詢場景，寫出可讀、高效、可維護的 SQL。