在MySQL開發(fā)中，用VARCHAR字段存儲日期是一個非常經(jīng)典的“反模式”——雖然從技術(shù)上可以實(shí)現(xiàn)，但會帶來性能下降、格式混亂、排序錯誤、數(shù)據(jù)無效等一系列問題。然而在現(xiàn)實(shí)場景中，很多老系統(tǒng)、歷史遺留項目依然在使用VARCHAR存儲日期，因此我們需要掌握正確的篩選方法，同時明確如何優(yōu)化和改造。

本文將從VARCHAR存日期的常見格式、正確篩選方法、核心風(fēng)險與問題、最佳實(shí)踐改造方案、實(shí)戰(zhàn)示例五個維度出發(fā)，全面講解在MySQL中使用VARCHAR字段進(jìn)行日期篩選的知識，幫你避開坑點(diǎn)，同時給出長期優(yōu)化方向。

前置認(rèn)知：為什么會用VARCHAR存日期？這是反模式！

在開始講解篩選方法之前，必須先明確一個核心結(jié)論：用VARCHAR存儲日期是一個不推薦的反模式，生產(chǎn)環(huán)境應(yīng)優(yōu)先使用DATE、DATETIME、TIMESTAMP等原生日期類型！

為什么會有VARCHAR存日期的情況？

• 歷史遺留系統(tǒng)：很多老系統(tǒng)在設(shè)計時，開發(fā)者對MySQL日期類型不熟悉，或者為了“靈活”選擇了VARCHAR；
• 格式兼容需求：部分業(yè)務(wù)需要存儲多種格式的日期字符串（雖然這本身就是問題）；
• 開發(fā)者認(rèn)知不足：部分開發(fā)者認(rèn)為“VARCHAR存字符串更簡單”，忽略了后續(xù)的性能和維護(hù)成本。

原生日期類型 vs VARCHAR存日期的核心對比

一、VARCHAR存日期的常見格式

VARCHAR存日期的格式五花八門，不同格式的篩選方法和性能差異極大，常見格式如下：

核心結(jié)論：如果必須用VARCHAR存日期，唯一正確的格式是’YYYY-MM-DD’或’YYYYMMDD’，且必須補(bǔ)零，只有這兩種格式的字符串排序和日期排序完全一致，篩選時可以直接用字符串比較，無需函數(shù)轉(zhuǎn)換。

二、VARCHAR存日期的正確篩選方法

根據(jù)VARCHAR存日期的格式不同，篩選方法分為兩類：標(biāo)準(zhǔn)格式直接字符串比較（推薦，性能好） 和 非標(biāo)準(zhǔn)格式用函數(shù)轉(zhuǎn)換（不推薦，性能差）。

前置準(zhǔn)備：統(tǒng)一的測試表

為了讓示例更清晰，我們先創(chuàng)建一張測試表，包含不同格式的VARCHAR日期字段：

