0

I noticed that I can insert multiple NULL values into a table with a UNIQUE key :

INSERT INTO table (autoincrementkey, uniquekey, id) VALUES (NULL, NULL, 0), (NULL, NULL, 1), (NULL, NULL, 2)

This works fine as I get the 3 lines inserted (and yes, the uniquekey is NULL = Yes)

However, when I try to use the Load Data from a file with the same query, it only inserts 1 NULL value, which is the first.

LOAD DATA LOCAL INFILE $file.csv INTO TABLE table FIELDS TERMINATED BY ',' ENCLOSED BY '"'

Is there any way I can insert multiple NULL values on a UNIQUE column, with the LOAD DATA INTO please ?

Thanks in advance!

=============

[UPDATE]

I noticed that uniquekey gets a 0 instead of a NULL, which is why only the 1st entry gets to the database. In the CSV file it looks like this :

,,1
,,2
,,3

But uniquekey is set to NULL by default, I don't get why it gets 0 :

ALTER TABLE `table` CHANGE `uniquekey ` `uniquekey ` INT(10) DEFAULT NULL;

[UPDATE 2]

OK I got it!! I thought (,,1) would insert NULL, NULL, 1. But it's not true, it has to be (NULL,NULL,1) to get what you see.

simhamed
  • 75
  • 1
  • 7
  • Please add some sample data. I doubt you are inserting `null`, but you are probably inserting the string `'null'` (as if you would insert `null`, it should work). – Solarflare Oct 09 '17 at 08:54
  • Thanks for your reply! When I export my .csv file (the query contains NULL), in the file I have data like this : ,,0 ,,1 So it should be NULL since by default the value of uniquekey is NULL – simhamed Oct 09 '17 at 08:59
  • Well, what you think should happen and what actually happens can be two different things, especially with `null`. See e.g. [here](https://dev.mysql.com/doc/refman/5.7/en/problems-with-null.html) (the part starting with "When reading data with LOAD DATA INFILE,"). You can use `\N` to mark nulls, or use the method described in [MySQL load NULL values from CSV data](https://stackoverflow.com/q/2675323/6248528) (or something similar). – Solarflare Oct 09 '17 at 09:21
  • I've just updated my first post : Both NULL and \N gets me a 0 instead of NULL for uniquekey, that's why I only get the first entry and not the rest. Any idea why it sets uniquekey to 0 ? I know it's an INT but the default value should be NULL – simhamed Oct 09 '17 at 12:02

0 Answers0