MYSQL import literal NULL into a numeric filed

Asked Aug 06 '18 at 15:31

Active Aug 06 '18 at 15:31

Viewed 113 times

I created the following table in MySQL.

CREATE TABLE person (ID int(11) NOT NULL DEFAULT '0', 
age float DEFAULT NULL,
spouse smallint(6) DEFAULT NULL)

and I tried to populate this table with an existing txt file. While most the age and spouse values are numerical, there are few records with the value for these two fields as literal NULL. for example

[1,1,NULL]
[2,Null,10]

Then the final result for this table becomes

[1,1,0]
[2,0,10]

It seems it turns literal NULL in the txt file into 0 in my SQL dataset. Does anyone know how to fix this problem: for the literal null, I just want it to turn the null value in SQL dataset. Many thanks!

asked Aug 06 '18 at 15:31

user2960592

Are you trying to populate this table using load data Infile? – P.Salmon Aug 06 '18 at 15:35
Yes, I use load data local infile – user2960592 Aug 06 '18 at 15:40
1

You can use `nullif` in your query, explained here: https://stackoverflow.com/questions/2675323/mysql-load-null-values-from-csv-data – Simranjit Singh Aug 06 '18 at 15:51
The solution provided by @SimranjitSingh should help you resolve your problem. The thing to remember is that a string with a value of "NULL" is not the same as NULL data. – UncleCarl Aug 06 '18 at 16:47
It worked. thank you all! – user2960592 Aug 08 '18 at 08:43

MYSQL import literal NULL into a numeric filed

0 Answers0