小编典典

MySQL 从 CSV 数据加载 NULL 值

all

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

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

下表是在 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    |       | 
+-------+--------+------+-----+---------+-------+

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

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

结果表:

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

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

不幸的是,在这个阶段我必须能够区分 NULL 和 0,所以任何帮助都将不胜感激。

谢谢S。

编辑

显示警告的输出:


+---------+------+--------------------------------------------------------+
| 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             | 
+---------+------+--------------------------------------------------------+

阅读 72

收藏
2022-08-01

共1个答案

小编典典

这将做你想要的。它将第四个字段读入局部变量,然后将实际字段值设置为 NULL,如果局部变量最终包含一个空字符串:

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,'')
;

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

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,'')
;
2022-08-01