MySQL SELECT 語法精通：從基礎查詢到進階選項完整指南

MySQL SELECT 語句完整語法與實戰技巧

SELECT 是 SQL 最核心、變化最多的指令。MySQL 在 ANSI SQL-92 基礎上擴展了多項實用選項。本文系統梳理完整語法、各子句用法與效能技巧。

完整語法骨架

SELECT
  [ALL | DISTINCT | DISTINCTROW]
  [HIGH_PRIORITY]
  [STRAIGHT_JOIN]
  [SQL_SMALL_RESULT | SQL_BIG_RESULT]
  [SQL_BUFFER_RESULT]
  [SQL_CACHE | SQL_NO_CACHE]
  [SQL_CALC_FOUND_ROWS]
  select_expr [, select_expr ...]
  [FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [WINDOW window_name AS (window_spec) ...]
    [ORDER BY {col_name | expr | position} [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [INTO OUTFILE 'file_name' [CHARACTER SET charset_name] export_options
       | INTO DUMPFILE 'file_name'
       | INTO var_name [, var_name] ...]
    [FOR UPDATE | LOCK IN SHARE MODE]
    [NOWAIT | SKIP LOCKED]]

核心選項詳細解析

去重選項

• ALL (預設)：保留所有列，含重複
• DISTINCT：移除完全重複的列，NULL 視為相等
• DISTINCTROW：同 DISTINCT，MySQL 延伸

優化器提示

進階子句

WITH ROLLUP：超級彙總

SELECT year, month, SUM(sales) FROM sales_data
GROUP BY year, month WITH ROLLUP;
-- 輸出：各月小計 + 各年小計 + 總計

WINDOW FUNCTIONS (8.0+)：分析函數

SELECT
  id,
  department,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

FOR UPDATE / LOCK IN SHARE MODE：鎖定讀

-- 悲觀鎖：排他鎖，阻塞其他寫入
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

-- 共享鎖：允許並發讀，阻塞寫入
SELECT * FROM accounts WHERE id = 1 LOCK IN SHARE MODE;

-- 8.0+ 非阻塞選項
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;      -- 立即失敗
SELECT * FROM accounts WHERE id = 1 FOR UPDATE SKIP LOCKED; -- 跳過已鎖列

INTO OUTFILE / DUMPFILE：匯出檔案

-- CSV 匯出 (伺服器端檔案，需 FILE 權限)
SELECT * FROM users INTO OUTFILE '/tmp/users.csv'
  FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

-- 單行二進位匯出
SELECT blob_col FROM files WHERE id = 1 INTO DUMPFILE '/tmp/out.bin';

實戰技巧集錦

1. 分頁優化 (大偏移量問題)

-- 慢：OFFSET 1000000 LIMIT 10 (掃描 100 萬列再丟棄)
-- 快：延遲關聯 / 書籤分頁
SELECT * FROM t WHERE id > 1000000 ORDER BY id LIMIT 10;
-- 或子查詢覆蓋索引
SELECT * FROM t WHERE id IN (SELECT id FROM t ORDER BY id LIMIT 1000000, 10);

2. 分組取每組前 N (Top-N per Group)

-- 8.0+ 窗口函數
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY category ORDER BY score DESC) AS rn
  FROM scores
) t WHERE rn <= 3;

3. 條件聚合 (單掃描多統計)

SELECT
  COUNT(*) AS total,
  SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_cnt,
  SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) AS refund_cnt,
  AVG(amount) AS avg_amt
FROM orders;

4. 避免 SELECT * 陷阱

• 增加網路傳輸、記憶體、磁碟 I/O
• 破壞覆蓋索引優化
• Schema 變更導致應用錯誤
• 永遠明確列出欄位

常見錯誤與修正

SELECT 語句看似簡單，實則變化無窮。掌握核心選項、進階子句與效能技巧，能寫出既正確又高效的查詢。