我正在嘗試將帶有一些空單元格的 CSV 匯入 MySQL 資料庫。我可以請你幫我嗎？
我的最后一個執行緒已關閉，因為它“與
CSV 檔案csvfile.csv：
id,city,number,comment
1,NY,1,Something
2,W,2,
3,C,1,Something
4,LA,1,
所以我使用以下命令加載 CSV：
LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/csvfile.csv'
INTO TABLE citytable
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id,city,number,@vcomment)
SET comment = NULLIF(@vcomment,'');
我還嘗試設定默認值commentas''并重新運行命令并收到錯誤“錯誤 1261 (01000)：第 4 行不包含所有列的資料”。
LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/csvfile.csv'
INTO TABLE citytable
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id,city,number,comment);
如果該單元格中沒有資料，我該如何匯入資料庫并讓值為“NULL”？
uj5u.com熱心網友回復：
更改為時LOAD FILE：
LOAD DATA INFILE 'D:/TEMP/csvfile.csv'
INTO TABLE citytable
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(id,city,number,@vcomment)
SET comment = NULLIF(@vcomment,'');
我確實得到了以下結果：
MySQL [test]> LOAD DATA INFILE 'D:/TEMP/csvfile.csv'
-> INTO TABLE citytable
-> FIELDS TERMINATED BY ','
-> ENCLOSED BY '"'
-> LINES TERMINATED BY '\r\n'
-> IGNORE 1 LINES
-> (id,city,number,@vcomment)
-> SET comment = NULLIF(@vcomment,'');
Query OK, 4 rows affected (0.00 sec)
Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
MySQL [test]> select * from citytable;
---- ------ -------- -----------
| id | city | number | comment |
---- ------ -------- -----------
| 1 | NY | 1 | Something |
| 2 | W | 2 | NULL |
| 3 | C | 1 | Something |
| 4 | LA | 1 | NULL |
---- ------ -------- -----------
4 rows in set (0.00 sec)
MySQL [test]>
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/450609.html
標籤：mysql CSV mysql-加载文件