By Paul DuBois

Cover | Table of Contents | Colophon

Table of Contents

Chapter 1: Using the mysql Client Program

Content preview·Buy reprint rights for this chapter

The MySQL database system uses a client-server architecture that centers around the server, mysqld. The server is the program that actually manipulates databases. Client programs don't do that directly; rather, they communicate your intent to the server by means of queries written in Structured Query Language (SQL). The client program or programs are installed locally on the machine from which you wish to access MySQL, but the server can be installed anywhere, as long as clients can connect to it. MySQL is an inherently networked database system, so clients can communicate with a server that is running locally on your machine or one that is running somewhere else, perhaps on a machine on the other side of the planet. Clients can be written for many different purposes, but each interacts with the server by connecting to it, sending SQL queries to it to have database operations performed, and receiving the query results from it.

One such client is the mysql program that is included in MySQL distributions. When used interactively, mysql prompts for a query, sends it to the MySQL server for execution, and displays the results. This capability makes mysql useful in its own right, but it's also a valuable tool to help you with your MySQL programming activities. It's often convenient to be able to quickly review the structure of a table that you're accessing from within a script, to try a query before using it in a program to make sure it produces the right kind of output, and so forth. mysql is just right for these jobs. mysql also can be used non-interactively, for example, to read queries from a file or from other programs. This allows you to use it from within scripts or cron jobs or in conjunction with other applications.

This chapter describes mysql's capabilities so that you can use it more effectively. Of course, to try out for yourself the recipes and examples shown in this book, you'll need a MySQL user account and a database to work with. The first two sections of the chapter describe how to use mysql to set these up. For demonstration purposes, the examples assume that you'll use MySQL as follows:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Introduction

Content preview·Buy reprint rights for this chapter

The MySQL database system uses a client-server architecture that centers around the server, mysqld. The server is the program that actually manipulates databases. Client programs don't do that directly; rather, they communicate your intent to the server by means of queries written in Structured Query Language (SQL). The client program or programs are installed locally on the machine from which you wish to access MySQL, but the server can be installed anywhere, as long as clients can connect to it. MySQL is an inherently networked database system, so clients can communicate with a server that is running locally on your machine or one that is running somewhere else, perhaps on a machine on the other side of the planet. Clients can be written for many different purposes, but each interacts with the server by connecting to it, sending SQL queries to it to have database operations performed, and receiving the query results from it.

One such client is the mysql program that is included in MySQL distributions. When used interactively, mysql prompts for a query, sends it to the MySQL server for execution, and displays the results. This capability makes mysql useful in its own right, but it's also a valuable tool to help you with your MySQL programming activities. It's often convenient to be able to quickly review the structure of a table that you're accessing from within a script, to try a query before using it in a program to make sure it produces the right kind of output, and so forth. mysql is just right for these jobs. mysql also can be used non-interactively, for example, to read queries from a file or from other programs. This allows you to use it from within scripts or cron jobs or in conjunction with other applications.

This chapter describes mysql's capabilities so that you can use it more effectively. Of course, to try out for yourself the recipes and examples shown in this book, you'll need a MySQL user account and a database to work with. The first two sections of the chapter describe how to use mysql to set these up. For demonstration purposes, the examples assume that you'll use MySQL as follows:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Setting Up a MySQL User Account

Content preview·Buy reprint rights for this chapter

You need to create an account to use for connecting to the MySQL server running on a given host.

Use the GRANT statement to set up the MySQL user account. Then use that account's name and password to make connections to the server.

Connecting to a MySQL server requires a username and password. You can also specify the name of the host where the server is running. If you don't specify connection parameters explicitly, mysql assumes default values. For example, if you specify no hostname, mysql typically assumes the server is running on the local host.

The following example shows how to use the mysql program to connect to the server and issue a GRANT statement that sets up a user account with privileges for accessing a database named cookbook. The arguments to mysql include -h localhost to connect to the MySQL server running on the local host, -p to tell mysql to prompt for a password, and -u root to connect as the MySQL root user. Text that you type is shown in bold; non-bold text is program output:

