MySQL 从 CSV 数据加载 NULL 值

我有一个文件，可以包含 3 到 4 列用逗号分隔的数值.空字段被定义为例外，当它们位于行的末尾时:

1,2,3,4,51,2,3,,51,2,3

下表是在 MySQL 中创建的:

<前>+-------+--------+------+-------+-------+-------+|领域 |类型 |空 |钥匙 |默认 |额外 |+-------+--------+------+-------+-------+-------+|一 |整数(1) |是 ||空 |||二 |整数(1) |是 ||空 |||三 |整数(1) |是 ||空 |||四 |整数(1) |是 ||空 |||五 |整数(1) |是 ||空 ||+-------+--------+------+-------+-------+-------+

我正在尝试使用 MySQL LOAD 命令加载数据:

LOAD DATA INFILE '/tmp/testdata.txt' INTO TABLE moo FIELDS终止于 "," 行终止于 "\n";

结果表:

<前>+------+------+-------+------+------+|一 |二 |三 |四 |五 |+------+------+-------+------+------+|1 |2 |3 |4 |5 ||1 |2 |3 |0 |5 ||1 |2 |3 |空 |空 |+------+------+-------+------+------+

问题在于，当原始数据中的字段为空且未定义时，MySQL 出于某种原因不使用列的默认值(即 NULL)而使用零.当字段完全丢失时，NULL 被正确使用.

不幸的是，在此阶段我必须能够区分 NULL 和 0，因此我们将不胜感激.

谢谢

编辑

显示警告的输出:

<前>+---------+------+--------------------------------------------------------+|级别 |代码 |留言 |+---------+------+--------------------------------------------------------+|警告 |第1366章不正确的整数值:第 2 行的列 'four' 的 '' ||警告 |第1261章第 3 行不包含所有列的数据 ||警告 |第1261章第 3 行不包含所有列的数据 |+---------+------+--------------------------------------------------------+

解决方案

这将满足您的需求.它将第四个字段读入局部变量，然后将实际字段值设置为 NULL，如果局部变量最终包含一个空字符串:

LOAD DATA INFILE '/tmp/testdata.txt'INTO TABLE MOO以,"结尾的字段以\n"结尾的行(一，二，三，@vfour，五)SET 四 = NULLIF(@vfour,'');

如果它们都可能为空，那么您可以将它们全部读入变量并有多个 SET 语句，如下所示:

LOAD DATA INFILE '/tmp/testdata.txt'INTO TABLE MOO以,"结尾的字段以\n"结尾的行(@vone、@vtwo、@vthree、@vfour、@vfive)放一 = NULLIF(@vone,''),二 = NULLIF(@vtwo,''),三 = NULLIF(@vthree,''),四 = NULLIF(@vfour,'');

I have a file that can contain from 3 to 4 columns of numerical values which are separated by comma. Empty fields are defined with the exception when they are at the end of the row:

1,2,3,4,5
1,2,3,,5
1,2,3

The following table was created in MySQL:

+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| one   | int(1) | YES  |     | NULL    |       | 
| two   | int(1) | YES  |     | NULL    |       | 
| three | int(1) | YES  |     | NULL    |       | 
| four  | int(1) | YES  |     | NULL    |       | 
| five  | int(1) | YES  |     | NULL    |       | 
+-------+--------+------+-----+---------+-------+

I am trying to load the data using MySQL LOAD command:

LOAD DATA INFILE '/tmp/testdata.txt' INTO TABLE moo FIELDS 
TERMINATED BY "," LINES TERMINATED BY "\n";

The resulting table:

+------+------+-------+------+------+
| one  | two  | three | four | five |
+------+------+-------+------+------+
|    1 |    2 |     3 |    4 |    5 | 
|    1 |    2 |     3 |    0 |    5 | 
|    1 |    2 |     3 | NULL | NULL | 
+------+------+-------+------+------+

The problem lies with the fact that when a field is empty in the raw data and is not defined, MySQL for some reason does not use the columns default value (which is NULL) and uses zero. NULL is used correctly when the field is missing alltogether.

Unfortunately, I have to be able to distinguish between NULL and 0 at this stage so any help would be appreciated.

Thanks S.

edit

The output of SHOW WARNINGS:

+---------+------+--------------------------------------------------------+
| Level   | Code | Message                                                |
+---------+------+--------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: '' for column 'four' at row 2 | 
| Warning | 1261 | Row 3 doesn't contain data for all columns             | 
| Warning | 1261 | Row 3 doesn't contain data for all columns             | 
+---------+------+--------------------------------------------------------+

解决方案

This will do what you want. It reads the fourth field into a local variable, and then sets the actual field value to NULL, if the local variable ends up containing an empty string:

LOAD DATA INFILE '/tmp/testdata.txt'
INTO TABLE moo
FIELDS TERMINATED BY ","
LINES TERMINATED BY "\n"
(one, two, three, @vfour, five)
SET four = NULLIF(@vfour,'')
;

If they're all possibly empty, then you'd read them all into variables and have multiple SET statements, like this:

LOAD DATA INFILE '/tmp/testdata.txt'
INTO TABLE moo
FIELDS TERMINATED BY ","
LINES TERMINATED BY "\n"
(@vone, @vtwo, @vthree, @vfour, @vfive)
SET
one = NULLIF(@vone,''),
two = NULLIF(@vtwo,''),
three = NULLIF(@vthree,''),
four = NULLIF(@vfour,'')
;