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. For example, 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 SQL examples from this book are distributed. Many of the examples shown here can be run using script files that are available in the accompanying recipes source distribution. (See Appendix A.) You can feed these files to mysql in batch mode to avoid typing statements yourself. For example, when a recipe shows a CREATE TABLE statement that describes what a particular table looks like, you’ll usually find an SQL batch file in the recipes distribution that can be used to create (and perhaps load data into) the table. Recall that Creating a Database and a Sample Table showed the statements for creating and populating the limbs table. Those statements were shown as you would enter them manually, but the recipes distribution includes a limbs.sql file 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 location into the tables directory of the recipes distribution that contains the table-creation scripts, and 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 the table anew and loading it with data. That enables 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. Alternatively, you can read a file of SQL statements from within a mysql session using a SOURCE filename command (or \. filename, which is synonymous). Suppose that 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()               |
+---------------------+
| 2006-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. This gives you additional flexibility but also raises the danger that it’s possible to create a source loop. Normally, you should take care to avoid such loops. If you’re feeling mischievous and want to create a loop 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
OS error code 24:  Too many open files

In other words, you have hit the limit imposed by the operating system on the number of open files you can have.