CHANGE COLUMN DATE NULL DEFAULT NULL

2020-09-12 18:25:38.935

数据来源于csv,生日字段是19350303,使用工具导入到mysql数据库使用,使用MySQL Workbench 6.3 CE修改字段类型,报错。

Operation failed: There was an error while applying the SQL script to the database.
ERROR 1292: Incorrect date value: '192011' for column 'birth_date' at row 13310
SQL Statement:
ALTER TABLE `atp`.`atp_players`
CHANGE COLUMN `birth_date` `birth_date` DATE NULL DEFAULT NULL

解决之道:

换个思路,先添加一个日期类型字段,修改刚添加的日期类型字段

update atp.atp_players set dt = birth_date where length(birth_date)=8;

于是新的值就是正常的日期类型啦