CREATE TABLE varchar_date_demo (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    order_no VARCHAR(32) NOT NULL COMMENT '訂單號',
    -- 標(biāo)準(zhǔn)格式：YYYY-MM-DD，補(bǔ)零
    date_std VARCHAR(10) NOT NULL COMMENT '標(biāo)準(zhǔn)格式日期',
    -- 標(biāo)準(zhǔn)格式：YYYYMMDD，純數(shù)字
    date_num VARCHAR(8) NOT NULL COMMENT '純數(shù)字格式日期',
    -- 非標(biāo)準(zhǔn)格式：DD-MM-YYYY
    date_non_std VARCHAR(10) NOT NULL COMMENT '非標(biāo)準(zhǔn)格式日期',
    amount DECIMAL(10,2) NOT NULL COMMENT '訂單金額',
    INDEX idx_date_std (date_std),
    INDEX idx_date_num (date_num)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='VARCHAR日期測試表';

-- 插入測試數(shù)據(jù)
INSERT INTO varchar_date_demo (order_no, date_std, date_num, date_non_std, amount) VALUES
('ORD001', '2026-03-25', '20260325', '25-03-2026', 100.00),
('ORD002', '2026-03-26', '20260326', '26-03-2026', 200.00),
('ORD003', '2026-03-27', '20260327', '27-03-2026', 300.00),
('ORD004', '2026-03-28', '20260328', '28-03-2026', 400.00),
('ORD005', '2026-04-01', '20260401', '01-04-2026', 500.00);

2.1 標(biāo)準(zhǔn)格式（YYYY-MM-DD / YYYYMMDD）：直接字符串比較（推薦）

如果VARCHAR日期是標(biāo)準(zhǔn)格式且補(bǔ)零，字符串排序和日期排序完全一致，可以直接用字符串比較運(yùn)算符（=、>、<、>=、<=、BETWEEN、LIKE）篩選，無需函數(shù)轉(zhuǎn)換，索引生效，性能最好。

示例1：等值查詢（查詢某一天的數(shù)據(jù)）

-- 查詢2026-03-27的訂單（標(biāo)準(zhǔn)格式Y(jié)YYY-MM-DD）
SELECT * FROM varchar_date_demo WHERE date_std = '2026-03-27';

-- 查詢2026-03-27的訂單（純數(shù)字格式Y(jié)YYYMMDD）
SELECT * FROM varchar_date_demo WHERE date_num = '20260327';

示例2：范圍查詢（查詢某段時間的數(shù)據(jù)）

-- 查詢2026-03-25到2026-03-28的訂單（BETWEEN）
SELECT * FROM varchar_date_demo 
WHERE date_std BETWEEN '2026-03-25' AND '2026-03-28';

-- 查詢2026-03-27之后的訂單（>）
SELECT * FROM varchar_date_demo WHERE date_std > '2026-03-27';

-- 查詢2026年3月的訂單（>= AND <）
SELECT * FROM varchar_date_demo 
WHERE date_std >= '2026-03-01' AND date_std < '2026-04-01';

示例3：模糊查詢（查詢某月/某年的數(shù)據(jù)）

-- 查詢2026年3月的訂單（LIKE '2026-03%'）
SELECT * FROM varchar_date_demo WHERE date_std LIKE '2026-03%';

-- 查詢2026年的訂單（LIKE '2026%'）
SELECT * FROM varchar_date_demo WHERE date_std LIKE '2026%';

示例4：驗證索引生效（EXPLAIN）

-- 用EXPLAIN驗證標(biāo)準(zhǔn)格式篩選的索引生效情況
EXPLAIN SELECT * FROM varchar_date_demo 
WHERE date_std BETWEEN '2026-03-25' AND '2026-03-28';

EXPLAIN結(jié)果：

說明：type是range，key是idx_date_std，說明索引生效，性能優(yōu)秀。

2.2 非標(biāo)準(zhǔn)格式：用STR_TO_DATE函數(shù)轉(zhuǎn)換（不推薦，性能差）

如果VARCHAR日期是非標(biāo)準(zhǔn)格式（比如DD-MM-YYYY、YYYY/MM/DD、中文格式），字符串排序和日期排序不一致，無法直接用字符串比較，必須用STR_TO_DATE函數(shù)將VARCHAR轉(zhuǎn)換為DATE/DATETIME類型，再進(jìn)行篩選。

STR_TO_DATE函數(shù)語法

STR_TO_DATE(字符串, 格式模板)

常用格式模板：

示例1：非標(biāo)準(zhǔn)格式DD-MM-YYYY的篩選

-- 查詢2026-03-27的訂單（非標(biāo)準(zhǔn)格式DD-MM-YYYY）
SELECT * FROM varchar_date_demo 
WHERE STR_TO_DATE(date_non_std, '%d-%m-%Y') = '2026-03-27';

-- 查詢2026-03-25到2026-03-28的訂單
SELECT * FROM varchar_date_demo 
WHERE STR_TO_DATE(date_non_std, '%d-%m-%Y') BETWEEN '2026-03-25' AND '2026-03-28';

示例2：非標(biāo)準(zhǔn)格式Y(jié)YYY/MM/DD的篩選

-- 假設(shè)date_slash字段格式為'2026/03/27'
-- 查詢2026-03-27的訂單
SELECT * FROM varchar_date_demo 
WHERE STR_TO_DATE(date_slash, '%Y/%m/%d') = '2026-03-27';

核心問題：用STR_TO_DATE會導(dǎo)致索引失效！

-- 用EXPLAIN驗證非標(biāo)準(zhǔn)格式篩選的索引失效情況
EXPLAIN SELECT * FROM varchar_date_demo 
WHERE STR_TO_DATE(date_non_std, '%d-%m-%Y') = '2026-03-27';

EXPLAIN結(jié)果：

說明：type是ALL，key是NULL，說明索引失效，全表掃描，性能極差！

優(yōu)化方案：MySQL 8.0+用函數(shù)索引

如果你用的是MySQL 8.0.13+，可以創(chuàng)建函數(shù)索引，讓STR_TO_DATE轉(zhuǎn)換后的結(jié)果也能用上索引：

-- 第一步：創(chuàng)建函數(shù)索引（基于STR_TO_DATE的結(jié)果）
CREATE INDEX idx_date_non_std_func ON varchar_date_demo((STR_TO_DATE(date_non_std, '%d-%m-%Y')));

-- 第二步：再次查詢，驗證索引生效
EXPLAIN SELECT * FROM varchar_date_demo 
WHERE STR_TO_DATE(date_non_std, '%d-%m-%Y') = '2026-03-27';

EXPLAIN結(jié)果：

說明：索引生效了，但函數(shù)索引依然不如原生日期類型的索引性能好，且僅MySQL 8.0+支持。

三、VARCHAR存日期的核心風(fēng)險與問題

即使掌握了正確的篩選方法，VARCHAR存日期依然存在大量風(fēng)險，這也是為什么我們強(qiáng)烈不推薦的原因：

3.1 性能風(fēng)險：索引失效或性能差

• 非標(biāo)準(zhǔn)格式必須用STR_TO_DATE轉(zhuǎn)換，導(dǎo)致索引失效，全表掃描；
• 即使是標(biāo)準(zhǔn)格式，VARCHAR的索引體積也比DATE/DATETIME大（DATE僅3字節(jié)，'YYYY-MM-DD’需要10字節(jié)），緩存命中率更低，查詢性能更差。

3.2 格式混亂風(fēng)險：篩選結(jié)果錯誤

• 不同開發(fā)者可能存入不同格式的日期（比如有的存’2026-03-27’，有的存’2026/03/27’，有的存’26-03-27’），導(dǎo)致篩選時部分?jǐn)?shù)據(jù)查不到；
• 不補(bǔ)零的日期（比如’2026-3-27’）會導(dǎo)致排序錯誤，比如’2026-10-01’會排在’2026-3-27’前面，范圍查詢會漏掉數(shù)據(jù)。

