SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW()) - TO_DAYS(birth_date)), '%Y') + 0 AS age
FROM users;
原理:日期差轉天數 → FROM_DAYS 得到年月日 → 取年部分 → 隱式轉數字。
-- 秒差
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 id
FROM tbl
GROUP BY id
HAVING COUNT(*) = N;
-- 需預建日歷表 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;
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';
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