MySQL Cookbook, 3rd Edition

Chapter 1. Using the mysql Client Program

Introduction

The MySQL database system uses a client-server architecture. The server, mysqld, is the program that actually manipulates databases. To tell the server what to do, use a client program that communicates your intent by means of statements written in Structured Query Language (SQL). Client programs are written for diverse purposes, but each interacts with the server by connecting to it, sending SQL statements to have database operations performed, and receiving the results.

Clients are installed locally on the machine from which you want to access MySQL, but the server can be installed anywhere, as long as clients can connect to it. Because MySQL is an inherently networked database system, clients can communicate with a server running locally on your own machine or somewhere on the other side of the planet.

The mysql program is one of the clients included in MySQL distributions. When used interactively, mysql prompts you for a statement, sends it to the MySQL server for execution, and displays the results. mysql also can be used noninteractively in batch mode to read statements stored in files or produced by programs. This enables use of mysql 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:

• Setting up a MySQL account for using the cookbook database

• Specifying connection parameters and using option files

• Executing SQL statements interactively and in batch mode

• Controlling mysql output format

• Using user-defined variables to save information

To try for yourself the examples shown in this book, you need a MySQL user account and a database. The first two recipes in this chapter describe how to use mysql to set those up, based on these assumptions:

• The MySQL server is running locally on your own system

• Your MySQL username and password are cbuser and cbpass

• Your database is named cookbook

If you like, you can violate any of the assumptions. Your server need not be running locally, and you need not use the username, password, or database name that are used in this book. Naturally, in such cases, you must modify the examples accordingly.

Even if you choose not to use cookbook as your database name, I recommend that you use a database dedicated to the examples shown here, not one that you also use for other purposes. Otherwise, the names of existing tables may conflict with those used in the examples, and you’ll have to make modifications that would be unnecessary with a dedicated database.

Scripts that create the tables used in this chapter are located in the tables directory of the recipes distribution that accompanies MySQL Cookbook. Other scripts are located in the mysql directory. To get the recipes distribution, see the Preface.

Alternatives to the mysql Program

The mysql client is not the only program you can use for executing queries. For example, you might prefer the graphical MySQL Workbench program, which provides a point-and-click interface to MySQL servers. Another popular interface is phpMyAdmin, which enables you to access MySQL through your web browser. If you execute queries other than by using mysql, some concepts covered in this chapter may not apply.

Setting Up a MySQL User Account

Problem

You need an account for connecting to your MySQL server.

Solution

Use CREATE USER and GRANT statements to set up the account. Then use the account name and password to make connections to the server.

Discussion

Connecting to a MySQL server requires a username and password. You may also need to specify the name of the host on which the server is running. If you don’t specify connection parameters explicitly, mysql assumes default values. For example, given no explicit hostname, mysql assumes that the server is running on the local host.

If someone else has already set up an account for you, just use that account. Otherwise, the following example shows how to use the mysql program to connect to the server and issue the statements that set 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, -u root to connect as the MySQL root user, and -p to tell mysql to prompt for a password:

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

If when you attempt to invoke mysql the result is an error message that it cannot be found or is an invalid command, that means your command interpreter doesn’t know where mysql is installed. See What to Do if mysql Cannot Be Found for information about setting the PATH environment variable that the interpreter uses to find commands.

In the commands shown, the % represents the prompt displayed by your shell or command interpreter, and mysql> is the prompt displayed by mysql. Text that you type is shown in bold. Nonbold text (including the prompts) is program output; don’t type any of that.

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 the Enter (or Return) key at the password prompt. Then enter the CREATE USER and GRANT statements as shown.

The quit command terminates your mysql session. You can also terminate a session by using an exit command or (under Unix) by typing Ctrl-D.

To grant the cbuser account access to a database other than cookbook, substitute the database name where you see cookbook in the GRANT statement. To grant access for the cookbook database to an existing account, omit the CREATE USER statement and substitute that account for 'cbuser'@'localhost' in the GRANT statement.

