Recipe 10.35. Importing and Exporting NULL Values

Problem

You're not sure how to represent NULL values in a datafile.

Solution

Try to use a value not otherwise present, so that you can distinguish NULL from all other legitimate non-NULL values. When you import the file, look for that value and convert instances of it to NULL.

Discussion

There's no standard for representing NULL values in datafiles, which makes them a bit of a problem for import and export operations. Some of the difficulty arises from the fact that NULL indicates the absence of a value, and something that's not there is 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 strategy for dealing with this problem is to represent NULL using a value that doesn't otherwise occur in the data. This is how LOAD DATA and mysqlimport handle the issue: they understand the value of \N by convention to mean NULL. (\N is interpreted as NULL when it occurs by itself, and 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 will treat 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 contains 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 capability for preprocessing input values: 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),     ->     c2 = IF(@c2=-1,NULL,@c2),     ->     c3 = IF(@c3='',NULL,@c3);             

The resulting data after import looks like this:

+------+------+------------+ | c1   | c2   | c3         | +------+------+------------+ | str1 |   13 | 1997-10-14 | | str2 | NULL | 2009-05-07 | | NULL |   15 | NULL       | | NULL | NULL | 1973-07-15 | +------+------+------------+ 

The preceding discussion pertains to interpreting NULL values for import into MySQL, but it's also necessary to think about NULL values when transferring data in the other directionfrom MySQL into other programs. Here are some examples:

• SELECT ... INTO OUTFILE writes NULL values as \N. Will another program understand that convention? If not, you need to convert \N to something the program will understand. For example, the SELECT statement can export the column using an expression like this:

  IFNULL(col_name,'Unknown') 

  
  

• You can use mysql in batch mode as an easy way to produce tab-delimited output (Section 10.13), but one problem with doing so is that NULL values appear in the output as instances of the word "NULL". If that word occurs nowhere else in the output, you may be able to postprocess it to convert instances of the word to something more appropriate. For example, you can use a one-line sed command:

  % sed -e "s/NULL/\\N/g" data.txt > tmp                   

  
  

  If the word "NULL" does appear where it represents something other than a NULL value, then it's ambiguous and you should probably export your data differently. For example, your export statement could use IFNULL⁠(⁠ ⁠ ⁠) to map NULL values to something else.