MySQL Cookbook

Don’t Assume LOAD DATA Knows More than It Does

Problem

You think LOAD DATA is smarter than it really is.

Solution

Don’t assume that LOAD DATA knows anything at all about the format of your datafile. And make sure you yourself know what its format is. If the file has been transferred from one machine to another, its contents may have been changed in subtle ways of which you’re not aware.

Discussion

Many LOAD DATA frustrations occur because people expect MySQL to know things that it cannot possibly know. LOAD DATA makes certain assumptions about the structure of input files, represented as the default settings for the line and field terminators, and for the quote and escape character settings. If your input doesn’t match those assumptions, you need to tell MySQL about it.

When in doubt, check the contents of your datafile using a hex dump program or other utility that displays a visible representation of whitespace characters like tab, carriage return, and linefeed. Under Unix, the od program can display file contents in a variety of formats. If you don’t have od or some comparable utility, the transfer directory of the recipes distribution contains hex dumpers written in Perl and Python (hexdump.pl and hexdump.py), as well as a couple of programs that display printable representations of all characters of a file (see.pl and see.py). You may find them useful for examining files to see what they really contain. In some cases, you may be surprised to discover that a file’s contents are ...