MySQL从CSV数据加载NULL值

Question

MySQL从CSV数据加载NULL值

我有一个文件，可以包含3到4列数字，用逗号分隔。当空字段位于行的末尾时，将定义空字段：

1,2,3,4,5

1,2,3,,5

1,2,3

下表是在MySQL中创建的：

+ ------- + -------- + ------ + ------ + --------- + ------- +

| 领域| 输入| 空| 钥匙| 默认| 额外的|

+ ------- + -------- + ------ + ------ + --------- + ------- +

| 一个| int（1）| 是的| | NULL | |

| 两个| int（1）| 是的| | NULL | |

| 三个| int（1）| 是的| | NULL | |

| 四个| int（1）| 是的| | NULL | |

| 五| int（1）| 是的| | NULL | |

+ ------- + -------- + ------ + ------ + --------- + ------- +

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

LOAD DATA INFILE '/tmp/testdata.txt' INTO TABLE moo FIELDS 

TERMINATED BY "," LINES TERMINATED BY "\n";

结果表：

+ ------ + ------ + ------- + ------ + ------ +

| 一个| 两个| 三个| 四个| 五|

+ ------ + ------ + ------- + ------ + ------ +

| 1 | 2 | 3 | 4 | 5 |

| 1 | 2 | 3 | 0 | 5 |

| 1 | 2 | 3 | NULL | NULL |

+ ------ + ------ + ------- + ------ + ------ +

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

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

谢谢S.

编辑

SHOW WARNINGS的输出：

+ --------- + ------ + -------------------------------- ------------------------ +

| 等级| 代码| 消息|

+ --------- + ------ + -------------------------------- ------------------------ +

| 警告| 1366 | 不正确的整数值：''对于第2行的第4列'

| 警告| 1261 | 第3行不包含所有列的数据

| 警告| 1261 | 第3行不包含所有列的数据

+ --------- + ------ + -------------------------------- ------------------------ +

Answer 1

这将做你想要的。它将第四个字段读入局部变量，然后将实际字段值设置为NULL，如果局部变量最终包含空字符串：LOAD DATA infile '/tmp/testdata.txt'INTO TABLE moofields terminated BY ","lines terminated BY "\n"(one, two, three, @vfour, five)SET four = nullif(@vfour,'');如果它们都可能是空的，那么你将它们全部读入变量并有多个SET语句，如下所示：LOAD DATA infile '/tmp/testdata.txt'INTO TABLE moofields terminated BY ","lines terminated BY "\n"(@vone, @vtwo, @vthree, @vfour, @vfive)SETone = nullif(@vone,''),two = nullif(@vtwo,''),three = nullif(@vthree,''),four = nullif(@vfour,'');

Answer 2

MySQL手册说：使用LOAD DATA INFILE读取数据时，使用''更新空列或缺少列。如果要在列中使用NULL值，则应在数据文件中使用\ N. 在某些情况下也可以使用文字“NULL”。所以你需要用\ N替换空白像这样：1,2,3,4,51,2,3,\N,51,2,3

Answer 3

根据数据库配置，行为会有所不同。在严格模式下，这会抛出错误，否则会发出警告。以下查询可用于标识数据库配置。mysql> show variables like 'sql_mode';