3.3 數(shù)據(jù)無效風(fēng)險：存入錯誤日期

• VARCHAR可以存入任意無效日期（比如’2026-13-01’、‘2026-02-30’、‘abc’），DATE/DATETIME類型會自動拒絕這些無效日期；
• 無效日期會導(dǎo)致STR_TO_DATE轉(zhuǎn)換失敗，返回NULL，篩選結(jié)果錯誤。

3.4 日期計算風(fēng)險：無法直接用日期函數(shù)

VARCHAR無法直接用DATE_ADD（加天數(shù)）、DATEDIFF（計算日期差）、YEAR（取年份）等日期函數(shù)，必須先轉(zhuǎn)換，代碼更復(fù)雜，性能更差；

示例：

-- VARCHAR存日期：必須先轉(zhuǎn)換才能計算
SELECT 
    order_no,
    STR_TO_DATE(date_std, '%Y-%m-%d') AS order_date,
    DATE_ADD(STR_TO_DATE(date_std, '%Y-%m-%d'), INTERVAL 7 DAY) AS next_week,
    DATEDIFF(NOW(), STR_TO_DATE(date_std, '%Y-%m-%d')) AS days_ago
FROM varchar_date_demo;

-- 原生DATE類型：直接用函數(shù)，代碼簡潔，性能好
SELECT 
    order_no,
    order_date,
    DATE_ADD(order_date, INTERVAL 7 DAY) AS next_week,
    DATEDIFF(NOW(), order_date) AS days_ago
