問題描述

我有一個文件，可以包含 3 到 4 列用逗號分隔的數值.空字段被定義為例外，當它們位于行的末尾時:

1,2,3,4,51,2,3,,51,2,3

下表是在 MySQL 中創建的:

我正在嘗試使用 MySQL LOAD 命令加載數據:

LOAD DATA INFILE '/tmp/testdata.txt' INTO TABLE moo FIELDS終止于 "," 行終止于 "\n";

結果表:

問題在于，當原始數據中的字段為空且未定義時，MySQL 出于某種原因不使用列的默認值(即 NULL)而使用零.當字段完全丟失時，NULL 被正確使用.

不幸的是，在此階段我必須能夠區分 NULL 和 0，因此我們將不勝感激.

謝謝

編輯

顯示警告的輸出:

這將滿足您的需求.它將第四個字段讀入局部變量，然后將實際字段值設置為 NULL，如果局部變量最終包含一個空字符串:

LOAD DATA INFILE '/tmp/testdata.txt'INTO TABLE MOO以,"結尾的字段以\n"結尾的行(一，二，三，@vfour，五)SET 四 = NULLIF(@vfour,'');

如果它們都可能為空，那么您可以將它們全部讀入變量并有多個 SET 語句，如下所示:

LOAD DATA INFILE '/tmp/testdata.txt'INTO TABLE MOO以,"結尾的字段以\n"結尾的行(@vone、@vtwo、@vthree、@vfour、@vfive)放一 = NULLIF(@vone,''),二 = NULLIF(@vtwo,''),三 = NULLIF(@vthree,''),四 = 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 值的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！