The hostname part of 'cbuser'@'localhost' indicates the host from which you’ll connect to the MySQL server. To set up an account that will connect to a server running on the local host, use localhost, as shown. If you plan to connect to the server from another host, substitute that host in the CREATE USER and GRANT statements. For example, if you’ll connect to the server from a host named myhost.example.com, the statements look like this:

mysql> CREATE USER 'cbuser'@'myhost.example.com' IDENTIFIED BY 'cbpass';
mysql> GRANT ALL ON cookbook.* TO 'cbuser'@'myhost.example.com';

It may have occurred to you that there’s a paradox in the procedure just described: to set up a cbuser account that can connect to the MySQL server, you must first connect to the server so that you can execute the CREATE USER and GRANT statements. I’m assuming that you can already connect as the MySQL root user because CREATE USER and GRANT can be used only by a user such as root that has the administrative privileges needed to set up other user accounts. If you can’t connect to the server as root, ask your MySQL administrator to create the cbuser account for you.

MySQL Accounts and Login Accounts

MySQL accounts differ from login accounts for your operating system. For example, the MySQL root user and the Unix root user are separate and have nothing to do with each other, even though the username is the same in each case. This means they very likely have different passwords. It also means you don’t create new MySQL accounts by creating login accounts for your operating system; use CREATE USER and GRANT instead.

After creating the cbuser account, verify that you can use it to connect to the MySQL server. From the host that was named in the CREATE USER statement, run the following command to do this (the host named after -h should be the host where the MySQL server is running):

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

Now you can proceed to create the cookbook database and tables within it, as described in Creating a Database and a Sample Table. To make it easier to invoke mysql without specifying connection parameters each time, put them in an option file (see Specifying mysql Command Options).

See Also

For additional information about administering MySQL accounts, see Chapter 23.

Creating a Database and a Sample Table

Problem

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

Solution

Use a CREATE DATABASE statement to create the database, a CREATE TABLE statement for each table, and INSERT statements to add rows to the tables.

Discussion

The GRANT statement shown in Setting Up a MySQL User Account sets up privileges for accessing the cookbook database but does not create the database. This section shows how to do that, and also how to create a table and load it with the sample data used for examples in the following sections. Similar instructions apply for creating other tables used elsewhere in this book.

Connect to the MySQL server as shown at the end of Setting Up a MySQL User Account, then create the database like this:

mysql> CREATE DATABASE cookbook;

Now that you have a database, you can create tables in it. First, select cookbook as the default database:

mysql> USE cookbook;

Then create a simple table:

mysql> CREATE TABLE limbs (thing VARCHAR(20), legs INT, arms INT);

