在MySQL开发中，用VARCHAR字段存储日期是一个非常经典的“反模式”——虽然从技术上可以实现，但会带来性能下降、格式混乱、排序错误、数据无效等一系列问题。然而在现实场景中，很多老系统、历史遗留项目依然在使用VARCHAR存储日期，因此我们需要掌握正确的筛选方法，同时明确如何优化和改造。
本文将从VARCHAR存日期的常见格式、正确筛选方法、核心风险与问题、最佳实践改造方案、实战示例五个维度出发，全面讲解在MySQL中使用VARCHAR字段进行日期筛选的知识，帮你避开坑点，同时给出长期优化方向。
前置认知：为什么会用VARCHAR存日期？这是反模式！
在开始讲解筛选方法之前，必须先明确一个核心结论：用VARCHAR存储日期是一个不推荐的反模式，生产环境应优先使用DATE、DATETIME、TIMESTAMP等原生日期类型！
为什么会有VARCHAR存日期的情况？
历史遗留系统：很多老系统在设计时，开发者对MySQL日期类型不熟悉，或者为了“灵活”选择了VARCHAR；格式兼容需求：部分业务需要存储多种格式的日期字符串（虽然这本身就是问题）；开发者认知不足：部分开发者认为“VARCHAR存字符串更简单”，忽略了后续的性能和维护成本。原生日期类型 vs VARCHAR存日期的核心对比
一、VARCHAR存日期的常见格式
VARCHAR存日期的格式五花八门，不同格式的筛选方法和性能差异极大，常见格式如下：
核心结论：如果必须用VARCHAR存日期，唯一正确的格式是’YYYY-MM-DD’或’YYYYMMDD’，且必须补零，只有这两种格式的字符串排序和日期排序完全一致，筛选时可以直接用字符串比较，无需函数转换。
二、VARCHAR存日期的正确筛选方法
根据VARCHAR存日期的格式不同，筛选方法分为两类：标准格式直接字符串比较（推荐，性能好） 和 非标准格式用函数转换（不推荐，性能差）。
前置准备：统一的测试表
为了让示例更清晰，我们先创建一张测试表，包含不同格式的VARCHAR日期字段：
CREATE TABLE varchar_date_demo ( id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, order_no VARCHAR(32) NOT NULL COMMENT '订单号', -- 标准格式：YYYY-MM-DD，补零 date_std VARCHAR(10) NOT NULL COMMENT '标准格式日期', -- 标准格式：YYYYMMDD，纯数字 date_num VARCHAR(8) NOT NULL COMMENT '纯数字格式日期', -- 非标准格式：DD-MM-YYYY date_non_std VARCHAR(10) NOT NULL COMMENT '非标准格式日期', 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日期测试表'; -- 插入测试数据 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 标准格式（YYYY-MM-DD / YYYYMMDD）：直接字符串比较（推荐）
如果VARCHAR日期是标准格式且补零，字符串排序和日期排序完全一致，可以直接用字符串比较运算符（=、>、<、>=、<=、BETWEEN、LIKE）筛选，无需函数转换，索引生效，性能最好。
示例1：等值查询（查询某一天的数据）
-- 查询2026-03-27的订单（标准格式YYYY-MM-DD） SELECT * FROM varchar_date_demo WHERE date_std = '2026-03-27'; -- 查询2026-03-27的订单（纯数字格式YYYYMMDD） SELECT * FROM varchar_date_demo WHERE date_num = '20260327';
示例2：范围查询（查询某段时间的数据）
-- 查询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：模糊查询（查询某月/某年的数据）
-- 查询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验证标准格式筛选的索引生效情况 EXPLAIN SELECT * FROM varchar_date_demo WHERE date_std BETWEEN '2026-03-25' AND '2026-03-28';
EXPLAIN结果：
说明：type是range，key是idx_date_std，说明索引生效，性能优秀。
2.2 非标准格式：用STR_TO_DATE函数转换（不推荐，性能差）
如果VARCHAR日期是非标准格式（比如DD-MM-YYYY、YYYY/MM/DD、中文格式），字符串排序和日期排序不一致，无法直接用字符串比较，必须用STR_TO_DATE函数将VARCHAR转换为DATE/DATETIME类型，再进行筛选。
STR_TO_DATE函数语法
STR_TO_DATE(字符串, 格式模板)
常用格式模板：
示例1：非标准格式DD-MM-YYYY的筛选
-- 查询2026-03-27的订单（非标准格式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：非标准格式YYYY/MM/DD的筛选
-- 假设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会导致索引失效！
-- 用EXPLAIN验证非标准格式筛选的索引失效情况 EXPLAIN SELECT * FROM varchar_date_demo WHERE STR_TO_DATE(date_non_std, '%d-%m-%Y') = '2026-03-27';
EXPLAIN结果：
说明：type是ALL，key是NULL，说明索引失效，全表扫描，性能极差！
优化方案：MySQL 8.0+用函数索引
如果你用的是MySQL 8.0.13+，可以创建函数索引，让STR_TO_DATE转换后的结果也能用上索引：
-- 第一步：创建函数索引（基于STR_TO_DATE的结果） 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结果：
说明：索引生效了，但函数索引依然不如原生日期类型的索引性能好，且仅MySQL 8.0+支持。
三、VARCHAR存日期的核心风险与问题
即使掌握了正确的筛选方法，VARCHAR存日期依然存在大量风险，这也是为什么我们强烈不推荐的原因：
3.1 性能风险：索引失效或性能差
非标准格式必须用STR_TO_DATE转换，导致索引失效，全表扫描；即使是标准格式，VARCHAR的索引体积也比DATE/DATETIME大（DATE仅3字节，'YYYY-MM-DD’需要10字节），缓存命中率更低，查询性能更差。
3.2 格式混乱风险：筛选结果错误
不同开发者可能存入不同格式的日期（比如有的存’2026-03-27’，有的存’2026/03/27’，有的存’26-03-27’），导致筛选时部分数据查不到；不补零的日期（比如’2026-3-27’）会导致排序错误，比如’2026-10-01’会排在’2026-3-27’前面，范围查询会漏掉数据。3.3 数据无效风险：存入错误日期
VARCHAR可以存入任意无效日期（比如’2026-13-01’、‘2026-02-30’、‘abc’），DATE/DATETIME类型会自动拒绝这些无效日期；无效日期会导致STR_TO_DATE转换失败，返回NULL，筛选结果错误。
3.4 日期计算风险：无法直接用日期函数
VARCHAR无法直接用DATE_ADD（加天数）、DATEDIFF（计算日期差）、YEAR（取年份）等日期函数，必须先转换，代码更复杂，性能更差；
示例：
-- VARCHAR存日期：必须先转换才能计算 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类型：直接用函数，代码简洁，性能好 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 排序风险：非标准格式排序错误
非标准格式（比如’DD-MM-YYYY’）的字符串排序和日期排序完全不一致，ORDER BY会得到错误的结果；
示例：
-- 非标准格式DD-MM-YYYY的排序：错误！ SELECT * FROM varchar_date_demo ORDER BY date_non_std; -- 结果：'01-04-2026'会排在'25-03-2026'前面，但实际日期是4月1日比3月25日晚！ -- 必须转换后排序：性能差 SELECT * FROM varchar_date_demo ORDER BY STR_TO_DATE(date_non_std, '%d-%m-%Y');
四、最佳实践：尽量改成原生日期类型！
VARCHAR存日期的最佳实践，不是“如何更好地筛选”，而是“如何尽快改成原生日期类型”！
4.1 改造步骤：从VARCHAR改成DATE/DATETIME
假设你有一张老表，用VARCHAR存标准格式的日期，改造步骤如下：
第一步：备份数据（重要！）
改造前必须先备份数据，避免改造失败导致数据丢失：
-- 备份整表 CREATE TABLE varchar_date_demo_bak LIKE varchar_date_demo; INSERT INTO varchar_date_demo_bak SELECT * FROM varchar_date_demo;
第二步：添加临时原生日期字段
先添加一个临时的DATE/DATETIME字段，不要直接修改原字段，避免影响线上业务：
-- 添加临时DATE字段 ALTER TABLE varchar_date_demo ADD COLUMN order_date DATE COMMENT '原生日期字段' AFTER date_std;
第三步：将VARCHAR数据转换到原生字段
用STR_TO_DATE将VARCHAR数据转换到原生字段，注意处理无效数据：
-- 标准格式YYYY-MM-DD转换为DATE UPDATE varchar_date_demo SET order_date = STR_TO_DATE(date_std, '%Y-%m-%d') WHERE order_date IS NULL; -- 检查是否有转换失败的NULL值（无效日期） SELECT * FROM varchar_date_demo WHERE order_date IS NULL; -- 手动处理这些无效数据
第四步：验证数据正确性
验证原生字段的数据和原VARCHAR字段一致：
-- 对比数据 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'); -- 应该没有结果，说明数据一致
第五步：修改应用代码，切换到原生字段
修改应用代码，所有读写操作都切换到新的原生字段order_date，灰度上线，验证无误后再全量切换。
第六步：删除旧VARCHAR字段，重命名字段（可选）
应用完全切换到原生字段后，可以删除旧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（老系统无法改造）：严格遵守以下规范
如果因为历史原因无法改造为原生日期类型，必须严格遵守以下规范，将风险降到最低：
统一格式为’YYYY-MM-DD’或’YYYYMMDD’：绝对不要用其他格式；强制补零：月份和日期必须补零（比如’2026-03-27’，不要’2026-3-27’）；应用层做格式验证：存入数据前，应用层必须验证格式是否正确，拒绝无效日期；筛选时直接用字符串比较：绝对不要用STR_TO_DATE等函数，避免索引失效；给VARCHAR字段加索引：标准格式的VARCHAR字段可以加索引，提升筛选性能；制定改造计划：尽量找机会改成原生日期类型，不要长期用VARCHAR。
五、实战示例：从VARCHAR到DATE的完整改造
我们用测试表varchar_date_demo，演示从VARCHAR到DATE的完整改造过程：
5.1 备份数据
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 转换数据
UPDATE varchar_date_demo SET order_date = STR_TO_DATE(date_std, '%Y-%m-%d') WHERE order_date IS NULL;
5.4 验证数据
SELECT id, date_std, order_date FROM varchar_date_demo WHERE order_date != STR_TO_DATE(date_std, '%Y-%m-%d'); -- 无结果，数据正确
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结果：
说明：原生DATE类型的索引生效，性能最好！
六、避坑指南：这6个错误绝对不要犯
6.1 用非标准格式存VARCHAR日期
错误：用’DD-MM-YYYY’、‘YYYY/MM/DD’、中文格式等非标准格式；正确：统一用’YYYY-MM-DD’或’YYYYMMDD’，补零。6.2 在VARCHAR日期列上用STR_TO_DATE等函数
错误：非标准格式筛选时用STR_TO_DATE，导致索引失效；正确：要么改成标准格式直接字符串比较，要么改成原生日期类型。
6.3 存不补零的日期
错误：存’2026-3-27’、'2026-03-5’等不补零的日期；正确：强制补零，存’2026-03-27’、‘2026-03-05’。6.4 不做数据验证，存入无效日期
错误：VARCHAR字段可以存任意无效日期，不做验证；正确：应用层做格式和有效性验证，拒绝无效日期。6.5 长期用VARCHAR存日期，不改造
错误：因为“老系统不敢动”，长期用VARCHAR存日期；正确：制定改造计划，尽快改成原生日期类型。6.6 不给VARCHAR日期字段加索引
错误：标准格式的VARCHAR日期字段不加索引，全表扫描；正确：给标准格式的VARCHAR日期字段加索引，提升筛选性能。七、总结
最后，我们用一句话总结核心观点：用VARCHAR存日期是一个不推荐的反模式，生产环境应优先使用DATE、DATETIME、TIMESTAMP等原生日期类型；如果必须用VARCHAR，统一格式为’YYYY-MM-DD’或’YYYYMMDD’，补零，直接字符串比较，避免用函数转换，同时尽快制定计划改成原生日期类型。
关键要点回顾：
原生日期类型是首选：性能好、自动验证、支持日期函数、排序正确；VARCHAR存日期的唯一正确格式：‘YYYY-MM-DD’或’YYYYMMDD’，补零；标准格式筛选：直接用字符串比较，索引生效，性能好；非标准格式筛选：用STR_TO_DATE转换，索引失效，性能差；MySQL 8.0+可用函数索引优化；VARCHAR存日期的风险：性能差、格式混乱、数据无效、日期计算复杂、排序错误；最佳实践：尽快改成原生日期类型；如果必须保留VARCHAR，严格遵守规范。
永远记住：数据库设计要从根源上避免问题，而不是后续弥补——原生日期类型就是为日期场景设计的，不要为了“灵活”选择VARCHAR，否则后续的维护成本会远高于初期的“便利”。
以上就是MySQL中使用VARCHAR字段进行日期筛选的最佳实践的详细内容，更多关于MySQL VARCHAR日期筛选的资料请关注其它相关文章！
