MySQL Cookbook, 2nd Edition

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 ...