Telling mysql to Read Queries from a File

1.16.1 Problem

You want mysql to read queries stored in a file so you don't have to enter them manually.

1.16.2 Solution

Redirect mysql's input or use the SOURCE command.

1.16.3 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 them manually each time. For example, batch mode makes it easy to set up cron jobs that run with no user intervention. SQL scripts are also useful for distributing queries to other people. Many of the examples shown in this book can be run using script files that are available as part of the accompanying recipes source distribution (see Appendix A). You can feed these files to mysql in batch mode to avoid typing queries yourself. A common instance of this is that when an example shows a CREATE TABLE statement that describes what a particular table looks like, you'll find a SQL batch file in the distribution that can be used to create (and perhaps load data into) the table. For example, earlier in the chapter, statements for creating and populating the limbs table were shown. The recipes distribution includes a file limbs.sql that contains statements to do the same thing. The file looks like this:

DROP TABLE IF EXISTS limbs;
CREATE TABLE limbs
(
 thing VARCHAR(20), # what the thing is
 legs INT, # number of legs it has
 arms INT # number of arms it has
);

INSERT INTO limbs (thing,legs,arms) VALUES('human',2,2);
INSERT INTO limbs (thing,legs,arms) VALUES('insect',6,0);
INSERT INTO limbs (thing,legs,arms) VALUES('squid',0,10);
INSERT INTO limbs (thing,legs,arms) VALUES('octopus',0,8);
INSERT INTO limbs (thing,legs,arms) VALUES('fish',0,0);
INSERT INTO limbs (thing,legs,arms) VALUES('centipede',100,0);
INSERT INTO limbs (thing,legs,arms) VALUES('table',4,0);
INSERT INTO limbs (thing,legs,arms) VALUES('armchair',4,2);
INSERT INTO limbs (thing,legs,arms) VALUES('phonograph',0,1);
INSERT INTO limbs (thing,legs,arms) VALUES('tripod',3,0);
INSERT INTO limbs (thing,legs,arms) VALUES('Peg Leg Pete',1,2);
INSERT INTO limbs (thing,legs,arms) VALUES('space alien',NULL,NULL);

To execute the statements in this SQL script file in batch mode, change directory into the tables directory of the recipes distribution where the table-creation scripts are located, then run this command:

% mysql cookbook < limbs.sql

You'll note that the script contains a statement to drop the table if it exists before creating it anew and loading it with data. That allows you to experiment with the table without worrying about changing its contents, because you can restore the table to its baseline state any time by running the script again.

The command just shown illustrates how to specify an input file for mysql on the command line. As of MySQL 3.23.9, you can read a file of SQL statements from within a mysql session by using a SOURCE filename command (or . filename, which is synonymous). Suppose the SQL script file test.sql contains the following statements:

SELECT NOW( );
SELECT COUNT(*) FROM limbs;

You can execute that file from within mysql as follows:

mysql> SOURCE test.sql;
+---------------------+
| NOW( ) |
+---------------------+
| 2001-07-04 10:35:08 |
+---------------------+
1 row in set (0.00 sec)
+----------+
| COUNT(*) |
+----------+
| 12 |
+----------+
1 row in set (0.01 sec)

SQL scripts can themselves include SOURCE or . commands to include other scripts. The danger of this is that it's possible to create a source loop. Normally you should take care to avoid such loops, but if you're feeling mischievous and want to create one deliberately to find out how deep mysql can nest input files, here's how to do it. First, issue the following two statements manually to create a counter table to keep track of the source file depth and initialize the nesting level to zero:

mysql> CREATE TABLE counter (depth INT);
mysql> INSERT INTO counter SET depth = 0;

Then create a script file loop.sql that contains the following lines (be sure each line ends with a semicolon):

UPDATE counter SET depth = depth + 1;
SELECT depth FROM counter;
SOURCE loop.sql;

Finally, invoke mysql and issue a SOURCE command to read the script file:

% mysql cookbook
mysql> SOURCE loop.sql;

The first two statements in loop.sql increment the nesting counter and display the current depth value. In the third statement, loop.sql sources itself, thus creating an input loop. You'll see the output whiz by, with the counter display incrementing each time through the loop. Eventually mysql will run out of file descriptors and stop with an error:

ERROR:
Failed to open file 'loop.sql', error: 24

What is error 24? Find out by using MySQL's perror (print error) utility:

% perror 24
Error code 24: Too many open files