How can I avoid warnings when importing numbers by replacing comma's with a dot in a MySQL tabl-CodePudding

I have a table in Mysql that looks like:

CREATE TABLE `overig`.`test`(
`ID` INT(10) AUTO_INCREMENT,
`Order_ID` DECIMAL(10) NOT NULL,
`Price_total` DECIMAL(12,2),
PRIMARY KEY (`ID`)
);

I want to import the following CSV:

Order_ID;Price_total
145131343;118,8
145131349;

I load the CSV data into the MYSQL table:

LOAD DATA LOCAL INFILE 'test3.csv' INTO TABLE `overig`.`test` FIELDS TERMINATED BY ';' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES (
`Order_ID`, @Price_total)
SET 
`Price_total`  = REPLACE(@Price_total, ',', '.');

The response:
Query OK, 2 rows affected, 1 warning (0.00 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql> show warnings;
 --------- ------ --------------------------------------------------------------- 
| Level   | Code | Message                                                       |
 --------- ------ --------------------------------------------------------------- 
| Warning | 1366 | Incorrect decimal value: '' for column 'Price_total' at row 2 |
 --------- ------ --------------------------------------------------------------- 
1 row in set (0.00 sec)

How can I avoid this warning when the Price_total is een empty value?

CodePudding user response：

Use implicit convertion.

LOAD DATA LOCAL INFILE 'test3.csv' 
INTO TABLE `overig`.`test` 
FIELDS TERMINATED BY ';' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n' 
IGNORE 1 LINES 
(`Order_ID`, @Price_total)
SET `Price_total` = 0   REPLACE(@Price_total, ',', '.');