MySQL从CSV数据加载NULL值 [英] MySQL load NULL values from CSV data

问题描述

我有一个文件，可以包含从3到4列的数值，用逗号分隔。空字段在行末尾处用异常定义：

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

下面的表是在MySQL中创建的：

 
 + ------- + -------- + ---- + ----- + --------- + ------- + 
 |字段|类型| Null | Key |默认|额外| 
 + ------- + -------- + ------ + ----- + --------- + ----- -  + 
 |一| int（1）| YES | | NULL | | 
 |二| int（1）| YES | | NULL | | 
 |三| int（1）| YES | | NULL | | 
 |四| int（1）| YES | | NULL | | 
 |五| int（1）| YES | | NULL | | 
 + ------- + -------- + ------ + ----- + --------- + ----- -  + 
 

我试图使用MySQL LOAD命令加载数据：

  LOAD DATA INFILE'/tmp/testdata.txt'INTO TABLE moo FIELDS 
 TERMINATED BY，LINES TERMINATED BY\\\
 
  

结果表：

 
 + ------ + ------ + ------- + ------ + ------ + 
 |一|二|三|四|五| 
 + ------ + ------ + ------- + ------ + ------ + 
 | 1 | 2 | 3 | 4 | 5 | 
 | 1 | 2 | 3 | 0 | 5 | 
 | 1 | 2 | 3 | NULL | NULL | 
 + ------ + ------ + ------- + ------ + ------ + 
 

问题在于，当一个字段在原始数据中为空且未定义时，MySQL由于某种原因不使用列默认值（即NULL）并使用零。不幸的是，我必须能够在这个阶段区分NULL和0，所以任何帮助将不胜感激。

/ p>

感谢
S。

编辑

输出SHOW WARNINGS：

 
 + --------- + ------ + -------------------------------------------------- ---- + 
 |级别|代码|消息| 
 + --------- + ------ + ---------------------------- ---------------------------- + 
 |警告| 1366 |不正确的整数值：''用于第2行的列'4' 
 |警告| 1261 |第3行不包含所有列的数据
 |警告| 1261 |第3行不包含所有列的数据
 + --------- + ------ + ---------------------------- ---------------------------- + 
 

解决方案

这将做你想要的。它将第四个字段读入局部变量，然后如果局部变量最后包含空字符串，则将实际字段值设置为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，''）
; 
  

如果他们都可能是空的，那么你将它们全部读入变量，语句，如下所示：

  LOAD DATA infile'/tmp/testdata.txt'
 INTO TABLE moo 
 fields terminated BY，
 lines terminated By\\\

（@vone，@vtwo，@vthree，@vfour，@vfive）
 SET 
 one = nullif（@vone，''），
 two = nullif（@vtwo，''），
 three = nullif（@vthree，''），
 four = 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,'')
;

这篇关于MySQL从CSV数据加载NULL值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！