What is the difference between NULL and \N while loading data from a csv using load data local infile I have a table which has four fields 

create table test_hc1(cola int,colb int,colc varchar(20),cold int);


Now, I am trying to load following data

1,2,\N,4


and the third field in table is coming out as NULL, which is the expected result.

In another case, I am trying to load following data

1,2,NULL,4


and it is also loading the third field as NULL.

Here is my question : What is the difference between using the two(\N, NULL) ?

I am using

LOAD DATA LOCAL INFILE 'PATH_TO_DATAFILE'
INTO TABLE TABLE_NAME FIELDS TERMINATED BY x'01';


Any help is appreciated.

Thanks
Depends on your options FIELDS and LINES used in the LOAD DATA statement.

MySQL documentation

From MySQL doc:


  Handling of NULL values varies according to the FIELDS and LINES
  options in use:
  
  For the default FIELDS and LINES values, NULL is written as a field
  value of \N for output, and a field value of \N is read as NULL for
  input (assuming that the ESCAPED BY character is “\”).
  
  If FIELDS ENCLOSED BY is not empty, a field containing the literal
  word NULL as its value is read as a NULL value. This differs from the
  word NULL enclosed within FIELDS ENCLOSED BY characters, which is read
  as the string 'NULL'.
  
  If FIELDS ESCAPED BY is empty, NULL is written as the word NULL.
  
  With fixed-row format (which is used when FIELDS TERMINATED BY and
  FIELDS ENCLOSED BY are both empty), NULL is written as an empty
  string. Note that this causes both NULL values and empty strings in
  the table to be indistinguishable when written to the file because
  both are written as empty strings. If you need to be able to tell the
  two apart when reading the file back in, you should not use fixed-row
  format.