And populate it with a few rows:

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('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);

Here’s a tip for entering the INSERT statements more easily: after entering the first one, press the up arrow to recall it, press Backspace (or Delete) a few times to erase characters back to the last open parenthesis, then type the data values for the next statement. Or, to avoid typing the INSERT statements altogether, skip ahead to Executing SQL Statements Read from a File or Program.

The table you just created is named limbs and contains three columns to record the number of legs and arms possessed by various life forms and objects. The physiology of the alien in the last row is such that the proper values for the arms and legs columns cannot be determined; NULL indicates “unknown value.”

Verify that the rows were added to the limbs table by executing a SELECT statement:

mysql> SELECT * FROM limbs;
+--------------+------+------+
| thing        | legs | arms |
+--------------+------+------+
| human        |    2 |    2 |
| insect       |    6 |    0 |
| squid        |    0 |   10 |
| 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 |
+--------------+------+------+

At this point, you’re all set up with a database and a table. For additional information about executing SQL statements, see Executing SQL Statements Interactively and Executing SQL Statements Read from a File or Program.

Note

In this book, statements show SQL keywords such as SELECT or INSERT in uppercase for distinctiveness. That’s only a typographical convention; keywords can be any lettercase.

What to Do if mysql Cannot Be Found

Problem

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

Solution

Add the directory where mysql is installed to your PATH setting. Then you can run mysql from any directory easily.

Discussion

If your shell or command interpreter can’t find mysql when you invoke it, you’ll see some sort of error message. It might 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 command interpreter 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:\Program Files\MySQL\MySQL Server 5.6\bin\mysql"

Typing long pathnames gets tiresome pretty quickly. You can avoid doing so by changing location into the directory where mysql is installed before you run it. But if you do that, you may be tempted to put all your datafiles and SQL batch files in the same directory as mysql, thus unnecessarily cluttering up a location intended only for programs.

A better solution is to modify your PATH search-path environment variable, which specifies directories where the command interpreter looks for commands. Add to the PATH value the directory where mysql is installed. Then you can invoke mysql from any location by entering only its name, which eliminates pathname typing. For instructions on setting your PATH variable, read “Executing Programs from the Command Line” on the companion website (see the Preface).

A significant additional benefit of being able to easily run mysql from anywhere is that you need not put your datafiles in the directory where mysql is located. You’re free to organize your files in a way that makes sense to you, not 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 work files associated with a given database in the appropriate directory. (I point out the importance of PATH here because many newcomers to MySQL aren’t aware of the existence of such a thing, and consequently try to do all their MySQL-related work in the bin directory where mysql is installed.)

On Windows, another way to avoid typing the pathname or changing into the mysql directory is to create a shortcut and place it in a more convenient location such as the desktop. This makes it easy to start mysql simply by opening the shortcut. To specify command options or the startup directory, edit the shortcut’s properties. If you don’t always invoke mysql with the same options, it might be useful to create one shortcut for each set of options you need. For example, create one shortcut to connect as an ordinary user for general work and another to connect as the MySQL root user for administrative purposes.

Specifying mysql Command Options

Problem

When you invoke the mysql program without command options, it exits immediately with an “access denied” message.

Solution

You must specify connection parameters. Do this on the command line, in an option file, or using a mix of the two.

Discussion

If you invoke mysql with no command options, the result may be an “access denied” error. To avoid that, connect to the MySQL server as shown in Setting Up a MySQL User Account, using mysql like this:

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

Each option is the single-dash “short” form: -h and -u to specify the hostname and username, and -p to be prompted for the password. There are also corresponding double-dash “long” forms: --host, --user, and --password. Use them like this:

% mysql --host=localhost --user=cbuser --password
Enter password: cbpass

To see all options that mysql supports, use this command:

% mysql --help

The way you specify command options for mysql also applies to other MySQL programs such as mysqldump and mysqladmin. For example, to generate a dump file named cookbook.sql that contains a backup of the tables in the cookbook database, execute mysqldump like this:

% mysqldump -h localhost -u cbuser -p cookbook > cookbook.sql
Enter password: cbpass

Some operations require an administrative MySQL account. The mysqladmin program can perform operations that are available only to the MySQL root account. For example, to stop the server, invoke mysqladmin as follows:

% mysqladmin -h localhost -u root -p shutdown
Enter password:        ← enter MySQL root account password here

If the value that you use for an option is the same as its default value, you can omit the option. However, there is no default password. If you like, you can specify the password directly on the command line by using -ppassword (with no space between the option and the password) or --password=password. I don’t recommend this because the password is visible to onlookers and, on multiple-user systems, may be discoverable to other users who run tools such as ps that report process information.

Because the default host is localhost, the same value we’ve been specifying explicitly, you can omit the -h (or --host) option from the command line:

% mysql -u cbuser -p

But suppose that you’d really rather not specify any options. How can you get mysql to “just know” what values to use? That’s easy because MySQL programs support option files:

• If you put an option in an option file, you need not specify it on the command line each time you invoke a given program.

• You can mix command-line and option-file options. This enables you to store the most commonly used option values in a file but override them as desired on the command line.

The rest of this section describes these capabilities.

The Meaning of localhost in MySQL

One of the parameters you specify when connecting to a MySQL server is the host where the server is running. Most programs treat the hostname localhost and the IP address 127.0.0.1 as synonyms for “the local host.” Under Unix, MySQL programs behave differently: by convention, they treat the hostname localhost specially and attempt to connect to the local server using a Unix domain socket file. To force a TCP/IP connection to the local server, use the IP address 127.0.0.1 (or ::1 if your system is configured to support IPv6) rather than the hostname localhost. Alternatively, you can specify a --protocol=tcp option to force use of TCP/IP for connecting.

The default port number is 3306 for TCP/IP connections. The pathname for the Unix domain socket varies, although it’s often /tmp/mysql.sock. To name the socket file pathname explicitly, use -S file_name or --socket=file_name.

Specifying connection parameters using option files

To avoid entering options on the command line each time you invoke mysql, put them in an option file for mysql to read automatically. Option files are plain-text files:

• 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 the my.cnf file in the /etc or /etc/mysql directory, or in the etc directory under the MySQL installation directory.

• Under Windows, files you can use include the my.ini or my.cnf file in your MySQL installation directory (for example, C:\Program Files\MySQL\MySQL Server 5.6), your Windows directory (likely C:\WINDOWS), or the C:\ directory.

To see the exact list of permitted option-file locations, invoke mysql --help.

The following example illustrates the format used in MySQL option files:

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

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

With connection parameters listed in the [client] group as just shown, you can connect as cbuser by invoking mysql with no options on the command line:

% mysql

The same holds for other MySQL client programs, such as mysqldump.

MySQL option files have these characteristics:

• Lines are written in groups (or sections). The first line of a group specifies the group name within 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. To specify options for the server, mysqld, put them in a [mysqld] group.

• The usual option group for specifying client connection parameters is [client]. This group actually is used by all the standard MySQL clients. By listing an option in this group, you make it easier to invoke not only mysql, but also other programs such as mysqldump and mysqladmin. Just make sure that any option you put in this group is understood by all client programs. Otherwise, invoking any client that does not understand it results in an “unknown option” error.

• You can define multiple groups in an option file. By convention, MySQL clients look for parameters in the [client] group and in the group named for the program itself. This provides a convenient way to list general client parameters that you want all client programs to use, but you can still specify options that apply only to a particular program. The preceding sample option file illustrates this convention for the mysql program, which gets general connection parameters from the [client] group and also picks up the skip-auto-rehash and pager options from the [mysql] group.

• Within a group, write option lines in name=value format, where name corresponds to an option name (without leading dashes) and value is the option’s value. If an option takes no value (such as skip-auto-rehash), list the name by itself with no trailing =value part.

• In option files, only the long form of an option is permitted, not the short form. For example, on the command line, the hostname can be given using either -h host_name or --host=host_name. In an option file, only host=host_name is permitted.

• Many programs, mysql and mysqld included, have program variables in addition to command options. (For the server, these are called system variables; see Configuring the Server.) Program variables can be specified in option files, just like options. Internally, program variable names use underscores, but in option files, you can write options and variables using dashes or underscores interchangeably. For example, skip-auto-rehash and skip_auto_rehash are equivalent. To set the server’s sql_mode system variable in a [mysqld] option group, sql_mode=value and sql-mode=value are equivalent. (Interchangeability of dash and underscore also applies for options or variables specified on the command line.)

• In option files, spaces are permitted around the = that separates an option name and value. This contrasts with command lines, where no spaces around = are permitted.

• If an option value contains spaces or other special characters, you can quote it using single or double quotes. The pager option illustrates this.

• It’s common to use an option file to specify options for connection parameters (such as host, user, and password). However, the file can list options that have other purposes. The pager option shown for the [mysql] group specifies the paging program that mysql should use for displaying output in interactive mode. It has nothing to do with how the program connects to the server.

• If a parameter appears multiple times in an option file, the last value found takes precedence. Normally, you should list any program-specific groups following the [client] group so that if there is any overlap in the options set by the two groups, the more general options are overridden by the program-specific values.

• Lines beginning with # or ; characters are ignored as comments. Blank lines are ignored, too. # can be used to write comments at the end of option lines, as shown for the pager option.

• Options that specify file or directory pathnames should be written using / as the pathname separator character, even under Windows, which uses \ as the pathname separator. Alternatively, write \ by doubling it as \\ (this is necessary because \ is the MySQL escape character in strings).

To find out which options the mysql program will read from option files, use this command:

% mysql --print-defaults

You can also use the my_print_defaults utility, which takes as arguments the names of the option-file groups that it should read. For example, mysqldump looks in both the [client] and [mysqldump] groups for options. To check which option-file settings are in those groups, use this command:

% my_print_defaults client mysqldump

Mixing command-line and option-file parameters

It’s possible to mix command-line options and options in option files. Perhaps you want to list your username and server host in an option file, but would rather not store your password there. That’s okay; MySQL programs first read your option file to see what connection parameters are listed there, then check the command line for additional parameters. This means you can specify some options one way, and some the other way. For example, you can list your username and hostname in an option file, but use a password option on the command line:

% mysql -p
Enter password:        ← enter your password here

Command-line parameters take precedence over parameters found in your option file, so to override an option file parameter, just specify it on the command line. For example, you can list your regular MySQL username and password in the option-file for general-purpose use. Then, if you must 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 -u root -p
Enter password:        ← enter MySQL root account password here

To explicitly specify “no password” when there is a nonempty password in the option file, use --skip-password on the command line:

% mysql --skip-password

Note

From this point on, I’ll usually show commands for MySQL programs 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.

Protecting option files from other users

On a multiple-user operating system such as Unix, protect the option file located in your home directory to prevent other users from reading it and finding out how to connect to MySQL using your account. Use chmod to make the file private by setting its mode to enable access only by yourself. Either of the following commands do this:

% chmod 600 .my.cnf
% chmod go-rwx .my.cnf

On Windows, you can use Windows Explorer to set file permissions.

Executing SQL Statements Interactively

Problem

You’ve started mysql. Now you want to send SQL statements to the MySQL server to be executed.

Solution

Just type them in, letting mysql know where each one ends. Or specify “one-liners” directly on the command line.

Discussion

When you invoke mysql, it displays a mysql> prompt to tell you that it’s ready for input. To execute an SQL statement at the mysql> prompt, type it in, add a semicolon (;) at the end to signify the end of the statement, and press Enter. An explicit statement terminator is necessary; mysql doesn’t interpret Enter as a terminator because you can 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 statement, even though they are entered differently and terminated differently:

mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2014-04-06 17:43:52 |
+---------------------+
mysql> SELECT
    -> NOW()\g
+---------------------+
| NOW()               |
+---------------------+
| 2014-04-06 17:43:57 |
+---------------------+

For the second statement, mysql changes the prompt from mysql> to -> to let you know that it’s still waiting to see the statement terminator.

The ; and \g statement terminators are not part of the statement itself. They’re conventions used by the mysql program, which recognizes these terminators and strips them from the input before sending the statement to the MySQL server.

Some statements generate output lines that are so long they take up more than one line on your terminal, which can make query results difficult to read. To avoid this problem, generate “vertical” output by terminating the statement with \G rather than with ; or \g. The output shows column values on separate lines:

mysql> SHOW FULL COLUMNS FROM limbs LIKE 'thing'\G
*************************** 1. row ***************************
     Field: thing
      Type: varchar(20)
 Collation: latin1_swedish_ci
      Null: YES
       Key:
   Default: NULL
     Extra:
Privileges: select,insert,update,references
   Comment:

To produce vertical output for all statements executed within a session, invoke mysql with the -E (or --vertical) option. To produce vertical output only for those results that exceed your terminal width, use --auto-vertical-output.

To execute a statement directly from the command line, specify it using the -e (or --execute) option. This is useful for “one-liners.” For example, to count the rows in the limbs table, use this command:

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

To execute multiple statements, separate them with semicolons:

% mysql -e "SELECT COUNT(*) FROM limbs;SELECT NOW()" cookbook
+----------+
| COUNT(*) |
+----------+
|       11 |
+----------+
+---------------------+
| NOW()               |
+---------------------+
| 2014-04-06 17:43:57 |
+---------------------+

mysql can also read statements from a file or from another program (see Executing SQL Statements Read from a File or Program).

Executing SQL Statements Read from a File or Program

Problem

You want mysql to read statements stored in a file so that you need not enter them manually. Or you want mysql to read the output from another program.

Solution

To read a file, redirect mysql’s input, or use the source command. To read from a program, use a pipe.

Discussion

By default, the mysql program reads input interactively from the terminal, but you can feed it statements using other input sources such as a file or program.

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

Statements read from an input file substitute for what you’d normally enter interactively by hand, so they must be terminated with ;, \g, or \G, just as if you were entering them manually. Interactive and batch modes do differ in default output format. For interactive mode, the default is tabular (boxed) format. For batch mode, the default is tab-delimited format. To override the default, use the appropriate command option (see Controlling mysql Output Destination and Format).

Batch mode is convenient for executing a set of statements on repeated occasions without entering them manually each time. 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 I distribute SQL examples for this book. Many of the examples shown here can be run using script files available in the accompanying recipes distribution (see the Preface). Feed these files to mysql in batch mode to avoid typing statements yourself. For example, when a recipe shows a CREATE TABLE statement that defines a table, you’ll usually find an SQL batch file in the recipes distribution that you can use to create (and perhaps load data into) the table. Recall that Creating a Database and a Sample Table shows the statements for creating and populating the limbs table. Those statements were shown as you would enter them manually, but the tables directory of 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('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, change location into the tables directory of the recipes distribution and 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, perhaps making changes to it, confident that you can easily restore it 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, to read a file of SQL statements from within a mysql session, use a source filename command (or \. filename, which is synonymous):

mysql> source limbs.sql;
mysql> \. limbs.sql;

SQL scripts can themselves include source or \. commands to include other scripts. This gives you additional flexibility, but take care to avoid source loops.

A file to be read by mysql need not be written by hand; it could be program generated. For example, the mysqldump utility generates database backups by writing a set of SQL statements that re-create the database. To reload mysqldump output, feed it to mysql. For example, you can copy a database over the network to another MySQL server like this:

% mysqldump cookbook > dump.sql
% mysql -h other-host.example.com cookbook < dump.sql

mysql can also read a pipe, so it can take output from other programs as its input. Any command that produces output consisting of properly terminated SQL statements can be used as an input source for mysql. The dump-and-reload example can be rewritten to connect the two programs directly with a pipe, avoiding the need for an intermediary file:

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

Program-generated SQL also can be useful for populating a table with test data without writing the INSERT statements by hand. Create a program that generates the statements, then send its output to mysql using a pipe:

% generate-test-data | mysql cookbook

Copying a Table Using mysqldump discusses mysqldump further.

Controlling mysql Output Destination and Format

Problem

You want mysql output to go somewhere other than your screen. And you don’t necessarily want the default output format.

Solution

Redirect the output to a file, or use a pipe to send the output to a program. You can also control other aspects of mysql output to produce tabular, tab-delimited, HTML, or XML output; suppress column headers; or make mysql more or less verbose.

Discussion

Unless you send mysql output elsewhere, it goes to your screen. To save output from mysql in a file, use your shell’s redirection capability:

% mysql cookbook > outputfile

If you run mysql interactively with the output redirected, you can’t see what you type, so in this case you usually also read the input from a file (or another program):

% mysql cookbook < inputfile > outputfile

To send the output to another program (for example, to mail query results to someone), use a pipe:

% mysql cookbook < inputfile | mail paul

The rest of this section shows how to control mysql output format.

Producing tabular or tab-delimited output

mysql chooses its default output format by whether it runs interactively or noninteractively. For interactive use, mysql writes output to the terminal using tabular (boxed) format:

% mysql
mysql> SELECT * FROM limbs WHERE legs=0;
+------------+------+------+
| thing      | legs | arms |
+------------+------+------+
| squid      |    0 |   10 |
| fish       |    0 |    0 |
| phonograph |    0 |    1 |
+------------+------+------+
3 rows in set (0.00 sec)

For noninteractive use (when the input or output is redirected), mysql writes tab-delimited output:

% echo "SELECT * FROM limbs WHERE legs=0" | mysql cookbook
thing   legs    arms
squid   0       10
fish    0       0
phonograph      0       1

To override the default output format, use the appropriate command option. Consider this command shown earlier:

% mysql cookbook < inputfile | mail paul

Because mysql runs noninteractively in that context, it produces tab-delimited output, which the mail recipient may find more difficult to read than tabular output. Use the -t (or --table) option to produce more readable tabular output:

% 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.

Producing HTML or XML output

mysql generates an HTML table from each query result set if you use the -H (or --html) option. This enables you to easily produce output for inclusion in a web page that shows a query result. Here’s an example (with line breaks added to make the output easier to read):

% 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>fish</TD><TD>0</TD><TD>0</TD></TR>
<TR><TD>phonograph</TD><TD>0</TD><TD>1</TD></TR>
</TABLE>

The first row of the table contains column headings. If you don’t want a header row, see the next section for instructions.

You can save the output in a file, then view it with a web browser. For example, on Mac OS X, do this:

% mysql -H -e "SELECT * FROM limbs WHERE legs=0" cookbook > limbs.html
% open -a safari limbs.html

To generate an XML document instead of HTML, use the -X (or --xml) option:

% mysql -X -e "SELECT * FROM limbs WHERE legs=0" cookbook
<?xml version="1.0"?>

<resultset statement="select * from limbs where legs=0
">
  <row>
    <field name="thing">squid</field>
    <field name="legs">0</field>
    <field name="arms">10</field>
  </row>

  <row>
    <field name="thing">fish</field>
    <field name="legs">0</field>
    <field name="arms">0</field>
  </row>

  <row>
    <field name="thing">phonograph</field>
    <field name="legs">0</field>
    <field name="arms">1</field>
  </row>
</resultset>

You can reformat XML to suit a variety of purposes by running it through XSLT transforms. This enables you to use the same input to produce many output formats. Here is a basic transform that produces plain-text output showing the original query, plus the row values separated by commas:

<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<!-- mysql-xml.xsl: interpret XML-format output from mysql client -->

<xsl:output method="text"/>

<!-- Process rows in each resultset  -->
<xsl:template match="resultset">
  <xsl:text>Query: </xsl:text>
  <xsl:value-of select="@statement"/>
  <xsl:value-of select="'&#10;'"/>
  <xsl:text>Result set:&#10;</xsl:text>
  <xsl:apply-templates select="row"/>
</xsl:template>

<!-- Process fields in each row  -->
<xsl:template match="row">
  <xsl:apply-templates select="field"/>
</xsl:template>

<!-- Display text content of each field -->
<xsl:template match="field">
  <xsl:value-of select="."/>
  <xsl:choose>
    <xsl:when test="position() != last()">
      <xsl:text>, </xsl:text> <!-- comma after all but last field -->
    </xsl:when>
    <xsl:otherwise>
      <xsl:value-of select="'&#10;'"/> <!-- newline after last field -->
    </xsl:otherwise>
  </xsl:choose>
</xsl:template>

</xsl:stylesheet>

Use the transform like this:

% mysql -X -e "SELECT * FROM limbs WHERE legs=0" cookbook \
    | xsltproc mysql-xml.xsl -
Query: SELECT * FROM limbs WHERE legs=0
Result set:
squid, 0, 10
fish, 0, 0
phonograph, 0, 1

The -H, --html -X, and --xml options produce output only for statements that generate a result set, not for statements such as INSERT or UPDATE.

To write your own programs that generate XML from query results, see Exporting Query Results as XML. To write web scripts that generate HTML from query results, see Chapter 18.

Suppressing column headings in query output

Tab-delimited format is convenient for generating datafiles for 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 that a program named summarize produces descriptive statistics for a column of numbers. If you produce output from mysql to be used with this program, a column header row would throw off the results because summarize would treat it as data. To create output that contains only data values, suppress the header row with the --skip-column-names option:

% mysql --skip-column-names -e "SELECT arms FROM limbs" cookbook | summarize

Specifying the “silent” option (-s or --silent) twice achieves the same effect:

% mysql -ss -e "SELECT arms FROM limbs" cookbook | summarize

Specifying the output column delimiter

In noninteractive mode, mysql separates output columns by tabs and there is no option for specifying the output delimiter. To produce output that uses a different delimiter, postprocess mysql output. Suppose that 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 a utility such as tr or sed. Any of the following commands change tabs to colons (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

The syntax differs among versions of tr; consult your local documentation. Also, some shells use the tab character for special purposes such as filename completion. For such shells, type a literal tab into the command by preceding it with Ctrl-V.

sed is more powerful than tr because it understands regular expressions and permits multiple substitutions. This is useful for producing output in something like comma-separated values (CSV) format, which requires three substitutions:

1. 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 interpreted as column delimiters.

2. Change the tabs to commas.

3. Surround column values with quotes.

sed permits all three substitutions to be performed in a single command line:

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

That’s 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
# csv.pl: convert tab-delimited input to comma-separated values output
while (<>)        # read next input line
{
  s/"/""/g;       # double 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
}

If you name the script csv.pl, use it like this:

% mysql cookbook < inputfile  | perl csv.pl > outputfile

tr and sed normally are unavailable under Windows. Perl may be more suitable as a cross-platform solution because it runs under both Unix and Windows. (On Unix systems, Perl is usually preinstalled. On Windows, it is freely available for you to install.)

Another way to produce CSV output is to use the Perl Text::CSV_XS module, which was designed for that purpose. Writing Your Own Data Export Programs discusses this module and uses it to construct a general-purpose file reformatter.

Controlling mysql’s verbosity level

When you run mysql noninteractively, not only does the default output format change, but it becomes more terse. For example, mysql doesn’t print row counts or indicate how long statements took to execute. To tell mysql to be more verbose, use -v or --verbose, specifying the option multiple times for increasing verbosity. Try the following commands to see how the output differs:

% echo "SELECT NOW()" | mysql
% echo "SELECT NOW()" | mysql -v
% echo "SELECT NOW()" | mysql -vv
% echo "SELECT NOW()" | mysql -vvv

The counterparts of -v and --verbose are -s and --silent, which also can be used multiple times for increased effect.

Using User-Defined Variables in SQL Statements

Problem

You want to use a value in one statement that is produced by an earlier statement.

Solution

Save the value in a user-defined variable to store it for later use.

Discussion

To save a value returned by a SELECT statement, assign it to a user-defined variable. This enables you to refer to it in other statements later in the same session (but not across sessions). User variables are a MySQL-specific extension to standard SQL. They will not work with other database engines.

To assign a value to a user variable within a SELECT statement, use @var_name := value syntax. The variable can be used in subsequent statements wherever an expression is permitted, such as in a WHERE clause or in an INSERT statement.

Here is an example that assigns a value to a user variable, then refers to that variable later. This is a simple way to determine a value that characterizes some row in a table, then select that particular row:

mysql> SELECT @max_limbs := MAX(arms+legs) FROM limbs;
+------------------------------+
| @max_limbs := MAX(arms+legs) |
+------------------------------+
|                          100 |
+------------------------------+
mysql> SELECT * FROM limbs WHERE arms+legs = @max_limbs;
+-----------+------+------+
| thing     | legs | arms |
+-----------+------+------+
| centipede |  100 |    0 |
+-----------+------+------+

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

mysql> SELECT @last_id := LAST_INSERT_ID();

LAST_INSERT_ID() returns the most recent 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(). Managing Multiple Auto-Increment Values Simultaneously discusses this technique further.

User variables hold single values. If a statement returns multiple rows, the value from the last row is assigned:

mysql> SELECT @name := thing FROM limbs WHERE legs = 0;
+----------------+
| @name := thing |
+----------------+
| squid          |
| 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, its 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 can use either := or = as the assignment operator:

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

You can assign a SELECT result to a variable, provided that you write it as a scalar subquery (a query within parentheses that returns a single value):

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

User variable names are not case sensitive:

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

User variables can appear only where expressions are permitted, not where constants or literal identifiers must be provided. It’s tempting to attempt to use variables for such things as table names, but it doesn’t work. For example, if you try to generate a temporary table name using a variable as follows, it fails:

mysql> SET @tbl_name = CONCAT('tmp_tbl_', CONNECTION_ID());
mysql> CREATE TABLE @tbl_name (int_col INT);
ERROR 1064: You have an error in your SQL syntax near '@tbl_name
(int_col INT)'

However, you can generate a prepared SQL statement that incorporates @tbl_name, then execute the result. Generating Unique Table Names shows how.

SET is also used to assign values to stored program parameters and local variables, and to system variables. For examples, see Chapter 9 and Configuring the Server.