Using the mysql Client Program - MySQL - page 15

mysql can also read statements from a file or from another program (see Recipe 1.6 ).
1.6. Executing SQL Statements Read from a File or
Program
Problem
You want mysql to read statements stored in a file so that you need not enter them
manually. Or you want mysql to read the output from another program.
Solution
To read a file, redirect mysql 's input, or use the source command. To read from a pro‐
gram, use a pipe.
Discussion
By default, the mysql program reads input interactively from the terminal, but you can
feed it statements using other input sources such as a file or program.
To create an SQL script for mysql to execute in batch mode, put your statements in a
text file. Then invoke mysql and redirect its input to read from that file:
% mysql cookbook < file_name
Statements read from an input file substitute for what you'd normally enter interactively
by hand, so they must be terminated with ; , \g , or \G , just as if you were entering them
manually. Interactive and batch modes do differ in default output format. For interactive
mode, the default is tabular (boxed) format. For batch mode, the default is tab-delimited
format. To override the default, use the appropriate command option (see Recipe 1.7 ).
Batch mode is convenient for executing a set of statements on repeated occasions
without entering them manually each time. Batch mode makes it easy to set up cron
jobs that run with no user intervention. SQL scripts also are useful for distributing
statements to other people. That is, in fact, how I distribute SQL examples for this topic.
Many of the examples shown here can be run using script files available in the accom‐
panying recipes distribution (see the Preface ). Feed these files to mysql in batch mode
to avoid typing statements yourself. For example, when a recipe shows a CREATE TABLE
statement that defines a table, you'll usually find an SQL batch file in the recipes
distribution that you can use to create (and perhaps load data into) the table. Recall that
Recipe 1.2 shows the statements for creating and populating the limbs table. Those
statements were shown as you would enter them manually, but the tables directory of
the recipes distribution includes a limbs.sql file that contains statements to do the same
thing. The file looks like this: