萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

6 條高頻實用 MySQL SQL 技巧:年齡計算、時間差、分組統計、工作日、主鍵查詢、庫大小監控

1. 通過生日精確計算年齡

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

原理:日期差轉天數 → FROM_DAYS 得到年月日 → 取年部分 → 隱式轉數字。

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;

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


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

4. 計算兩日期間的工作日(排除周末/節假日)


-- 需預建日歷表 calendar(d DATE PRIMARY KEY, holiday TINYINT)
SELECT COUNT(*) AS workdays
FROM calendar
WHERE d BETWEEN @start AND @end
  AND DAYOFWEEK(d) NOT IN (1,7)  -- 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 = 'your_db'
  AND t.TABLE_NAME = 'your_table';

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;

適用版本與性能提示

  • MySQL 5.0+ 全支持 information_schema
  • 第 6 句在表極多時較慢,可緩存結果或按庫分批查詢
  • 工作日計算依賴維護良好的日歷表,節假日需人工/腳本更新
copyright © 萬盛學電腦網 all rights reserved