MySQL中将dd/MM/yyyy格式varchar日期无损转为date类型

首先，咱们得搞清楚为什么你的查询会出问题：你把日期存在varchar字段里，用字符串比较的时候，MySQL是按字典顺序来比对的，而不是日期的实际先后顺序。举个例子，'30/04/2018'作为字符串，第一个字符是3，比'03/05/2018'的第一个字符0大，所以在BETWEEN条件里会被判定为符合，这就是你看到4月数据混进来的原因。

要彻底解决这个问题，最好的办法就是把invoice_date字段从varchar转换成DATE类型。下面是一套安全可靠的分步方案，适合你这780条数据的场景：

第一步：先验证所有日期都能正确转换 #

在动手修改字段之前，先确保所有存储的dd/MM/yyyy格式字符串都能转换成有效的日期值，避免转换失败导致数据异常：

SELECT invoice_date, STR_TO_DATE(invoice_date, '%d/%m/%Y') AS converted_date
FROM pr_tax_intra
WHERE STR_TO_DATE(invoice_date, '%d/%m/%Y') IS NULL;

如果这个查询返回空结果，说明所有数据都能正常转换，可以放心继续下一步。

第二步：分步转换字段（最安全的方式） #

1. 添加临时DATE字段 #

先给表加一个临时的DATE类型字段，用来存放转换后的日期：

ALTER TABLE pr_tax_intra ADD COLUMN temp_invoice_date DATE;

2. 将原varchar日期转换后写入临时字段 #

用STR_TO_DATE函数把原字段的字符串日期转换成MySQL的DATE类型，写入临时字段：

UPDATE pr_tax_intra
SET temp_invoice_date = STR_TO_DATE(invoice_date, '%d/%m/%Y');

3. 验证转换结果 #

一定要做这一步，确保转换没有出错：

-- 检查是否有转换失败的记录（应该返回空）
SELECT invoice_date, temp_invoice_date
FROM pr_tax_intra
WHERE temp_invoice_date IS NULL;

-- 随机抽查部分记录确认转换正确
SELECT invoice_date, temp_invoice_date
FROM pr_tax_intra
LIMIT 10;

4. 替换原字段 #

如果验证没问题，就可以删除原varchar字段，把临时字段重命名为原来的字段名：

ALTER TABLE pr_tax_intra DROP COLUMN invoice_date;
ALTER TABLE pr_tax_intra CHANGE COLUMN temp_invoice_date invoice_date DATE;

临时查询方案（转换前可用） #

如果你在转换字段之前需要正确查询5月3日到5月5日的数据，可以用STR_TO_DATE先转换再比较：

SELECT invoice_date
FROM pr_tax_intra
WHERE STR_TO_DATE(invoice_date, '%d/%m/%Y') BETWEEN '2018-05-03' AND '2018-05-05';

或者两边都用STR_TO_DATE统一格式：

SELECT invoice_date
FROM pr_tax_intra
WHERE STR_TO_DATE(invoice_date, '%d/%m/%Y') 
      BETWEEN STR_TO_DATE('03/05/2018', '%d/%m/%Y') 
      AND STR_TO_DATE('05/05/2018', '%d/%m/%Y');

⚠️ 重要提醒：操作前一定要备份你的表数据！虽然780条数据量不大，但备份能避免意外情况导致的数据丢失。

内容的提问来源于stack exchange，提问作者krishna