MySQL Cookbook, 2nd Edition

Telling mysql to Read Statements from a File

Problem

You want mysql to read statements stored in a file so that you don’t have to enter them manually.

Solution

Redirect mysql’s input, or use the SOURCE command.

Discussion

By default, the mysql program reads input interactively from the terminal, but you can feed it statements in batch mode using other input sources such as a file, another program, or the command arguments. You can also use copy and paste as a source of statement input. This section discusses how to read statements from a file. The next few sections discuss how to take input from other sources.

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 < 
               filename

Statements that are read from an input file substitute for what you’d normally type in by hand, so they must be terminated with semicolons (or \g), just as if you were entering them manually. One difference between interactive and batch modes is the default output style. For interactive mode, the default is tabular (boxed) format. For batch mode, the default is to delimit column values with tabs. However, you can select whichever output style you want using the appropriate command option. See Selecting Tabular or Tab-Delimited Query Output Format.

Batch mode is convenient when you need to issue a given set of statements on multiple occasions because you need not enter them manually each time. ...