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 ...
Get Managing & Using MySQL, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.