MySQL 字串型數字排序與日期格式化查詢實戰
資料庫設計不當導致數字存為 VARCHAR、日期存為字串，查詢排序時出現「10 排在 2 前面」、「無法按月日篩選生日」等問題。本文提供解決方案與最佳實踐。
問題 1：VARCHAR 數字排序錯誤
-- 資料：'1', '2', '10', '20', '100'
SELECT `id` FROM `t` ORDER BY `id`; -- 字串排序：1, 10, 100, 2, 20 ❌
-- 解決 1：隱式轉換（利用數學運算強制轉數值）
SELECT `id` FROM `t` ORDER BY `id` + 0; -- 1, 2, 10, 20, 100 ✅
SELECT `id` FROM `t` ORDER BY CAST(`id` AS UNSIGNED); -- 標準寫法 ✅
SELECT `id` FROM `t` ORDER BY `id` * 1; -- 同理 ✅
-- 解決 2：左填充固定長度（不改動資料，純排序）
SELECT `id` FROM `t` ORDER BY LPAD(`id`, 10, '0'); -- 0000000001, 0000000002... ✅
問題 2：生日月日範圍查詢（忽略年份）
-- 需求：查詢 06-03 到 07-08 之間過生日的會員
-- birthday 欄位為 DATE 或 DATETIME
-- 解決：DATE_FORMAT 擷取月日比較
SELECT * FROM `member`
WHERE DATE_FORMAT(`birthday`, '%m-%d') BETWEEN '06-03' AND '07-08';
-- 注意：跨年範圍（如 12-20 到 01-10）需拆兩段
SELECT * FROM `member`
WHERE DATE_FORMAT(`birthday`, '%m-%d') >= '12-20'
OR DATE_FORMAT(`birthday`, '%m-%d') <= '01-10';
MySQL 日期時間函數速查表
| 函數 | 說明 | 範例 | 結果 |
|---|
DATE_FORMAT(date, fmt) | 自訂格式化 | DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i') | 2024-05-20 14:30 |
DAYOFWEEK(date) | 星期索引 (1=週日) | DAYOFWEEK('2024-05-20') | 2 |
WEEKDAY(date) | 星期索引 (0=週一) | WEEKDAY('2024-05-20') | 0 |
DAYOFMONTH(date) | 月份中的天 | DAYOFMONTH('2024-05-20') | 20 |
DAYOFYEAR(date) | 年中的天數 | DAYOFYEAR('2024-05-20') | 141 |
MONTH(date) | 月份 (1-12) | MONTH('2024-05-20') | 5 |
DAYNAME(date) | 星期名稱 | DAYNAME('2024-05-20') | Monday |
MONTHNAME(date) | 月份名稱 | MONTHNAME('2024-05-20') | May |
QUARTER(date) | 季度 (1-4) | QUARTER('2024-05-20') | 2 |
WEEK(date, mode) | 週數，mode 控制週起始日 | WEEK('2024-05-20', 1) | 21 |
YEAR(date) | 年份 | YEAR('2024-05-20') | 2024 |
HOUR(time) | 小時 (0-23) | HOUR('14:30:00') | 14 |
MINUTE(time) | 分鐘 | MINUTE('14:30:00') | 30 |
SECOND(time) | 秒 | SECOND('14:30:00') | 0 |
DATE_ADD(date, INTERVAL expr unit) | 日期加減 | DATE_ADD(NOW(), INTERVAL 1 MONTH) | 一個月後 |
DATEDIFF(date1, date2) | 天數差 | DATEDIFF('2024-12-31', NOW()) | 正整數 |
TO_DAYS(date) | 從 Year 0 起的天數 | TO_DAYS('2024-05-20') | 738987 |
FROM_DAYS(days) | 天數轉日期 | FROM_DAYS(738987) | 2024-05-20 |
UNIX_TIMESTAMP(date) | 轉 Unix 時間戳 | UNIX_TIMESTAMP('2024-05-20') | 1716172800 |
FROM_UNIXTIME(ts) | 時間戳轉日期 | FROM_UNIXTIME(1716172800) | 2024-05-20 00:00:00 |
SEC_TO_TIME(sec) | 秒數轉時分秒 | SEC_TO_TIME(3661) | 01:01:01 |
TIME_TO_SEC(time) | 時分秒轉秒數 | TIME_TO_SEC('01:01:01') | 3661 |
DATE_FORMAT 格式符號速查
| 符號 | 含義 | 範例 |
|---|
%Y | 4 位數年 | 2024 |
%y | 2 位數年 | 24 |
%m | 月份 (01-12) | 05 |
%c | 月份 (1-12) | 5 |
%d | 日期 (01-31) | 20 |
%e | 日期 (1-31) | 20 |
%H | 小時 24制 (00-23) | 14 |
%h | 小時 12制 (01-12) | 02 |
%i | 分鐘 (00-59) | 30 |
%s | 秒 (00-59) | 00 |
%W | 星期全名 | Monday |
%a | 星期縮寫 | Mon |
%M | 月份全名 | May |
%b | 月份縮寫 | May |
%D | 帶後綴的日期 | 20th |
%r | 12制時間 | 02:30:00 PM |
%T | 24制時間 | 14:30:00 |
效能提醒
WHERE DATE_FORMAT(col, ...) = ... **無法使用索引**，大表會全掃。- 優化：建立虛擬欄位/生成欄位（8.0+）
ALTER TABLE t ADD month_day VARCHAR(5) GENERATED ALWAYS AS (DATE_FORMAT(birthday, '%m-%d')) STORED, ADD INDEX idx_month_day (month_day);
- 或應用層預計算月日存入獨立欄位
birth_month_day CHAR(5)。
