I have a column of decimals, but MySQL is reading them as TEXT. I tried this to alter the column by:

ALTER TABLE `engine_type_project`.`weo_data_eu_test` 
CHANGE COLUMN `Mean_GDP_all_time` `Mean_GDP_all_time` DECIMAL(6,2) NULL DEFAULT NULL;

An original value is: 3,282.772

But my code returns it as: 3.00

Prior to this, I attempted:

SELECT CAST('Mean_GDP_all_time' AS DECIMAL(6,2))
FROM weo_data_eu_test;

But the entire column returned as 0.00

CodePudding user response：

In casting non-numeric values to numeric, mysql does not expect commas. So it gives up looking for additional parts of the number after the "3".

Before changing the type, remove the commas with:

update weo_data_eu_test set Mean_GDP_all_time=replace(Mean_GDP_all_time,',','')

fiddle