Recipe 1.13. Telling mysql to Read Statements from Other Programs

Problem

You want to shove the output from another program into mysql.

Solution

Use a pipe .

Discussion

Section 1.12 used the following command to show how mysql can read SQL statements from a file:

% mysql cookbook < limbs.sql             

mysql can also read a pipe, which means that it can receive output from other programs as its input. As a trivial example, the preceding command is equivalent to this one:

% cat limbs.sql | mysql cookbook             

Before you tell me that I've qualified for this week's "useless use of cat award," ^[] allow me to observe that you can substitute other commands for cat. The point is that any command that produces output consisting of semicolon-terminated SQL statements can be used as an input source for mysql. This can be useful in many ways. For example, the mysqldump utility generates database backups by writing a set of SQL statements that recreate the database. To process mysqldump output, feed it to mysql. This means you can use the combination of mysqldump and mysql to copy a database over the network to another MySQL server:

^[] Under Windows, the equivalent would be the "useless use of type award":

C:\> type limbs.sql | mysql cookbook                   

% mysqldump cookbook | mysql -h some.other.host.com cookbook             

Program-generated SQL also can be useful when you need to populate a table with test data but don't want to write the INSERT statements by hand. Instead, write a short program that generates the statements, and then send its output to mysql using a pipe:

% generate-test-data | mysql cookbook             

See Also

Chapter 10 discusses mysqldump further.