1. 計算年齡

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

2. 兩日期差值（秒/分/時/天）

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

-- 天數差
SELECT DATEDIFF(dt2, dt1) 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)  -- 排除週末
  AND holiday = 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 = '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;

使用提醒

• information_schema 查詢在大量表時較慢，建議快取結果
• 日期運算優先用 DATE_ADD/SUB、DATEDIFF，避免字串轉換
• 年齡計算若需精確到月日，建議應用層處理