1.18.1 Problem

You want to specify a query directly on the command line for mysql to execute.

1.18.2 Solution

mysql can read a query from its argument list. Use the -e (or --execute) option to specify a query on the command line.

1.18.3 Discussion

For example, to find out how many records are in the limbs table, run this command:

% mysql -e "SELECT COUNT(*) FROM limbs" cookbook
+----------+
| COUNT(*) |
+----------+
|       12 |
+----------+

To run multiple queries with the -e option, separate them with semicolons:

% mysql -e "SELECT COUNT(*) FROM limbs;SELECT NOW( )" cookbook
+----------+
| COUNT(*) |
+----------+
|       12 |
+----------+
+---------------------+
| NOW( )               |
+---------------------+
| 2001-07-04 10:42:22 |
+---------------------+

1.18.4 See Also

By default, results generated by queries that are specified with -e are displayed in tabular format if output goes to the terminal, and in tab-delimited format otherwise. To produce a different output style

1.17.1 Problem

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

1.17.2 Solution

Use a pipe.

1.17.3 Discussion

An earlier section 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, to 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 is used to generate database backups. It writes a backup as a set of SQL statements that recreate the database, so to process mysqldump output, you 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":

% 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 send its output to mysql using a pipe:

% generate-test-data | mysql cookbook

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

1.15.1 Problem

You want to save a value from a query so you can refer to it in a subsequent query.

1.15.2 Solution

Use a SQL variable to store the value for later use.

1.15.3 Discussion

As of MySQL 3.23.6, you can assign a value returned by a SELECT statement to a variable, then refer to the variable later in your mysql session. This provides a way to save a result returned from one query, then refer to it later in other queries. The syntax for assigning a value to a SQL variable within a SELECT query is @var_name := value, where var_name is the variable name and value is a value that you're retrieving. The variable may be used in subsequent queries wherever an expression is allowed, such as in a WHERE clause or in an INSERT statement.

A common situation in which SQL variables come in handy is when you need to issue successive queries on multiple tables that are related by a common key value. Suppose you have a customers table with a cust_id column that identifies each customer, and an orders table that also has a cust_id column to indicate which customer each order is associated with. If you have a customer name and you want to delete the customer record as well as all the customer's orders, you need to determine the proper cust_id value for that customer, then delete records from both the customers and orders tables that match the ID. One way to do this is to first save the ID value in a variable, then refer to the variable in the DELETE statements

In MySQL 4, you can use multiple-table DELETE statements to accomplish tasks like this with a single query. 

mysql> SELECT @id := cust_id FROM customers WHERE cust_id=' customer name '; 
mysql> DELETE FROM customers WHERE cust_id = @id; 
mysql> DELETE FROM orders WHERE cust_id = @id; 

The preceding SELECT statement assigns a column value to a variable, but variables also can be assigned values from arbitrary expressions. The following statement determines the highest sum of the arms and legs columns in the limbs table and assigns it to the @max_limbs variable:

mysql> SELECT @max_limbs := MAX(arms+legs) FROM limbs;

Another use for a variable is to save the result from LAST_INSERT_ID( ) after creating a new record in a table that has an AUTO_INCREMENT column:

mysql> SELECT @last_id := LAST_INSERT_ID( );

LAST_INSERT_ID( ) returns the value of the new AUTO_INCREMENT value. By saving it in a variable, you can refer to the value several times in subsequent statements, even if you issue other statements that create their own AUTO_INCREMENT values and thus change the value returned by LAST_INSERT_ID( ). 

SQL variables hold single values. If you assign a value to a variable using a statement that returns multiple rows, the value from the last row is used:

mysql> SELECT @name := thing FROM limbs WHERE legs = 0;
+----------------+
| @name := thing |
+----------------+
| squid          |
| octopus        |
| fish           |
| phonograph     |
+----------------+
mysql> SELECT @name;
+------------+
| @name      |
+------------+
| phonograph |
+------------+

If the statement returns no rows, no assignment takes place and the variable retains its previous value. If the variable has not been used previously, that value is NULL:

mysql> SELECT @name2 := thing FROM limbs WHERE legs < 0;
Empty set (0.00 sec)
mysql> SELECT @name2;
+--------+
| @name2 |
+--------+
| NULL   |
+--------+

To set a variable explicitly to a particular value, use a SET  statement. SET syntax uses = rather than := to assign the value:

mysql> SET @sum = 4 + 7;
mysql> SELECT @sum;
+------+
| @sum |
+------+
|   11 |
+------+

A given variable's value persists until you assign it another value or until the end of your mysql session, whichever comes first.

Variable names are case sensitive:

mysql> SET @x = 1; SELECT @x, @X;
+------+------+
| @x   | @X   |
+------+------+
|    1 | NULL |
+------+------+

SQL variables can be used only where expressions are allowed, not where constants or literal identifiers must be provided. Although it's tempting to attempt to use variables for such things as table names, it doesn't work. For example, you might try to generate a temporary table name using a variable as follows, but the result is only an error message:

mysql> SET @tbl_name = CONCAT('tbl_',FLOOR(RAND( )*1000000));
mysql> CREATE TABLE @tbl_name (int_col INT);
ERROR 1064 at line 2: You have an error in your SQL syntax near '@tbl_name
(int_col INT)' at line 1

SQL variables are a MySQL-specific extension, so they will not work with other database engines.