List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:March 14 1999 12:34pm
Subject:Importing NULL values
View as plain text  
>>>>> "garyl" == garyl cwa4 epa gov
> <garyl.cwa4.epa.gov@stripped> writes:

>> Description:
garyl> 	I am trying to do a LOAD DATA INFILE where the data file has NULL
garyl> values.  For example, using the table:

garyl>      +--------+-------------+------+-----+---------+-------+
garyl>      | Field  | Type        | Null | Key | Default | Extra |
garyl>      +--------+-------------+------+-----+---------+-------+
garyl>      | fname  | varchar(10) | YES  |     | NULL    |       |
garyl>      | lname  | varchar(10) | YES  |     | NULL    |       |
garyl>      | age    | int(4)      | YES  |     | NULL    |       |
garyl>      | height | float(5,2)  | YES  |     | NULL    |       |
garyl>      +--------+-------------+------+-----+---------+-------+
garyl>      4 rows in set (0.16 sec)

garyl> and data file:

garyl>      "sally","rogers",,
garyl>      "joe","blo",32,5.5
garyl>      "test","",,
     
<cut>

The problem is that you have given an empty string for each of the
unknown values;  MySQL will try to store the empty string into each
column as good as possible (in a number filed, a empty string is
converted to 0).

As a lot of users pointed out the following will work:

"sally","rogers",NULL,NULL
"joe","blo",32,5.5
"test","",NULL, NULL

but also the following:

"sally","rogers"
"joe","blo",32,5.5
"test",""

Regards,
Monty
Thread
Importing NULL valuesgaryl.cwa4.epa.gov12 Mar
  • Re: Importing NULL valuesChris12 Mar
  • Re: Importing NULL valuesThimble Smith12 Mar
  • Re: Importing NULL valuesMicheal Mc Evoy12 Mar
  • Re: Importing NULL valuesChristian Mack12 Mar
  • Importing NULL valuesMichael Widenius15 Mar