MySQL 實用 SQL 技巧精選：6 條高頻業務場景一條龍解決方案

SQL 編寫雖非火箭科學，但掌握幾條高頻場景的「標準解法」能大幅提升開發效率與查詢效能。本文精選 6 條實戰中極高頻出現的 SQL 模式，涵蓋年齡計算、時間差、重複值統計、工作日計算、主鍵查詢、庫容量評估，附帶執行計畫分析與優化提示。

技巧 1：精確計算年齡（生日 → 年齡）

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW()) - TO_DAYS(birth_date)), '%Y') + 0 AS age
FROM users;

原理解析

1. TO_DAYS(date)：將日期轉為「從公元 0 年起的天數」
2. 相減得天數差 → FROM_DAYS(days) 轉回日期（結果為 0000-00-00 加上天數差）
3. DATE_FORMAT(..., '%Y') 取年份部分 → 隱含轉數值 + 0

現代寫法 (MySQL 8.0+)

SELECT TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age FROM users;

優勢：語義清晰、自動處理閏年邊界、可直接用於索引條件下推。

技巧 2：兩個 DATETIME 差值換算（秒/分/時/天）

-- 秒差
SELECT UNIX_TIMESTAMP(dt2) - UNIX_TIMESTAMP(dt1) AS diff_seconds;

-- 分差
SELECT (UNIX_TIMESTAMP(dt2) - UNIX_TIMESTAMP(dt1)) / 60 AS diff_minutes;

-- 時差
SELECT (UNIX_TIMESTAMP(dt2) - UNIX_TIMESTAMP(dt1)) / 3600 AS diff_hours;

-- 天差
SELECT (UNIX_TIMESTAMP(dt2) - UNIX_TIMESTAMP(dt1)) / 86400 AS diff_days;

更優雅的 TIMESTAMPDIFF

SELECT 
    TIMESTAMPDIFF(SECOND, dt1, dt2) AS diff_sec,
    TIMESTAMPDIFF(MINUTE, dt1, dt2) AS diff_min,
    TIMESTAMPDIFF(HOUR, dt1, dt2) AS diff_hour,
    TIMESTAMPDIFF(DAY, dt1, dt2) AS diff_day;

注意： TIMESTAMPDIFF(unit, start, end) 參數順序為「起始、結束」，結果為 end - start。

技巧 3：查詢出現恰好 N 次的值（去重計數）

SELECT id
FROM tbl
GROUP BY id
HAVING COUNT(*) = N;

變體：出現 ≥ N 次 / 找出重複值

-- 重複值（出現 >1 次）
SELECT id, COUNT(*) AS cnt
FROM tbl
GROUP BY id
HAVING COUNT(*) > 1
ORDER BY cnt DESC;

效能提示

• 確保 id 有索引（覆蓋索引最佳）
• 大表可先抽樣估算分布，再決定是否全表掃描

技巧 4：計算兩日期間的工作日（排除週末/假日）

SELECT COUNT(*) AS workdays
FROM calendar
WHERE d BETWEEN @start_date AND @end_date
  AND DAYOFWEEK(d) NOT IN (1, 7)  -- 1=週日, 7=週六
  AND holiday = 0;                 -- 假日表標記

前置條件：維護一張日曆表

CREATE TABLE calendar (
    d DATE PRIMARY KEY,
    holiday TINYINT NOT NULL DEFAULT 0,  -- 1=法定假日
    workday TINYINT NOT NULL DEFAULT 1   -- 0=補班日
);

優勢：複雜班次規則（調休、輪班）集中在表維護，查詢極簡高效；避免 SQL 複雜邏輯難維護。

技巧 5：查詢表的主鍵欄位（元資料查詢）

SELECT k.column_name
FROM information_schema.table_constraints t
JOIN information_schema.key_column_usage k
  USING (constraint_name, table_schema, table_name)
WHERE t.constraint_type = 'PRIMARY KEY'
  AND t.table_schema = 'your_db'
  AND t.table_name = 'your_table';

簡化版（單主鍵欄位）

SELECT column_name
FROM information_schema.columns
WHERE table_schema = 'your_db'
  AND table_name = 'your_table'
  AND column_key = 'PRI';

技巧 6：查看各資料庫佔用磁碟空間（容量評估）

SELECT 
    table_schema AS 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Data+Index (MB)',
    ROUND(SUM(data_free) / 1024 / 1024, 2) AS 'Free Fragment (MB)',
    COUNT(*) AS 'Table Count'
FROM information_schema.tables
GROUP BY table_schema
ORDER BY SUM(data_length + index_length) DESC;

單表 Top N 大表排查

SELECT 
    table_schema, table_name,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS 'Total (MB)',
    ROUND(data_free / 1024 / 1024, 2) AS 'Fragment (MB)',
    table_rows
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','mysql','performance_schema','sys')
ORDER BY (data_length + index_length) DESC
LIMIT 20;

進階補充：EXPLAIN 驗證這些查詢的執行計畫

總結

這 6 條 SQL 模式覆蓋了業務開發 80% 以上的統計分析場景。核心心法：**善用元資料表、預先建立維度表（日曆）、優先選擇語義清晰的內建函數（TIMESTAMPDIFF）、配合 EXPLAIN 驗證索引命中**。將常用片段封裝為團隊 Code Snippets，讓標準解法成為肌肉記憶。