Mysql

MySQL import csv 只得到一半的行

  • March 22, 2022

我使用以下命令讀取 csv:

LOAD DATA INFILE '/Users/Tyler/Desktop/players_escaped.txt' 
INTO TABLE players
FIELDS TERMINATED BY ',' 
ENCLOSED BY '^'
LINES TERMINATED BY '\n';

csv 看起來像這樣:

^1^,^False^,^False^,^Ovie^,^Soko^,^6^,^8^,^210^,^^,^M^,^London^,^^,^^,^^,^^,^0^,^2009^,^^,^False^,^False^,^{299F909C-88D9-4D26-8ADC-3EC1A66168BB}^,^844^,^2013^,^^,^^,^0^,^^,^^,^2011-02-16 20:53:34.877000000^,^^,^2011-02-16 20:53:34.877000000^,^^,^^,^^,^^,^^,^^,^^,^^,^^,^1^,^2^,^^,^^,^^,^^,^^,^^,^^,^^,^^,^^
^2^,^False^,^False^,^Jordan^,^Swing^,^6^,^6^,^200^,^^,^M^,^Birmingham^,^AL^,^35218^,^^,^^,^0^,^2009^,^^,^False^,^False^,^{299F909C-88D9-4D26-8ADC-3EC1A66168BB}^,^844^,^2013^,^^,^^,^0^,^^,^^,^2011-02-16 20:53:34.877000000^,^^,^2011-02-16 20:53:34.877000000^,^^,^^,^^,^^,^^,^^,^^,^^,^^,^1^,^2^,^^,^^,^^,^^,^^,^^,^^,^^,^^,^^

我也嘗試\作為分隔符並得到相同的結果。

我只得到奇數行。

csv 中有 250k 條記錄。

謝謝

您可能需要檢查每行的最後一個字元

而不是這個:

LOAD DATA INFILE '/Users/Tyler/Desktop/players_escaped.txt'  
INTO TABLE players 
FIELDS TERMINATED BY ','  
ENCLOSED BY '^' 
LINES TERMINATED BY '\n'; 

嘗試這個:

LOAD DATA INFILE '/Users/Tyler/Desktop/players_escaped.txt'  
INTO TABLE players 
FIELDS TERMINATED BY ','  
ENCLOSED BY '^' 
LINES TERMINATED BY '\r\n'; 

在此處輸入圖像描述

不要使用命令行,它需要設置環境參數以允許您從本地 csv 文件導入。

相反,使用導入嚮導對我來說要容易得多。 在此處輸入圖像描述

但是我只導入前 100 行。

我通過選擇 latin1 而不是預設的 utf-8 來解決它

https://bugs.mysql.com/bug.php?id=89044

預設 utf-8 無效,latin1 有效

引用自:https://dba.stackexchange.com/questions/15423