% mysql -h localhost -p -u root
Enter password: ******
mysql> GRANT ALL ON cookbook.* TO 'cbuser'@'localhost' IDENTIFIED BY 'cbpass';
Query OK, 0 rows affected (0.09 sec)
mysql> QUIT
Bye

After you enter the mysql command shown on the first line, if you get a message indicating that the program cannot be found or that it is a bad command, see Recipe 1.8. Otherwise, when mysql prints the password prompt, enter the MySQL root password where you see the ******. (If the MySQL root user has no password, just press Return at the password prompt.) Then issue a GRANT statement like the one shown.

To use a database name other than cookbook, substitute its name where you see cookbook in the GRANT statement. Note that you need to grant privileges for the database even if the user account already exists. However, in that case, you'll likely want to omit the IDENTIFIED BY 'cbpass' part of the statement, because otherwise you'll change that account's current password.

The hostname part of 'cbuser'@'localhost' indicates the host from which you'll be connecting to the MySQL server to access the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Creating a Database and a Sample Table

Content preview·Buy reprint rights for this chapter

You want to create a database and to set up tables within it.

Use a CREATE DATABASE statement to create a database, a CREATE TABLE statement for each table you want to use, and INSERT to add records to the tables.

The GRANT statement used in the previous section defines privileges for the cookbook database, but does not create it. You need to create the database explicitly before you can use it. This section shows how to do that, and also how to create a table and load it with some sample data that can be used for examples in the following sections.

After the cbuser account has been set up, verify that you can use it to connect to the MySQL server. Once you've connected successfully, create the database. From the host that was named in the GRANT statement, run the following commands to do this (the host named after -h should be the host where the MySQL server is running):

% mysql -h localhost -p -u cbuser
Enter password: cbpass
mysql> CREATE DATABASE cookbook;
Query OK, 1 row affected (0.08 sec)

Now you have a database, so you can create tables in it. Issue the following statements to select cookbook as the default database, create a simple table, and populate it with a few records:

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

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Starting and Terminating mysql

Content preview·Buy reprint rights for this chapter

You want to start and stop the mysql program.

Invoke mysql from your command prompt to start it, specifying any connection parameters that may be necessary. To leave mysql, use a QUIT statement.

