實用 MySQL 技巧六則：年齡計算、時間差、去重、工作日、主鍵查詢與庫大小統計

1. 以生日精準計算年齡

SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW()) - TO_DAYS(@dateofbirth)), '%Y') + 0 AS age;

原理：日期轉天數相減 → 差值天數轉日期 → 取年份部分 → 隱性轉型為數值。比 YEAR(NOW()) - YEAR(birth) 精準（會自動處理生日未到的情況）。

2. 兩個 DATETIME 差值換算

SET @dt1 = '2024-01-01 08:00:00', @dt2 = '2024-01-03 14:30:00';
SELECT 
  UNIX_TIMESTAMP(@dt2) - UNIX_TIMESTAMP(@dt1) AS diff_seconds,
  (UNIX_TIMESTAMP(@dt2) - UNIX_TIMESTAMP(@dt1)) / 60 AS diff_minutes,
  (UNIX_TIMESTAMP(@dt2) - UNIX_TIMESTAMP(@dt1)) / 3600 AS diff_hours,
  (UNIX_TIMESTAMP(@dt2) - UNIX_TIMESTAMP(@dt1)) / 86400 AS diff_days;

也可用 TIMESTAMPDIFF(SECOND/MINUTE/HOUR/DAY, @dt1, @dt2) 更直觀。

3. 查詢出現恰好 N 次的值

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

延伸：HAVING COUNT(*) > 1 找重複值；HAVING COUNT(*) = 1 找唯一值。

4. 計算兩日期間的工作日（排除週末與假日）

SELECT COUNT(*) 
FROM calendar 
WHERE d BETWEEN @start AND @stop 
  AND DAYOFWEEK(d) NOT IN (1,7)  -- 1=Sun, 7=Sat
  AND holiday = 0;

前提：擁有 calendar 維度表（每天一列，含 holiday 標記）。若無維度表，可用遞迴 CTE（MySQL 8.0+）動態生成日期序列。

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

適用於自動化腳本、ORM 框架反射主鍵、Schema 比對工具。

6. 統計各資料庫大小與剩餘空間（MB）

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

data_free 代表 InnoDB 表空間內已分配但未使用的空間（可被後續寫入復用），非檔案系統剩餘空間。

小結

這六條語句覆蓋日期運算、聚合去重、行事曆維度、資料字典查詢、容量規劃五大高頻場景，建議收錄至團隊 SQL 片段庫，提升開發與運維效率。