MySQL中带逗号分隔值的varchar列转decimal报错，求解决方案

Hey there! Let's work through this problem together. Converting a VARCHAR column with comma-containing values to DECIMAL in MySQL almost always fails because of how that comma is being used—either as a decimal separator (like in European number formats) or a thousands separator. Let's break down the most common scenarios and their fixes:

Scenario 1: Comma is the decimal separator (e.g., "123,45" instead of "123.45") #

MySQL uses the dot (.) as the default decimal separator, so trying to cast "123,45" directly to DECIMAL will throw an error. First, we need to replace commas with dots, then convert:

Step 1: Test the conversion first (safe, no changes to your table) #

SELECT CAST(REPLACE(your_column_name, ',', '.') AS DECIMAL(10,2)) FROM your_table_name;

Adjust DECIMAL(10,2) to match your data's precision—10 is the total number of digits, 2 is the number of decimal places.

Step 2: Update the table structure (only if the test works!) #

Before making changes, always back up your table:

CREATE TABLE your_table_backup AS SELECT * FROM your_table_name;

Then modify the column:

ALTER TABLE your_table_name MODIFY COLUMN your_column_name DECIMAL(10,2);

Scenario 2: Comma is the thousands separator (e.g., "1,234.56") #

Here, we need to remove the commas entirely before converting to DECIMAL:

Step 1: Test the conversion #

SELECT CAST(REPLACE(your_column_name, ',', '') AS DECIMAL(10,2)) FROM your_table_name;

Step 2: Update the table (after backup!) #

ALTER TABLE your_table_name MODIFY COLUMN your_column_name DECIMAL(10,2);

Troubleshooting: If you still get errors #

If the conversion fails even after replacing/removing commas, you probably have non-numeric characters in your column (like spaces, letters, or extra symbols). Find those rows first:

SELECT * FROM your_table_name WHERE your_column_name REGEXP '[^0-9,.]';

Clean up those invalid values (update or delete them) before trying the conversion again.

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