MySQL将varchar类型年月字段转换为date类型并修改字段类型的方法

先分析你之前SQL的问题 #

• 第一条SQL使用STR_TO_DATE(year_and_month,"%Y-%m-%d")，但year_and_month只有YYYY-MM格式，缺少日期部分，导致函数无法解析，返回NULL。
• 第二条SQL硬编码了"2021-10"，没有引用表中的year_and_month字段，所以结果全是固定值，不是表内实际数据。

解决方案 #

因为DATE类型必须包含完整的年月日，所以需要先把YYYY-MM格式的字符串补全为合法日期（比如补当月第一天-01），再修改字段类型。以下是两种可行方法：

方法一：直接修改字段类型（简洁高效）

先验证转换逻辑是否正确：

-- 检查转换后的日期是否符合预期
SELECT STR_TO_DATE(CONCAT(year_and_month, '-01'), '%Y-%m-%d') FROM migration_flows;

确认结果无误后，执行修改字段类型的语句：

ALTER TABLE migration_flows MODIFY COLUMN year_and_month DATE;

MySQL会自动将YYYY-MM格式的字符串转换为YYYY-MM-01的DATE类型值。

方法二：分步操作（更安全，适合重要数据）

如果担心直接修改导致数据问题，可通过临时字段过渡：

1. 添加临时DATE字段：

ALTER TABLE migration_flows ADD COLUMN temp_year_month DATE;

2. 将原字段数据转换后写入临时字段：

UPDATE migration_flows SET temp_year_month = STR_TO_DATE(CONCAT(year_and_month, '-01'), '%Y-%m-%d');

3. 验证临时字段数据正确后，替换原字段：

ALTER TABLE migration_flows DROP COLUMN year_and_month;
ALTER TABLE migration_flows CHANGE COLUMN temp_year_month year_and_month DATE;

注意：如果你的业务只需要存储年月，也可以考虑使用MySQL 8.0.19及以上版本支持的YEAR_MONTH类型，该类型专门用于存储年月信息，比DATE更贴合需求。

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