我有这样一个文件,:
1,2,3,4,5
1,2,3,,5
1,2,3
下表是在MySQL中创建的:
+-------+--------+------+-----+---------+-------+
Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| 一个| 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正确使用。
发布于 2018-01-22 08:52:49
这将做你想要的。它将第四个字段读入局部变量,然后将实际字段值设置为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,'')
;
发布于 2018-01-22 09:53:27
需要用\ N替换这个空格:
1,2,3,4,5
1,2,3,\N,5
1,2,3
https://stackoverflow.com/questions/-100007206
复制相似问题