MySQL 查詢進階：DUAL 虛擬表、LIMIT 分頁與子查詢限制繞過技巧

MySQL 查詢進階：DUAL、LIMIT 與子查詢限制

MySQL 在標準 SQL 基礎上有獨特的查詢語法與限制。掌握 DUAL、LIMIT、子查詢更新限制及繞過方案，能寫出更地道、高效的 MySQL 查詢。

1. DUAL 虛擬表

無需實表即可 SELECT 標量/函數結果，兼容 Oracle 習慣。

-- MSSQL: 直接 SELECT
SELECT 1, 'A';

-- Oracle: 必須 FROM DUAL
SELECT 1, 'A' FROM DUAL;

-- MySQL: 兩種皆支援
SELECT 1, 'A';
SELECT 1, 'A' FROM DUAL;

實際執行計劃中 DUAL 為常量表，零開銷。

2. LIMIT 分頁神器

替代 MSSQL TOP，語法更靈活、效率更高（無需全排序）。

-- 取前 1 行（優化器可提前終止）
SELECT * FROM usr LIMIT 1;

-- 偏移量 + 條數：跳過 0 行，取 2 行（即第 1-2 行）
SELECT * FROM usr LIMIT 0, 2;
-- 等價於 LIMIT 2 OFFSET 0

子查詢中不能直接用 LIMIT

-- 報錯：This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
SELECT * FROM usr 
WHERE u_id IN (SELECT u_id FROM usr WHERE dept='信息部' LIMIT 1);

繞過方案：套一層衍生表

SELECT * FROM usr 
WHERE u_id IN (
  SELECT * FROM (
    SELECT u_id FROM usr WHERE dept='信息部' LIMIT 1
  ) aa
);

原理：衍生表物質化後，外層視為常規表，繞過限制。

3. 更新語句同表子查詢限制

-- 報錯：You can't specify target table 'usr' for update in FROM clause
UPDATE usr SET usr_name='匿名' 
WHERE u_id IN (SELECT u_id FROM usr WHERE dept='信息部');

同樣套衍生表繞過

UPDATE usr SET usr_name='匿名' 
WHERE u_id IN (
  SELECT * FROM (
    SELECT u_id FROM usr WHERE dept='信息部'
  ) aa
);

4. LIMIT vs TOP 效能本質差異

5. 實戰最佳實踐

• 分頁查詢：WHERE id > last_id LIMIT 20（游標式）優於 LIMIT offset, 20
• 子查詢去重：優先用 EXISTS / JOIN 替代 IN (SELECT...)
• 衍生表繞過限制時，注意 SELECT * 可能帶來額外開銷，建議顯式列出所需列
• MySQL 8.0+ 支援 CTE (WITH)，可替代衍生表寫法，可讀性更強