MySQL中STR_TO_DATE转换日期遇1411报错及更新无效问题求助

我的数据集里有一个order_date列，数据示例如下：

order_date
'2009-01-02'
'2009-01-05'
'2009-01-05'
'2009-01-05'
'2009-01-06'
'2009-01-10'
'2009-01-10'
'2009-01-11'

该列数据类型为TEXT，存储的是字符串格式的日期。

我尝试在MySQL Workbench中执行以下语句转换日期格式：

UPDATE orders SET order_date = STR_TO_DATE(order_date, '%d-%m-%Y');

执行后抛出错误：

Error Code: 1411. Incorrect datetime value: '2010-10-13' for function str_to_date

随后我将格式符修改为%Y-%m-%d，执行语句：

UPDATE orders SET order_date = STR_TO_DATE(order_date, '%Y-%m-%d');

结果返回：

0 row(s) affected Rows matched: 5506 Changed: 0 Warnings: 0

修改未生效，查询列类型仍为TEXT，怀疑'2010-10-13'这条数据存在问题，求问题原因及解决办法。

核心问题拆解 #

1. 首次报错原因：
   使用%d-%m-%Y格式符时，MySQL会按「日-月-年」规则解析字符串，但你的数据是「年-月-日」格式，比如'2010-10-13'会被错误解析为年份13、月份10、日期2010，完全不符合日期规则，因此触发1411错误。

2. 修改格式后无变更的原因：

   • 列类型仍为TEXT：即使STR_TO_DATE返回日期类型，存入TEXT列时会自动转回字符串，内容和原数据无差异，MySQL判定无行变更，返回Changed:0。
   • 存在异常数据：部分行的字符串可能带隐藏空格、多余引号或非法字符，导致STR_TO_DATE执行失败，返回NULL或原字符串，也会造成无变更。

分步解决步骤 #

1. 排查并修复异常数据 #

先找出所有无法用%Y-%m-%d解析的行：

SELECT order_date FROM orders WHERE STR_TO_DATE(order_date, '%Y-%m-%d') IS NULL;

找到异常数据后（比如带单引号、空格的行），手动修正格式，确保日期字符串符合YYYY-MM-DD标准。

2. 修改列数据类型为DATE #

直接更新TEXT列无法改变数据类型，需先修改列类型：

ALTER TABLE orders MODIFY COLUMN order_date DATE;

如果执行时提示非法日期值，说明还有未处理的异常数据，回到步骤1继续排查修复。

3. 强制转换异常格式数据（可选） #

若原数据带单引号等多余字符，可先清理再转换：

UPDATE orders SET order_date = STR_TO_DATE(TRIM(BOTH "'" FROM order_date), '%Y-%m-%d');

该语句会先去掉字符串两端的单引号，再按「年-月-日」格式转换为日期。

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