To start the mysql program, try just typing its name at your command-line prompt. If mysql starts up correctly, you'll see a short message, followed by a mysql> prompt that indicates the program is ready to accept queries. To illustrate, here's what the welcome message looks like (to save space, I won't show it in any further examples):

% mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18427 to server version: 3.23.51-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

If mysql tries to start but exits immediately with an "access denied" message, you'll need to specify connection parameters. The most commonly needed parameters are the host to connect to (the host where the MySQL server runs), your MySQL username, and a password. For example:

% mysql -h localhost -p -u cbuser
Enter password: cbpass

In general, I'll show mysql commands in examples with no connection parameter options. I assume that you'll supply any parameters that you need, either on the command line, or in an option file (Recipe 1.5) so that you don't have to type them each time you invoke mysql.

If you don't have a MySQL username and password, you need to obtain permission to use the MySQL server, as described earlier in Recipe 1.2.

The syntax and default values for the connection parameter options are shown in the following table. These options have both a single-dash short form and a double-dash long form.

Parameter type	Option syntax forms	Default value
Hostname	-h hostname--host=hostname	localhost
Username	-u username--user=username	Your login name
Password	-p--password	None

As the table indicates, there is no default password. To supply one, use --password or -p, then enter your password when mysql prompts you for it:

% mysql -p Enter password:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Specifying Connection Parameters by Using Option Files

Content preview·Buy reprint rights for this chapter

You don't want to type connection parameters on the command line every time you invoke mysql.

Put the parameters in an option file.

To avoid entering connection parameters manually, put them in an option file for mysql to read automatically. Under Unix, your personal option file is named .my.cnf in your home directory. There are also site-wide option files that administrators can use to specify parameters that apply globally to all users. You can use /etc/my.cnf or the my.cnf file in the MySQL server's data directory. Under Windows, the option files you can use are C:\my.cnf, the my.ini file in your Windows system directory, or my.cnf in the server's data directory.

Windows may hide filename extensions when displaying files, so a file named my.cnf may appear to be named just my. Your version of Windows may allow you to disable extension-hiding. Alternatively, issue a DIR command in a DOS window to see full names.

The following example illustrates the format used to write MySQL option files:

# general client program connection options
[client]
host=localhost
user=cbuser
password=cbpass

# options specific to the mysql program
[mysql]
no-auto-rehash
# specify pager for interactive mode
pager=/usr/bin/less

This format has the following general characteristics:

• Lines are written in groups. The first line of the group specifies the group name inside of square brackets, and the remaining lines specify options associated with the group. The example file just shown has a [client] group and a [mysql] group. Within a group, option lines are written in name=value format, where name corresponds to an option name (without leading dashes) and value is the option's value. If an option doesn't take any value (such as for the no-auto-rehash option), the name is listed by itself with no trailing =value part.
• If you don't need some particular parameter, just leave out the corresponding line. For example, if you normally connect to the default host (localhost), you don't need any host line. If your MySQL username is the same as your operating system login name, you can omit the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Protecting Option Files

Content preview·Buy reprint rights for this chapter

Your MySQL username and password are stored in your option file, and you don't want other users reading it.

Change the file's mode to make it accessible only by you.

If you use a multiple-user operating system such as Unix, you should protect your option file to prevent other users from finding out how to connect to MySQL using your account. Use chmod to make the file private by setting its mode to allow access only by yourself:

% chmod 600 .my.cnf

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Mixing Command-Line and Option File Parameters

Content preview·Buy reprint rights for this chapter

You'd rather not store your MySQL password in an option file, but you don't want to enter your username and server host manually.

Put the username and host in the option file, and specify the password interactively when you invoke mysql; it looks both in the option file and on the command line for connection parameters. If an option is specified in both places, the one on the command line takes precedence.

mysql first reads your option file to see what connection parameters are listed there, then checks the command line for additional parameters. This means you can specify some options one way, and some the other way.

Command-line parameters take precedence over parameters found in your option file, so if for some reason you need to override an option file parameter, just specify it on the command line. For example, you might list your regular MySQL username and password in the option file for general purpose use. If you need to connect on occasion as the MySQL root user, specify the user and password options on the command line to override the option file values:

% mysql -p -u root

To explicitly specify "no password" when there is a non-empty password in the option file, use -p on the command line, and then just press Return when mysql prompts you for the password:

% mysql -p Enter password: ← press Return here

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

What to Do if mysql Cannot Be Found

Content preview·Buy reprint rights for this chapter

When you invoke mysql from the command line, your command interpreter can't find it.

Add the directory where mysql is installed to your PATH setting. Then you'll be able to run mysql from any directory easily.

If your shell or command interpreter can't find mysql when you invoke it, you'll see some sort of error message. It may look like this under Unix:

% mysql
mysql: Command not found.

Or like this under Windows:

C:\> mysql
Bad command or invalid filename

One way to tell your shell where to find mysql is to type its full pathname each time you run it. The command might look like this under Unix:

% /usr/local/mysql/bin/mysql

Or like this under Windows:

C:\> C:\mysql\bin\mysql

Typing long pathnames gets tiresome pretty quickly, though. You can avoid doing so by changing into the directory where mysql is installed before you run it. However, I recommend that you not do that. If you do, the inevitable result is that you'll end up putting all your datafiles and query batch files in the same directory as mysql, thus unnecessarily cluttering up what should be a location intended only for programs.

A better solution is to make sure that the directory where mysql is installed is included in the PATH environment variable that lists pathnames of directories where the shell looks for commands. (See Recipe 1.9.) Then you can invoke mysql from any directory by entering just its name, and your shell will be able to find it. This eliminates a lot of unnecessary pathname typing. An additional benefit is that because you can easily run mysql from anywhere, you will have no need to put your datafiles in the same directory where mysql is located. When you're not operating under the burden of running mysql from a particular location, you'll be free to organize your files in a way that makes sense to you, not in a way imposed by some artificial necessity. For example, you can create a directory under your home directory for each database you have and put the files associated with each database in the appropriate directory.

I've pointed out the importance of the search path here because I receive many questions from people who aren't aware of the existence of such a thing, and who consequently try to do all their MySQL-related work in the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Setting Environment Variables

Content preview·Buy reprint rights for this chapter

You need to modify your operating environment, for example, to change your shell's PATH setting.

Edit the appropriate shell startup file. Under Windows NT-based systems, another alternative is to use the System control panel.

The shell or command interpreter you use to run programs from the command-line prompt includes an environment in which you can store variable values. Some of these variables are used by the shell itself. For example, it uses PATH to determine which directories to look in for programs such as mysql. Other variables are used by other programs (such as PERL5LIB, which tells Perl where to look for library files used by Perl scripts).

Your shell determines the syntax used to set environment variables, as well as the startup file in which to place the settings. Typical startup files for various shells are shown in the following table. If you've never looked through your shell's startup files, it's a good idea to do so to familiarize yourself with their contents.

Shell	Possible startup files
csh, tcsh	.login, .cshrc, .tcshrc
sh, bash, ksh	.profile .bash_profile, .bash_login, .bashrc
DOS prompt	C:\AUTOEXEC.BAT

The following examples show how to set the PATH variable so that it includes the directory where the mysql program is installed. The examples assume there is an existing PATH setting in one of your startup files. If you have no PATH setting currently, simply add the appropriate line or lines to one of the files.

If you're reading this section because you've been referred here from another chapter, you'll probably be more interested in changing some variable other than PATH. The instructions are similar because you use the same syntax.

The PATH variable lists the pathnames for one or more directories. If an environment variable's value consists of multiple pathnames, it's conventional under Unix to separate them using the colon character (:). Under Windows, pathnames may contain colons, so the separator is the semicolon character ( ;).

To set the value of PATH, use the instructions that pertain to your shell:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Issuing Queries

Content preview·Buy reprint rights for this chapter

You've started mysql and now you want to send queries to the MySQL server.

Just type them in, but be sure to let mysql know where each one ends.

To issue a query at the mysql> prompt, type it in, add a semicolon ( ;) at the end to signify the end of the statement, and press Return. An explicit statement terminator is necessary; mysql doesn't interpret Return as a terminator because it's allowable to enter a statement using multiple input lines. The semicolon is the most common terminator, but you can also use \g ("go") as a synonym for the semicolon. Thus, the following examples are equivalent ways of issuing the same query, even though they are entered differently and terminated differently:

mysql> SELECT NOW( );
+---------------------+
| NOW( )               |
+---------------------+
| 2001-07-04 10:27:23 |
+---------------------+
mysql> SELECT
    -> NOW( )\g
+---------------------+
| NOW( )               |
+---------------------+
| 2001-07-04 10:27:28 |
+---------------------+

Notice for the second query that the prompt changes from mysql> to -> on the second input line. mysql changes the prompt this way to let you know that it's still waiting to see the query terminator.

Be sure to understand that neither the ; character nor the \g sequence that serve as query terminators are part of the query itself. They're conventions used by the mysql program, which recognizes these terminators and strips them from the input before sending the query to the MySQL server. It's important to remember this when you write your own programs that send queries to the server (as we'll begin to do in the next chapter). In that context, you don't include any terminator characters; the end of the query string itself signifies the end of the query. In fact, adding a terminator may well cause the query to fail with an error.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Selecting a Database

Content preview·Buy reprint rights for this chapter

You want to tell mysql which database to use.

Name the database on the mysql command line or issue a USE statement from within mysql.

When you issue a query that refers to a table (as most queries do), you need to indicate which database the table is part of. One way to do so is to use a fully qualified table reference that begins with the database name. (For example, cookbook.limbs refers to the limbs table in the cookbook database.) As a convenience, MySQL also allows you to select a default (current) database so that you can refer to its tables without explicitly specifying the database name each time. You can specify the database on the command line when you start mysql:

% mysql cookbook

If you provide options on the command line such as connection parameters when you run mysql, they should precede the database name:

% mysql -h host -p -u user cookbook

If you've already started a mysql session, you can select a database (or switch to a different one) by issuing a USE statement:

mysql> USE cookbook;
Database changed

If you've forgotten or are not sure which database is the current one (which can happen easily if you're using multiple databases and switching between them several times during the course of a mysql session), use the following statement:

mysql> SELECT DATABASE( );
+------------+
| DATABASE( ) |
+------------+
| cookbook   |
+------------+

DATABASE( ) is a function that returns the name of the current database. If no database has been selected yet, the function returns an empty string:

mysql> SELECT DATABASE( );
+------------+
| DATABASE( ) |
+------------+
|            |
+------------+

The STATUS command (and its synonym, \s) also display the current database name, in additional to several other pieces of information:

mysql> \s
--------------
Connection id:      5589
Current database:   cookbook
Current user:       cbuser@localhost
Current pager:      stdout
Using outfile:      ''
Server version:     3.23.51-log
Protocol version:   10
Connection:     Localhost via UNIX socket
Client characterset:    latin1
Server characterset:    latin1
UNIX socket:        /tmp/mysql.sock
Uptime:         9 days 39 min 43 sec

Threads: 4  Questions: 42265  Slow queries: 0  Opens: 82  Flush tables: 1
Open tables: 52 Queries per second avg: 0.054
--------------

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Canceling a Partially Entered Query

Content preview·Buy reprint rights for this chapter

You start to enter a query, then decide not to issue it after all.

Cancel the query using your line kill character or the \c sequence.

If you change your mind about issuing a query that you're entering, cancel it. If the query is on a single line, use your line kill character to erase the entire line. (The particular character to use depends on your terminal setup; for me, the character is Ctrl-U.) If you've entered a statement over multiple lines, the line kill character will erase only the last line. To cancel the statement completely, enter \c and type Return. This will return you to the mysql> prompt:

mysql> SELECT *
    -> FROM limbs
    -> ORDER BY\c
mysql>

Sometimes \c appears to do nothing (that is, the mysql> prompt does not reappear), which leads to the sense that you're "trapped" in a query and can't escape. If \c is ineffective, the cause usually is that you began typing a quoted string and haven't yet entered the matching end quote that terminates the string. Let mysql's prompt help you figure out what to do here. If the prompt has changed from mysql> to ">, That means mysql is looking for a terminating double quote. If the prompt is '> instead, mysql is looking for a terminating single quote. Type the appropriate matching quote to end the string, then enter \c followed by Return and you should be okay.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Repeating and Editing Queries

Content preview·Buy reprint rights for this chapter

The query you just entered contained an error, and you want to fix it without typing the whole thing again. Or you want to repeat an earlier statement without retyping it.

Use mysql's built-in query editor.

If you issue a long query only to find that it contains a syntax error, what should you do? Type in the entire corrected query from scratch? No need. mysql maintains a statement history and supports input-line editing. This allows you to recall queries so that you can modify and reissue them easily. There are many, many editing functions, but most people tend to use a small set of commands for the majority of their editing. A basic set of useful commands is shown in the following table. Typically, you use Up Arrow to recall the previous line, Left Arrow and Right Arrow to move around within the line, and Backspace or Delete to erase characters. To add new characters to the line, just move the cursor to the appropriate spot and type them in. When you're done editing, press Return to issue the query (the cursor need not be at the end of the line when you do this).

Editing Key	Effect of Key
Up Arrow	Scroll up through statement history
Down Arrow	Scroll down through statement history
Left Arrow	Move left within line
Right Arrow	Move right within line
Ctrl-A	Move to beginning of line
Ctrl-E	Move to end of line
Backspace	Delete previous character
Ctrl-D	Delete character under cursor

Input-line editing is useful for more than just fixing mistakes. You can use it to try out variant forms of a query without retyping the entire thing each time. It's also handy for entering a series of similar statements. For example, if you wanted to use the query history to issue the series of INSERT statements shown earlier in Recipe 1.3 to create the limbs table, first enter the initial INSERT statement. Then, to issue each successive statement, press the Up Arrow key to recall the previous statement with the cursor at the end, backspace back through the column values to erase them, enter the new values, and press Return.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Using Auto-Completion for Database and Table Names

Content preview·Buy reprint rights for this chapter

You wish there was a way to type database and table names more quickly.

There is; use mysql's name auto-completion facility.

Normally when you use mysql interactively, it reads the list of database names and the names of the tables and columns in your current database when it starts up. mysql remembers this information to provide name completion capabilities that are useful for entering statements with fewer keystrokes:

• Type in a partial database, table, or column name and then hit the Tab key.
• If the partial name is unique, mysql completes it for you. Otherwise, you can hit Tab again to see the possible matches.
• Enter additional characters and hit Tab again once to complete it or twice to see the new set of matches.

mysql's name auto-completion capability is based on the table names in the current database, and thus is unavailable within a mysql session until a database has been selected, either on the command line or by means of a USE statement.

Auto-completion allows you to cut down the amount of typing you do. However, if you don't use this feature, reading name-completion information from the MySQL server may be counterproductive because it can cause mysql to start up more slowly when you have a lot of tables in your database. To tell mysql not to read this information so that it starts up more quickly, specify the -A (or --no-auto-rehash) option on the mysql command line. Alternatively, put a no-auto-rehash line in the [mysql] group of your MySQL option file:

[mysql]
no-auto-rehash

To force mysql to read name completion information even if it was invoked in no-completion mode, issue a REHASH or \# command at the mysql> prompt.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Using SQL Variables in Queries

Content preview·Buy reprint rights for this chapter

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

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

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:

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:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Telling mysql to Read Queries from a File

Content preview·Buy reprint rights for this chapter

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

Redirect mysql's input or use the SOURCE command.

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

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Telling mysql to Read Queries from Other Programs

Content preview·Buy reprint rights for this chapter

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

Use a pipe.

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:

% 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

mysqldump is discussed further in Chapter 10.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Specifying Queries on the Command Line

Content preview·Buy reprint rights for this chapter

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

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

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 |
+---------------------+

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, see Recipe 1.22.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Using Copy and Paste as a mysql Input Source

Content preview·Buy reprint rights for this chapter

You want to take advantage of your graphical user interface (GUI) to make mysql easier to use.

Use copy and paste to supply mysql with queries to execute. In this way, you can take advantage of your GUI's capabilities to augment the terminal interface presented by mysql.

Copy and paste is useful in a windowing environment that allows you to run multiple programs at once and transfer information between them. If you have a document containing queries open in a window, you can just copy the queries from there and paste them into the window in which you're running mysql. This is equivalent to typing the queries yourself, but often quicker. For queries that you issue frequently, keeping them visible in a separate window can be a good way to make sure they're always at your fingertips and easily accessible.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Preventing Query Output from Scrolling off the Screen

Content preview·Buy reprint rights for this chapter

Query output zooms off the top of your screen before you can see it.

Tell mysql to display output a page at a time, or run mysql in a window that allows scrollback.

If a query produces many lines of output, normally they just scroll right off the top of the screen. To prevent this, tell mysql to present output a page at a time by specifying the --pager option. --pager=program tells mysql to use a specific program as your pager:

% mysql --pager=/usr/bin/less

--pager by itself tells mysql to use your default pager, as specified in your PAGER environment variable:

% mysql --pager

If your PAGER variable isn't set, you must either define it or use the first form of the command to specify a pager program explicitly. To define PAGER, use the instructions in Recipe 1.9 for setting environment variables.

Within a mysql session, you can turn paging on and off using \P and \n. \P without an argument enables paging using the program specified in your PAGER variable. \P with an argument enables paging using the argument as the name of the paging program:

mysql> \P
PAGER set to /bin/more
mysql> \P /usr/bin/less
PAGER set to /usr/bin/less
mysql> \n
PAGER set to stdout

Output paging was introduced in MySQL 3.23.28.

Another way to deal with long result sets is to use a terminal program that allows you to scroll back through previous output. Programs such as xterm for the X Window System, Terminal for Mac OS X, MacSSH or BetterTelnet for Mac OS, or Telnet for Windows allow you to set the number of output lines saved in the scrollback buffer. Under Windows NT, 2000, or XP, you can set up a DOS window that allows scrollback using the following procedure:

1. Open the Control Panel.
2. Create a shortcut to the MS-DOS prompt by right clicking on the Console item and dragging the mouse to where you want to place the shortcut (on the desktop, for example).
3. Right click on the shortcut and select the Properties item from the menu that appears.
4. Select the Layout tab in the resulting Properties window.
5. Set the screen buffer height to the number of lines you want to save and click the OK button.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Sending Query Output to a File or to a Program

Content preview·Buy reprint rights for this chapter

You want to send mysql output somewhere other than to your screen.

Redirect mysql's output or use a pipe.

mysql chooses its default output format according to whether you run it interactively or non-interactively. Under interactive use, mysql normally sends its output to the terminal and writes query results using tabular format:

mysql> SELECT * FROM limbs;
+--------------+------+------+
| thing        | legs | arms |
+--------------+------+------+
| human        |    2 |    2 |
| insect       |    6 |    0 |
| squid        |    0 |   10 |
| octopus      |    0 |    8 |
| fish         |    0 |    0 |
| centipede    |  100 |    0 |
| table        |    4 |    0 |
| armchair     |    4 |    2 |
| phonograph   |    0 |    1 |
| tripod       |    3 |    0 |
| Peg Leg Pete |    1 |    2 |
| space alien  | NULL | NULL |
+--------------+------+------+
12 rows in set (0.00 sec)

In non-interactive mode (that is, when either the input or output is redirected), mysql writes output in tab-delimited format:

% echo "SELECT * FROM limbs" | mysql cookbook
thing   legs    arms
human   2       2
insect  6       0
squid   0       10
octopus 0       8
fish    0       0
centipede       100     0
table   4       0
armchair        4       2
phonograph      0       1
tripod  3       0
Peg Leg Pete    1       2
space alien     NULL    NULL

However, in either context, you can select any of mysql's output formats by using the appropriate command-line options. This section describes how to send mysql output somewhere other than the terminal. The next several sections discuss the various mysql output formats and how to select them explicitly according to your needs when the default format isn't what you want.

To save output from mysql in a file, use your shell's standard redirection capability:

% mysql cookbook > outputfile

However, if you try to run mysql interactively with the output redirected, you won't be able to see what you're typing, so generally in this case you'll also take query input from a file (or another program):

% mysql cookbook < inputfile > outputfile

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Selecting Tabular or Tab-Delimited Query Output Format

Content preview·Buy reprint rights for this chapter

mysql produces tabular output when you want tab-delimited output, or vice versa.

Select the desired format explicitly with the appropriate command-line option.

When you use mysql non-interactively (such as to read queries from a file or to send results into a pipe), it writes output in tab-delimited format by default. Sometimes it's desirable to produce tabular output instead. For example, if you want to print or mail query results, tab-delimited output doesn't look very nice. Use the -t (or --table) option to produce tabular output that is more readable:

% mysql -t cookbook < inputfile | lpr
% mysql -t cookbook < inputfile | mail paul

The inverse operation is to produce batch (tab-delimited) output in interactive mode. To do this, use -B or --batch.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Specifying Arbitrary Output Column Delimiters

Content preview·Buy reprint rights for this chapter

You want mysql to produce query output using a delimiter other than tab.

Postprocess mysql's output.

In non-interactive mode, mysql separates output columns with tabs and there is no option for specifying the output delimiter. Under some circumstances, it may be desirable to produce output that uses a different delimiter. Suppose you want to create an output file for use by a program that expects values to be separated by colon characters (:) rather than tabs. Under Unix, you can convert tabs to arbitrary delimiters by using utilities such as tr and sed. For example, to change tabs to colons, any of the following commands would work (TAB indicates where you type a tab character):

% mysql cookbook < inputfile  | sed -e "s/TAB/:/g" > outputfile
% mysql cookbook < inputfile  | tr "TAB" ":" > outputfile
% mysql cookbook < inputfile  | tr "\011" ":" > outputfile

sed is more powerful than tr because it understands regular expressions and allows multiple substitutions. This is useful when you want to produce output in something like comma-separated values (CSV) format, which requires three substitutions:

• Escape any quote characters that appear in the data by doubling them so that when you use the resulting CSV file, they won't be taken as column delimiters.
• Change the tabs to commas.
• Surround column values with quotes.

sed allows all three subsitutions to be performed in a single command:

% mysql cookbook < inputfile  \
    | sed -e 's/"/""/g' -e 's/TAB/","/g' -e 's/^/"/' -e 's/$/"/' > outputfile

That's fairly cryptic, to say the least. You can achieve the same result with other languages that may be easier to read. Here's a short Perl script that does the same thing as the sed command (it converts tab-delimited input to CSV output), and includes comments to document how it works:

#! /usr/bin/perl -w
while (<>)              # read next input line
{
    s/"/""/g;           # double any quotes within column values
    s/\t/","/g;         # put `","' between column values
    s/^/"/;             # add `"' before the first value
    s/$/"/;             # add `"' after the last value
    print;              # print the result
}
exit (0);

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Producing HTML Output

Content preview·Buy reprint rights for this chapter

You'd like to turn a query result into HTML.

mysql can do that for you.

mysql generates result set output as HTML tables if you use -H (or --html) option. This gives you a quick way to produce sample output for inclusion into a web page that shows what the result of a query looks like. Here's an example that shows the difference between tabular format and HTML table output (a few line breaks have been added to the HTML output to make it easier to read):

% mysql -e "SELECT * FROM limbs WHERE legs=0" cookbook
+------------+------+------+
| thing      | legs | arms |
+------------+------+------+
| squid      |    0 |   10 |
| octopus    |    0 |    8 |
| fish       |    0 |    0 |
| phonograph |    0 |    1 |
+------------+------+------+
% mysql -H -e "SELECT * FROM limbs WHERE legs=0" cookbook
<TABLE BORDER=1>
<TR><TH>thing</TH><TH>legs</TH><TH>arms</TH></TR>
<TR><TD>squid</TD><TD>0</TD><TD>10</TD></TR>
<TR><TD>octopus</TD><TD>0</TD><TD>8</TD></TR>
<TR><TD>fish</TD><TD>0</TD><TD>0</TD></TR>
<TR><TD>phonograph</TD><TD>0</TD><TD>1</TD></TR>
</TABLE>

The first line of the table contains column headings. If you don't want a header row, see Recipe 1.26.

The -H and --html options produce output only for queries that generate a result set. No output is written for queries such as INSERT or UPDATE statements.

-H and --html may be used as of MySQL 3.22.26. (They actually were introduced in an earlier version, but the output was not quite correct.)

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Producing XML Output

Content preview·Buy reprint rights for this chapter

You'd like to turn a query result into XML.

mysql can do that for you.

mysql creates an XML document from the result of a query if you use the -X (or --xml) option. Here's an example that shows the difference between tabular format and the XML created from the same query:

% mysql -e "SELECT * FROM limbs WHERE legs=0" cookbook
+------------+------+------+
| thing      | legs | arms |
+------------+------+------+
| squid      |    0 |   10 |
| octopus    |    0 |    8 |
| fish       |    0 |    0 |
| phonograph |    0 |    1 |
+------------+------+------+
% mysql -X -e "SELECT * FROM limbs WHERE legs=0" cookbook
<?xml version="1.0"?>

<resultset statement="SELECT * FROM limbs WHERE legs=0">
  <row>
    <thing>squid</thing>
    <legs>0</legs>
    <arms>10</arms>
  </row>

  <row>
    <thing>octopus</thing>
    <legs>0</legs>
    <arms>8</arms>
  </row>

  <row>
    <thing>fish</thing>
    <legs>0</legs>
    <arms>0</arms>
  </row>

  <row>
    <thing>phonograph</thing>
    <legs>0</legs>
    <arms>1</arms>
  </row>
</resultset>

-X and --xml may be used as of MySQL 4.0. If your version of MySQL is older than that, you can write your own XML generator. See Recipe 10.42.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Suppressing Column Headings in Query Output

Content preview·Buy reprint rights for this chapter

You don't want to include column headings in query output.

Turn column headings off with the appropriate command-line option. Normally this is -N or --skip-column-names, but you can use -ss instead.

Tab-delimited format is convenient for generating datafiles that you can import into other programs. However, the first row of output for each query lists the column headings by default, which may not always be what you want. Suppose you have a program named summarize the p