Importing and Exporting Data - MySQL - page 386

Solution
Use a value not otherwise present, so that you can distinguish NULL from all other le‐
gitimate non- NULL values. When you import the file, convert instances of that value to
NULL .
Discussion
There's no standard for representing NULL values in datafiles, which makes them prob‐
lematic for import and export operations. The difficulty arises from the fact that NULL
indicates the absence of a value, and that's not easy to represent literally in a datafile.
Using an empty column value is the most obvious thing to do, but that's ambiguous for
string-valued columns because there is no way to distinguish a NULL represented that
way from a true empty string. Empty values can be a problem for other data types as
well. For example, if you load an empty value with LOAD DATA into a numeric column,
it is stored as 0 rather than as NULL and thus becomes indistinguishable from a true 0 in
the input.
The usual solution to this problem is to represent NULL using a value not otherwise
present in the data. This is how LOAD DATA and mysqlimport handle the issue: they un‐
derstand the value of \N by convention to mean NULL . ( \N is interpreted as NULL only
when it occurs by itself, not as part of a larger value such as x\N or \Nx .) For example,
if you load the following datafile with LOAD DATA , it treats the instances of \N as NULL :
str1 13 1997-10-14
str2 \N 2009-05-07
\N 15 \N
\N \N 1973-07-14
But you might want to interpret values other than \N as signifying NULL , and you might
have different conventions in different columns. Consider the following datafile:
str1 13 1997-10-14
str2 -1 2009-05-07
Unknown 15
Unknown -1 1973-07-15
The first column contains strings, and Unknown signifies NULL . The second column con‐
tains integers, and -1 signifies NULL . The third column contains dates, and an empty
value signifies NULL . What to do?
To handle situations like this, use LOAD DATA 's input preprocessing capability: specify a
column list that assigns input values to user-defined variables and use a SET clause that
maps the special values to true NULL values. If the datafile is named has_nulls.txt , the
following LOAD DATA statement properly interprets its contents:
mysql> LOAD DATA LOCAL INFILE 'has_nulls.txt'
-> INTO TABLE t (@c1,@c2,@c3)
-> SET c1 = IF(@c1='Unknown',NULL,@c1),