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

1. 生日計算年齡

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

原理：日期差轉天數 → FROM_DAYS 得年份日期 → 取年部分 → 隱式轉數值。

2. 兩 datetime 時間差

SET @dt1 = '2023-01-01 10:00:00', @dt2 = '2023-01-02 12: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;

3. 查詢出現 N 次的值

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

4. 計算兩日期間工作日（排除周末、假期）

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

需預建 calendar 表含全量日期、holiday 標記。

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 = 'db_name'
  AND t.table_name = 'tbl_name';

6. 查看各庫佔用空間

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;