MySQL Cookbook

10.2 Importing Data with LOAD DATA and mysqlimport

10.2.1 Problem

You want to load a datafile into a table using MySQL's built in import capabilities.

10.2.2 Solution

Use the LOAD DATA statement or the mysqlimport command-line program.

10.2.3 Discussion

MySQL provides a LOAD DATA statement that acts as a bulk data loader. Here's an example statement that reads a file mytbl.txt from your current directory and loads it into the table mytbl in the current database:

mysql> LOAD DATA LOCAL INFILE 'mytbl.txt' INTO TABLE mytbl;

MySQL also includes a utility program named mysqlimport that acts as a wrapper around LOAD DATA so that you can load input files directly from the command line. The mysqlimport command that is equivalent to the preceding LOAD DATA statement looks like this, assuming that mytbl is in the cookbook database:^[1]

^[1] For mysqlimport, as with other MySQL programs, you may need to specify connection parameter options such as --user or --host. If so, they should precede the database name argument.

% mysqlimport --local cookbook mytbl.txt

The following list describes LOAD DATA's general characteristics and capabilities; mysqlimport shares most of these behaviors. There are some differences that we'll note as we go along, but for the most part you can read "LOAD DATA" as "LOAD DATA or mysqlimport." LOAD DATA provides options to address many of the import issues mentioned in the chapter introduction, such as the line-ending sequence for recognizing how to break input into records, the column value delimiter that allows records to be broken into separate values, the quoting character that may surround column values, quoting and escaping issues within values, and NULL value representation:

• By default, LOAD DATA expects the datafile to contain the same number of columns as the table into which you're loading data, and the datafile columns must be present in the same order as in the table. If the file doesn't contain a value for every column or the values aren't in the proper order, you can specify which columns are present and the order in which they appear. If the datafile contains fewer columns than the table, MySQL assigns default values to columns for which no values are present in the datafile.

• LOAD DATA assumes that data values are separated by tab characters and that lines end with linefeeds (newlines). You can specify the data format explicitly if a file doesn't conform to these conventions.

• You can indicate that data values may have quotes around them that should be stripped, and you can specify what the quote character is.

• Several special escape sequences are recognized and converted during input processing. The default escape character is backslash (\), but you can change it if you like. The \N sequence is taken to represent a NULL value. The \b, \n, \r, \t, \\, and \0 sequences are interpreted as backspace, linefeed, carriage return, tab, backslash, and ASCII NUL characters. (NUL is a zero-valued byte, which is different than the SQL NULL value.)

• LOAD DATA provides diagnostic information, but it's a summary that doesn't give you specific information about which input lines may have caused problems. There is work in progress for MySQL 4 on providing improved feedback. In the meantime, see Recipe 10.38, which describes a LOAD DATA diagnostic utility.

The next few sections describe how to import datafiles into MySQL tables using LOAD DATA or mysqlimport. They assume your files contain legal data values that are acceptable to MySQL. Why make this assumption? Because although LOAD DATA has several options that control how it reads the datafile, they're concerned only with the structure of the file. LOAD DATA won't validate or reformat data values for you. It's necessary to perform such operations either by preprocessing the datafile before loading it, or by issuing SQL statements after loading it. If you need to check or reformat an input file first to make sure it's legal, several sections later in the chapter show how to do that.