我在 Mysql 中有一個表，如下所示：
CREATE TABLE `overig`.`test`(
`ID` INT(10) AUTO_INCREMENT,
`Order_ID` DECIMAL(10) NOT NULL,
`Price_total` DECIMAL(12,2),
PRIMARY KEY (`ID`)
);
我想匯入以下 CSV：
Order_ID;Price_total
145131343;118,8
145131349;
我將 CSV 資料加載到 MYSQL 表中：
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)
當 Price_total 為空值時，如何避免此警告？
uj5u.com熱心網友回復：
使用隱式轉換。
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, ',', '.');
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/428822.html
上一篇：在csv列上寫入并更改標題
下一篇：更改csvpython的標題