List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:February 26 2002 10:49pm
Subject:Re: LOAD DATA INFILE sets blanks to 0 instead of NULL
View as plain text  
At 13:08 -0500 2/26/02, Philip Mak wrote:
>On Tue, Feb 26, 2002 at 12:03:25PM -0600, Paul DuBois wrote:
>>  >it set the integer columns to 0 if they were blank in the text file.
>>  >How can I make it set those to NULL instead?
>>
>>  You'll have to preprocess the file to convert empty fields to \N.
>
>Damn, that's what I thought. I guess I'll have to do that for now;
>thanks for the perl code.
>
>I think that it would have made sense for MySQL to treat empty fields
>as NULL when importing into a numeric column. (Right now, the empty
>field causes a warning anyway since it converts it to 0.)

An empty field is not NULL.  It's an empty string.  MySQL performs an
implicit string-to-number conversion when loading the field, which results
in a value of zero, and a warning.

>
>Is there a place where I can put this in as a feature request (or is
>there a good reason that it shouldn't do this)?


The good reason is that LOAD DATA has a convention for representing NULL,
and your file violates it. :-)


Thread
LOAD DATA INFILE sets blanks to 0 instead of NULLPhilip Mak26 Feb
  • Re: LOAD DATA INFILE sets blanks to 0 instead of NULLPaul DuBois26 Feb
    • Re: LOAD DATA INFILE sets blanks to 0 instead of NULLPhilip Mak26 Feb
      • Re: LOAD DATA INFILE sets blanks to 0 instead of NULLPaul DuBois26 Feb