FROM date_type_demo;

3.5 排序風(fēng)險：非標(biāo)準(zhǔn)格式排序錯誤

非標(biāo)準(zhǔn)格式（比如’DD-MM-YYYY’）的字符串排序和日期排序完全不一致，ORDER BY會得到錯誤的結(jié)果；

示例：

-- 非標(biāo)準(zhǔn)格式DD-MM-YYYY的排序：錯誤！
SELECT * FROM varchar_date_demo ORDER BY date_non_std;
-- 結(jié)果：'01-04-2026'會排在'25-03-2026'前面，但實(shí)際日期是4月1日比3月25日晚！

-- 必須轉(zhuǎn)換后排序：性能差
SELECT * FROM varchar_date_demo ORDER BY STR_TO_DATE(date_non_std, '%d-%m-%Y');

四、最佳實(shí)踐：盡量改成原生日期類型！

VARCHAR存日期的最佳實(shí)踐，不是“如何更好地篩選”，而是“如何盡快改成原生日期類型”！

4.1 改造步驟：從VARCHAR改成DATE/DATETIME

假設(shè)你有一張老表，用VARCHAR存標(biāo)準(zhǔn)格式的日期，改造步驟如下：

第一步：備份數(shù)據(jù)（重要?。?/p>

改造前必須先備份數(shù)據(jù)，避免改造失敗導(dǎo)致數(shù)據(jù)丟失：

-- 備份整表
CREATE TABLE varchar_date_demo_bak LIKE varchar_date_demo;
INSERT INTO varchar_date_demo_bak SELECT * FROM varchar_date_demo;

第二步：添加臨時原生日期字段

先添加一個臨時的DATE/DATETIME字段，不要直接修改原字段，避免影響線上業(yè)務(wù)：

-- 添加臨時DATE字段
ALTER TABLE varchar_date_demo ADD COLUMN order_date DATE COMMENT '原生日期字段' AFTER date_std;

第三步：將VARCHAR數(shù)據(jù)轉(zhuǎn)換到原生字段

用STR_TO_DATE將VARCHAR數(shù)據(jù)轉(zhuǎn)換到原生字段，注意處理無效數(shù)據(jù)：

-- 標(biāo)準(zhǔn)格式Y(jié)YYY-MM-DD轉(zhuǎn)換為DATE
UPDATE varchar_date_demo 
SET order_date = STR_TO_DATE(date_std, '%Y-%m-%d')
WHERE order_date IS NULL;

-- 檢查是否有轉(zhuǎn)換失敗的NULL值（無效日期）
SELECT * FROM varchar_date_demo WHERE order_date IS NULL;
-- 手動處理這些無效數(shù)據(jù)

第四步：驗證數(shù)據(jù)正確性

驗證原生字段的數(shù)據(jù)和原VARCHAR字段一致：

-- 對比數(shù)據(jù)
SELECT 
    id,
    date_std,
    order_date,
    STR_TO_DATE(date_std, '%Y-%m-%d') AS expected_date
FROM varchar_date_demo
WHERE order_date != STR_TO_DATE(date_std, '%Y-%m-%d');
-- 應(yīng)該沒有結(jié)果，說明數(shù)據(jù)一致

