Declare NULL values in mysql

Question

I would like to create a SQL table using the LOAD DATA INFILE comande. I have previously created a table with an attribut (column) FLOAT like:

CREATE TABLE table_1 (
    foo INT );

So now I can use the LOAD DATA INFILE comand. In the file I want to load, missing values are codded as 'NA'. When I run this command 'NA' values are translated as '0' and not as NULL values which is the mysql standard for missing values.

My question is: How I can declare to mysql that 'NAs' are missing values.

Appologize if this is a current topic but as I am not a SQL specialist I was not able to find the answer to this question.

Thanks in advance,

Marc

Please check the linked question. I believe there's no other way than coding it in a per-column basis (using `nullif()`or whatever). — Álvaro González, Nov 07 '18 at 14:15

peterm · Answer 1 · 2018-11-07T15:18:18.167

1

Something along the lines of

LOAD DATA INFILE 'path/to/file.csv'
INTO TABLE table_1
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"' 
IGNORE 1 LINES 
(@foo)
SET foo = NULLIF(@foo, 'NA')

Given the file path/to/file.csv has the following contents

foo
1
2
3
NA
4
NA
NA

Here is what you'd see in your table after loading the data

mysql> select * from table_1;
+------+
| foo  |
+------+
|    1 |
|    2 |
|    3 |
| NULL |
|    4 |
| NULL |
| NULL |
+------+
7 rows in set (0.00 sec)

edited Nov 07 '18 at 15:18

answered Nov 07 '18 at 14:19

peterm

91,357
15
148
157

Thank you. A problem remain because NAs have been translated into 0s. Maybe I could create first a `VARCHAR()` column, then use `SET foo = NULLIF(@foo, 'NA')` and finally, translate my column as an integer. – marc Nov 07 '18 at 14:51
What do you mean by "translated into 0s"? Do you have `NA` or `0` in your file? – peterm Nov 07 '18 at 15:15

Declare NULL values in mysql

1 Answers1