MySQL 从 CSV 数据加载 NULL 值

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

我正在try 使用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，所以任何帮助都将不胜感激.

谢谢

编辑

显示警告的输出:

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