第五步：修改應(yīng)用代碼，切換到原生字段

修改應(yīng)用代碼，所有讀寫操作都切換到新的原生字段order_date，灰度上線，驗證無誤后再全量切換。

第六步：刪除舊VARCHAR字段，重命名字段（可選）

應(yīng)用完全切換到原生字段后，可以刪除舊VARCHAR字段，將原生字段重命名為原字段名（如果需要）：

-- 刪除舊VARCHAR字段
ALTER TABLE varchar_date_demo DROP COLUMN date_std;

-- 重命名原生字段為原字段名（可選）
ALTER TABLE varchar_date_demo CHANGE COLUMN order_date date_std DATE NOT NULL COMMENT '日期字段';

第七步：給原生字段加索引

-- 給原生DATE字段加索引
CREATE INDEX idx_order_date ON varchar_date_demo(order_date);

4.2 如果必須保留VARCHAR（老系統(tǒng)無法改造）：嚴(yán)格遵守以下規(guī)范

如果因為歷史原因無法改造為原生日期類型，必須嚴(yán)格遵守以下規(guī)范，將風(fēng)險降到最低：

• 統(tǒng)一格式為’YYYY-MM-DD’或’YYYYMMDD’：絕對不要用其他格式；
• 強(qiáng)制補(bǔ)零：月份和日期必須補(bǔ)零（比如’2026-03-27’，不要’2026-3-27’）；
• 應(yīng)用層做格式驗證：存入數(shù)據(jù)前，應(yīng)用層必須驗證格式是否正確，拒絕無效日期；
• 篩選時直接用字符串比較：絕對不要用STR_TO_DATE等函數(shù)，避免索引失效；
• 給VARCHAR字段加索引：標(biāo)準(zhǔn)格式的VARCHAR字段可以加索引，提升篩選性能；
• 制定改造計劃：盡量找機(jī)會改成原生日期類型，不要長期用VARCHAR。

五、實(shí)戰(zhàn)示例：從VARCHAR到DATE的完整改造

我們用測試表varchar_date_demo，演示從VARCHAR到DATE的完整改造過程：

5.1 備份數(shù)據(jù)

CREATE TABLE varchar_date_demo_bak LIKE varchar_date_demo;
INSERT INTO varchar_date_demo_bak SELECT * FROM varchar_date_demo;

5.2 添加臨時原生字段

ALTER TABLE varchar_date_demo ADD COLUMN order_date DATE COMMENT '原生日期字段' AFTER date_std;

5.3 轉(zhuǎn)換數(shù)據(jù)

UPDATE varchar_date_demo 
SET order_date = STR_TO_DATE(date_std, '%Y-%m-%d')
WHERE order_date IS NULL;

5.4 驗證數(shù)據(jù)

SELECT 
    id,
    date_std,
    order_date
FROM varchar_date_demo
WHERE order_date != STR_TO_DATE(date_std, '%Y-%m-%d');
-- 無結(jié)果，數(shù)據(jù)正確

5.5 給原生字段加索引

CREATE INDEX idx_order_date ON varchar_date_demo(order_date);

5.6 用原生字段篩選（性能最好）

-- 等值查詢
SELECT * FROM varchar_date_demo WHERE order_date = '2026-03-27';

-- 范圍查詢
SELECT * FROM varchar_date_demo 
WHERE order_date BETWEEN '2026-03-25' AND '2026-03-28';

-- 日期計算
SELECT 
    order_no,
    order_date,
    DATE_ADD(order_date, INTERVAL 7 DAY) AS next_week,
    DATEDIFF(NOW(), order_date) AS days_ago
FROM varchar_date_demo;

-- EXPLAIN驗證索引生效
EXPLAIN SELECT * FROM varchar_date_demo 
WHERE order_date BETWEEN '2026-03-25' AND '2026-03-28';

