Managing & Using MySQL, 2nd Edition

Batch Processing

Batch loading is the act of loading a lot of data into or pulling a lot of data out of MySQL all at once. MySQL supports two types of batch loading.

Command-line loads

In the simplest kind of batch load, you stick all your SQL commands in a file and send the contents of that file to MySQL:

mysql -h somehost -u uid -p < filename

In other words, you are using the command line to pipe the SQL commands into the mysql command-line utility. The examples on this book’s web site contain several SQL command files that you can load into MySQL in this manner before you run the examples.

The LOAD command

The LOAD command enables you to load data from a file containing only data (no SQL commands). For example, if you had a file containing the names of all the books in your collection with one book on each line and the title and author separated by a tab, you could use the following command to load that data into your book table:

LOAD DATA LOCAL INFILE 'books.dat' INTO TABLE BOOK;

This command assumes that the file books.dat has one line for each database record to be inserted. It further assumes that there is a value for every column in the table or \N for null values. So, if the BOOK table has three columns, each line of books.dat should have three tab-separated values.

The LOCAL keyword tells the mysql command line to look for the file on the same machine as the client. ^[10] Without it, MySQL looks for the file on the server. Of course, if you are trying to load something on ...