MySQL Cookbook

Telling mysql to Read Queries from a File

Problem

You want mysql to read queries stored in a file so 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 queries 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 query input. This section discusses how to read queries from a file. The next few sections discuss how to take input from other sources.

To create a 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-line options. See the section on selecting tabular or tab-delimited format later in the chapter (Recipe 1.22).

Batch mode is convenient when you need to issue a given set of statements on multiple occasions, because then you need not enter ...