首页 文章

MySQL从CSV数据加载NULL值

提问于
浏览 次
142

我有一个文件，可以包含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.

编辑

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             | 
+---------+------+--------------------------------------------------------+
mysql csv load-data-infile

4 回答

  • 2

    这将做你想要的 . 它将第四个字段读入局部变量，然后将实际字段值设置为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,'')
;
    回复于
  • 111

    MySQL manual说：

    使用LOAD DATA INFILE读取数据时，使用''更新空列或缺少列 . 如果要在列中使用NULL值，则应在数据文件中使用\ N.在某些情况下也可以使用文字“NULL” .

    所以你需要用\ N替换空白像这样：

    1,2,3,4,5
1,2,3,\N,5
1,2,3
    回复于
  • 155

    根据数据库配置，行为会有所不同 . 在严格模式下，这会抛出错误，否则会发出警告 . 以下查询可用于标识数据库配置 .

    mysql> show variables like 'sql_mode';
    回复于
  • 5

    预处理输入CSV以使用\ N替换空白条目 .

    尝试正则表达式：s / ,, /，\ n，/ g和s /，$ /，\ N / g

    祝好运 .

    回复于

相关问题