EXPLAIN結(jié)果：

說明：原生DATE類型的索引生效，性能最好！

六、避坑指南：這6個錯誤絕對不要犯

6.1 用非標(biāo)準(zhǔn)格式存VARCHAR日期

• 錯誤：用’DD-MM-YYYY’、‘YYYY/MM/DD’、中文格式等非標(biāo)準(zhǔn)格式；
• 正確：統(tǒng)一用’YYYY-MM-DD’或’YYYYMMDD’，補(bǔ)零。

6.2 在VARCHAR日期列上用STR_TO_DATE等函數(shù)

• 錯誤：非標(biāo)準(zhǔn)格式篩選時用STR_TO_DATE，導(dǎo)致索引失效；
• 正確：要么改成標(biāo)準(zhǔn)格式直接字符串比較，要么改成原生日期類型。

6.3 存不補(bǔ)零的日期

• 錯誤：存’2026-3-27’、'2026-03-5’等不補(bǔ)零的日期；
• 正確：強(qiáng)制補(bǔ)零，存’2026-03-27’、‘2026-03-05’。

6.4 不做數(shù)據(jù)驗證，存入無效日期

• 錯誤：VARCHAR字段可以存任意無效日期，不做驗證；
• 正確：應(yīng)用層做格式和有效性驗證，拒絕無效日期。

6.5 長期用VARCHAR存日期，不改造

• 錯誤：因為“老系統(tǒng)不敢動”，長期用VARCHAR存日期；
• 正確：制定改造計劃，盡快改成原生日期類型。

6.6 不給VARCHAR日期字段加索引

• 錯誤：標(biāo)準(zhǔn)格式的VARCHAR日期字段不加索引，全表掃描；
• 正確：給標(biāo)準(zhǔn)格式的VARCHAR日期字段加索引，提升篩選性能。

七、總結(jié)

最后，我們用一句話總結(jié)核心觀點(diǎn)：用VARCHAR存日期是一個不推薦的反模式，生產(chǎn)環(huán)境應(yīng)優(yōu)先使用DATE、DATETIME、TIMESTAMP等原生日期類型；如果必須用VARCHAR，統(tǒng)一格式為’YYYY-MM-DD’或’YYYYMMDD’，補(bǔ)零，直接字符串比較，避免用函數(shù)轉(zhuǎn)換，同時盡快制定計劃改成原生日期類型。

關(guān)鍵要點(diǎn)回顧：

• 原生日期類型是首選：性能好、自動驗證、支持日期函數(shù)、排序正確；
• VARCHAR存日期的唯一正確格式：‘YYYY-MM-DD’或’YYYYMMDD’，補(bǔ)零；
• 標(biāo)準(zhǔn)格式篩選：直接用字符串比較，索引生效，性能好；
• 非標(biāo)準(zhǔn)格式篩選：用STR_TO_DATE轉(zhuǎn)換，索引失效，性能差；MySQL 8.0+可用函數(shù)索引優(yōu)化；
• VARCHAR存日期的風(fēng)險：性能差、格式混亂、數(shù)據(jù)無效、日期計算復(fù)雜、排序錯誤；
• 最佳實(shí)踐：盡快改成原生日期類型；如果必須保留VARCHAR，嚴(yán)格遵守規(guī)范。

永遠(yuǎn)記住：數(shù)據(jù)庫設(shè)計要從根源上避免問題，而不是后續(xù)彌補(bǔ)——原生日期類型就是為日期場景設(shè)計的，不要為了“靈活”選擇VARCHAR，否則后續(xù)的維護(hù)成本會遠(yuǎn)高于初期的“便利”。

以上就是MySQL中使用VARCHAR字段進(jìn)行日期篩選的最佳實(shí)踐的詳細(xì)內(nèi)容，更多關(guān)于MySQL VARCHAR日期篩選的資料請關(guān)注腳本之家其它相關(guān)文章！

最新評論