MySQL从csv NULL最后一列加载infile

Question

我有一个包含 5 列数据的 .csv 文件。有时，最后一列包含 NULL 值，如下所示。

2014-07-11 23:55:00,1,245,0.05,0.01,0.0003
2014-07-11 23:57:00,1,245,0.05,0.01,\N
2014-01-17 20:14:00,2,215,0.05,0.009,0.002

我正在尝试将其加载到本地数据库中，因此我从 MySQL 控制台运行以下代码:

LOAD DATA LOCAL INFILE 'C:/<redacted>/data.csv' 
INTO TABLE tbl_data 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';

当包含 NULL 值的列被移动，使其不在最后一列时，此方法确实工作得很好，但当数据格式为如上所示。

Query OK, 71735 rows affected, 49 warnings
' for column 'energy' at row 5253    |  value: 'N

我遵循了 this thread 的建议，并尝试使用局部变量，但这也没有安抚 MySQL 之神。

LOAD DATA LOCAL INFILE 'C:/<redacted>/data.csv' 
INTO TABLE tbl_data 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';
(time, device, voltage, amps, power, @energy)
SET energy = nullif(@energy,'\N')

哦，表结构是这样设置的，我运行的是 MySQL 5.6.17:

+--------+-------------+-----+---------+
|NAME    |TYPE         |NULL | DEFAULT |
|time    |datetime     |No   | None    |
|device  |tinyint(1)   |No   | None    |
|voltage |smallint(3)  |No   | None    |
|amps    |decimal(6,3) |No   | None    |
|power   |decimal(6,3) |No   | None    |
|energy  |decimal(7,4) |Yes  | NULL    |
+--------+-------------+-----+---------+

我在这里做错了什么？

Answer 1

Rimas 是正确的，我最初声明中的最后一个子句应该是: 以 '\r\n' 结尾的行

relevant documentation措辞如下:

If you have generated the text file on a Windows system, you might have to use LINES TERMINATED BY '\r\n' to read the file properly, because Windows programs typically use two characters as a line terminator. Some programs, such as WordPad, might use \r as a line terminator when writing files. To read such files, use LINES TERMINATED BY '\r'.