MySQL Cookbook

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

Discussion

There’s no particular 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 lack 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 column types as well. For example, if you load an empty value with LOAD DATA into a numeric column, it gets 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, with \N as the value that is understood by convention to mean NULL. Based on that fact, it’s sometimes helpful to convert empty fields in a datafile to \N so that LOAD DATA will interpret them as NULL. It’s easy to write a script that does this:

#! /usr/bin/perl -w # empty_to_null.pl - Convert empty input fields to \N. # \N is the MySQL ...