常用 MySQL 实用 SQL 语句合集：年龄计算、时间差、重复值、主键查找、库大小统计

開發運維高頻用到的 6 條實用 SQL，涵蓋業務計算、數據質量檢查、元數據查詢，可直接複製適配表名使用。

1. 根據生日精確計算年齡

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

原理：天數差 → 日期 → 取年份 → 隱式轉數值。比 TIMESTAMPDIFF(YEAR, birth_date, NOW()) 更直觀。

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;

-- 或用原生函數（推薦）
SELECT TIMESTAMPDIFF(SECOND, dt1, dt2) AS sec,
       TIMESTAMPDIFF(MINUTE, dt1, dt2) AS min,
       TIMESTAMPDIFF(HOUR, dt1, dt2) AS hour,
       TIMESTAMPDIFF(DAY, dt1, dt2) AS day;

3. 查詢某列值出現恰好 N 次的記錄

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

應用：發現重複手機號、身份證、訂單號等業務唯一鍵異常。

4. 計算兩日期間的工作日天數（排除周末與假日表）

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

需維護一張 calendar(d DATE PRIMARY KEY, holiday TINYINT) 表，節假日按政府公告更新。

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';

適用於動態構建 CRUD、同步工具、ORM 掃描元數據場景。

6. 統計每個庫佔用磁盤大小（含數據+索引+碎片）

SELECT 
    table_schema AS `Db Name`,
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS `Db Size (MB)`,
    ROUND(SUM(data_free) / 1024 / 1024, 2) AS `Free Space (MB)`
FROM information_schema.tables
GROUP BY table_schema
ORDER BY `Db Size (MB)` DESC;

用於容量規劃、碎片整理優先級排序、存儲成本核算。

---

💡 收藏建議：將上述 SQL 保存為代碼片段，IDE 綁定快捷鍵，需要時秒級調用。