我要评论在mysql开发中，用varchar字段存储日期是一个非常经典的“反模式”——虽然从技术上可以实现，但会带来性能下降、格式混乱、排序错误、数据无效等一系列问题。然而在现实场景中，很多老系统、历史遗留项目依然在使用varchar存储日期，因此我们需要掌握正确的筛选方法，同时明确如何优化和改造。
本文将从varchar存日期的常见格式、正确筛选方法、核心风险与问题、最佳实践改造方案、实战示例五个维度出发，全面讲解在mysql中使用varchar字段进行日期筛选的知识，帮你避开坑点，同时给出长期优化方向。
前置认知：为什么会用varchar存日期？这是反模式！
在开始讲解筛选方法之前，必须先明确一个核心结论：用varchar存储日期是一个不推荐的反模式，生产环境应优先使用date、datetime、timestamp等原生日期类型！
为什么会有varchar存日期的情况？
- 历史遗留系统：很多老系统在设计时，开发者对mysql日期类型不熟悉，或者为了“灵活”选择了varchar；
- 格式兼容需求：部分业务需要存储多种格式的日期字符串（虽然这本身就是问题）；
- 开发者认知不足：部分开发者认为“varchar存字符串更简单”，忽略了后续的性能和维护成本。
原生日期类型 vs varchar存日期的核心对比
| 对比维度 | date/datetime/timestamp | varchar存日期 |
|---|---|---|
| 存储性能 | 占用空间小（date仅3字节，datetime仅8字节） | 占用空间大（'yyyy-mm-dd’需要10字节） |
| 索引性能 | 索引体积小，查询性能高 | 索引体积大，查询性能差 |
| 排序正确性 | 原生日期排序，绝对正确 | 仅标准格式’yyyy-mm-dd’排序正确，非标准格式排序错误 |
| 数据验证 | 自动拒绝无效日期（比如’2026-13-01’） | 可以存任意无效日期，无验证 |
| 日期函数支持 | 直接用date_add、datediff、year等函数 | 必须先转换为日期类型，才能用日期函数 |
| 筛选性能 | 直接用日期比较，索引生效 | 非标准格式需用函数转换，索引失效 |
一、varchar存日期的常见格式
varchar存日期的格式五花八门，不同格式的筛选方法和性能差异极大，常见格式如下：
| 格式类型 | 示例 | 字符串排序与日期排序是否一致 | 推荐度 |
|---|---|---|---|
| 标准格式1 | '2026-03-27'（yyyy-mm-dd，补零） | ✅ 一致 | ⭐⭐⭐（仅存varchar时的首选） |
| 标准格式2 | '20260327'（yyyymmdd，纯数字，补零） | ✅ 一致 | ⭐⭐⭐（性能略优于带横线的） |
| 非标准格式1 | '2026/03/27'（yyyy/mm/dd） | ✅ 一致（但不如横线通用） | ⭐⭐ |
| 非标准格式2 | '27-03-2026'（dd-mm-yyyy） | ❌ 不一致 | ⭐（绝对不推荐） |
| 非标准格式3 | '2026-3-27'（yyyy-m-d，不补零） | ❌ 不一致（比如’2026-10-01’会排在’2026-3-27’前面） | ⭐（绝对不推荐） |
| 非标准格式4 | '2026年03月27日'（中文格式） | ❌ 不一致 | ⭐（绝对不推荐） |
核心结论：如果必须用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 | key | extra |
|---|---|---|
| range | idx_date_std | using where |
说明：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(字符串, 格式模板)
常用格式模板：
| 模板 | 含义 | 示例 |
|---|---|---|
| %y | 4位年份 | 2026 |
| %y | 2位年份 | 26 |
| %m | 2位月份（01-12） | 03 |
| %c | 1位月份（1-12） | 3 |
| %d | 2位日期（01-31） | 27 |
| %e | 1位日期（1-31） | 27 |
示例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 | key | extra |
|---|---|---|
| all | null | using where |
说明：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结果：
| type | key | extra |
|---|---|---|
| ref | idx_date_non_std_func | using where |
说明：索引生效了，但函数索引依然不如原生日期类型的索引性能好，且仅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结果：
| type | key | extra |
|---|---|---|
| range | idx_order_date | using where |
说明：原生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日期筛选的资料请关注代码网其它相关文章！
发表评论