MySQL load data null values
- by SP1
Hello,
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 | 0 |
| 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.
