List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:June 30 1999 12:19am
Subject:LOAD DATA puts NULL into NOT NULL key
View as plain text  
>>>>> "sullivan" == sullivan  <sullivan@stripped> writes:

>> Description:
sullivan> Hi - This may be a silly question, but it looks like
sullivan> LOAD DATA will load \N into a "NOT NULL" key.

sullivan> How can I prevent this?

>> How-To-Repeat:

sullivan> Here's an example:

sullivan> create table quesa (
sullivan> 	ident VARCHAR(255) NOT NULL,
sullivan> 	size INT,
sullivan> 	PRIMARY KEY( ident));

sullivan> load data local infile "quesa.txt" into table quesa;

sullivan> ========= quesa.txt file contents: ===========
sullivan> alpha	10
sullivan> \N	20
sullivan> gamma	30
sullivan> ========= end file =======================

sullivan> It replies ...
sullivan> Records: 3  Deleted: 0  Skipped: 0  Warnings: 1

sullivan> select * from quesa;
sullivan> +-------+------+
sullivan> | ident | size |
sullivan> +-------+------+
sullivan> | alpha |   10 |
sullivan> |       |   20 |
sullivan> | gamma |   30 |
sullivan> +-------+------+
sullivan> 3 rows in set (0.00 sec)

Note that in the 3'd row, MySQL did insert an empty string, which is
NOT the same thing as NULL.
As the MySQL couldn't insert a NULL value in the column, it tried to
make the best of the situation and inserted the default value instead.

sullivan> Questions:

sullivan> -- How can I view the individual warning messages,
sullivan> instead of just seeing a count?

You can't.  Please read the LOAD DATA INFILE section in the MySQL manual !

sullivan> -- How can I prevent MySQL from loading NULL fields
sullivan> into a NOT NULL field?

See above.

What would you like happen in the above case?

sullivan> Many thanks for any hints!
sullivan> Best regards -

sullivan> Steve

Regards,
Monty
Thread
LOAD DATA puts NULL into NOT NULL keysullivan30 Jun
  • LOAD DATA puts NULL into NOT NULL keyMichael Widenius30 Jun
  • Re: LOAD DATA puts NULL into NOT NULL keyPaul DuBois30 Jun
Re: LOAD DATA puts NULL into NOT NULL keySteve Sullivan30 Jun