>>>>> "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
