Chapter 1. Using the mysql Client Program

1.0 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 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 can also be used noninteractively in batch mode to read statements stored in files or produced by programs. This enables the 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 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 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, we 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.

1.1 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 and granted you privileges to create and modify the cookbook database, 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: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 54117
Server version: 8.0.27 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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> GRANT PROCESS ON *.* to  `cbuser`@`localhost` ;
Query OK, 0 rows affected (0,01 sec)
mysql> quit
Bye
Tip

The PROCESS privilege is required if you need to generate a dump file of your MySQL data. See also Recipe 1.4.

If you attempt to invoke mysql and receive 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 Recipe 1.3 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. You will see the MySQL welcome prompt, which could be slightly different for the MySQL version you use. 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.

Note

The MySQL user account record contains two parts: the username and the host. The username is an identifier or the user who is accessing the MySQL server. You can specify anything for this part. The hostname is the IP address or name of the host from which this user will connect to the MySQL server. We discuss the MySQL security model and user accounts in Recipe 24.0.

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.

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 Recipe 1.2. To make it easier to invoke mysql without specifying connection parameters each time, put them in an option file (see Recipe 1.4).

See Also

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

1.2 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 Recipe 1.1 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 Recipe 1.1, 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, PRIMARY KEY(thing));

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',99,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);
Tip

To enter 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 Recipe 1.6.

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.

The PRIMARY KEY clause defines the primary key that uniquely identifies the table row. This prevents inserting ambiguous data into the table and also helps MySQL to perform queries faster. We discuss ambiguous data in Chapter 18 and performance issues in Chapter 21.

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    |   99 |    0 |
| table        |    4 |    0 |
| armchair     |    4 |    2 |
| phonograph   |    0 |    1 |
| tripod       |    3 |    0 |
| Peg Leg Pete |    1 |    2 |
| space alien  | NULL | NULL |
+--------------+------+------+
11 rows in set (0,01 sec)

At this point, you’re all set up with a database and a table. For additional information about executing SQL statements, see Recipes 1.5 and 1.6.

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 letter case.

1.3 Finding mysql Client

Problem

When you invoke mysql client 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.exe
'mysql.exe' is not recognized as an internal or external command,↩
operable program or batch file.

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 8.0\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 GitHub repository (see the Preface).

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.

1.4 Specifying mysql Command Options

Problem

When you invoke the mysql program without command options, it exits immediately with an error 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 Recipe 1.1, 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.

Warning

We 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 or can read content of your shell history file.

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.

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 8.0), 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 -i" # 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.

Warning

The password option is stored in the configuration file in plain text format, and any user who has access to this file can read it. If you want to secure the connection credentials, you should use mysql_config_editor to store them securely.

mysql_config_editor stores connection credentials in a file, named .mylogin.cnf, located in your home directory under Unix and in the %APPDATA%\MySQL directory under Windows. It supports only the connection parameters host, user, password, and socket. The --login-path option specifies a group under which credentials are stored. The default is [client].

Following is an example of using mysql_config_editor to create an encrypted login file:

$ mysql_config_editor set --login-path=client \
> --host=localhost --user=cbuser --password
Enter password: cbpass

# print stored credentials
$ mysql_config_editor print --all
[client]
user = cbuser
password = *****
host = localhost

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 is actually 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 Recipe 22.1.) 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. (The interchangeability of the 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. We do not recommend putting password into the option file, because it is stored as plain text and could be discovered by users who have filesystem access to the configuration file while not necessary having access to the MySQL installation.

  • 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, we’ll usually show commands for MySQL programs with no connection-parameter options. We assume that you’ll supply any parameters 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.

1.5 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. Alternatively, specify one-liners directly on the command line.

Discussion

When you invoke mysql, by default, it displays a mysql> prompt to tell you that it’s ready for input. To execute a 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>  USE cookbook
mysql> SHOW FULL COLUMNS FROM limbs LIKE 'thing'\G
*************************** 1. row ***************************
     Field: thing
      Type: varchar(20)
 Collation: utf8mb4_0900_ai_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 Recipe 1.6).

1.6 Executing SQL Statements Read from a File or Program

Problem

You want mysql to read statements stored in a file so you don’t have to 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.

For this purpose, MySQL supports batch mode, which 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.

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 < 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 the tabular (boxed) format. For batch mode, the default is the tab-delimited format. To override the default, use the appropriate command option (see Recipe 1.7).

SQL scripts also are useful for distributing sets of SQL statements to other people. That is, in fact, how we 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 a SQL batch file in the recipes distribution that you can use to create (and perhaps load data into) the table. Recall that Recipe 1.2 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
  PRIMARY KEY(thing)
);

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',99,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 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 can also 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

Recipe 6.6 discusses mysqldump further.

1.7 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 parse the output of the query), use a pipe:

$ mysql cookbook < inputfile | sed -e "s/\t/:/g" > outputfile

The rest of this section shows how to control the 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 the tabular (boxed) format:

$ mysql cookbook
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 a sed command, shown earlier, and change its parameters to obfuscate the output:

$ mysql cookbook < inputfile | sed -e "s/table/XXXXX/g" 
$ mysql cookbook -e "SELECT * FROM limbs where legs=4" | ↩
        sed -e "s/table/XXXXX/g"
 thing legs arms 
 XXXXX 4 0 
 armchair 4 2

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

$ mysql cookbook -t -e "SELECT * FROM limbs where legs=4" | ↩
        sed -e "s/table/XXXXX/g"

+----------+------+------+
| thing    | legs | arms |
+----------+------+------+
| XXXXX    |    4 |    0 |
| armchair |    4 |    2 |
+----------+------+------+

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.

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 Recipe 13.15.

Suppressing column headings in query output

The 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 the 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. The cvt_file.pl utility, available in the recipes distribution, uses this module 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.

1.8 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(arms+legs) INTO @max_limbs FROM limbs;
Query OK, 1 row affected (0,01 sec)
mysql> SELECT * FROM limbs WHERE arms+legs = @max_limbs;
+-----------+------+------+
| thing     | legs | arms |
+-----------+------+------+
| centipede |   99 |    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(). Recipe 15.10 discusses this technique further.

User variables hold single values. If a statement returns multiple rows, the statement will fail with an error, but the value from the first row is assigned:

mysql> SELECT thing FROM limbs WHERE legs = 0;
+------------+
| thing      |
+------------+
| squid      |
| fish       |
| phonograph |
+------------+
3 rows in set (0,00 sec)

mysql> SELECT thing INTO @name FROM limbs WHERE legs = 0;
ERROR 1172 (42000): Result consisted of more than one row
mysql> SELECT @name;
+-------+
| @name |
+-------+
| squid |
+-------+

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 thing INTO @name2 FROM limbs WHERE legs < 0;
Query OK, 0 rows affected, 1 warning (0,00 sec)

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------------+
| Level   | Code | Message                                             |
+---------+------+-----------------------------------------------------+
| Warning | 1329 | No data - zero rows fetched, selected, or processed |
+---------+------+-----------------------------------------------------+
1 row in set (0,00 sec)

mysql> select @name2;
+--------+
| @name2 |
+--------+
| NULL   |
+--------+
1 row in set (0,00 sec)
Tip

The SQL SHOW WARNINGS command returns informational messages about recoverable errors, such as assigning an empty result to a variable or the use of a deprecated feature.

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 (42000): You have an error in your SQL syntax; ↩
check the manual that corresponds to your MySQL server version for ↩
the right syntax to use near '@tbl_name (int_col INT)' at line 1

However, you can generate a prepared SQL statement that incorporates @tbl_name, then execute the result. Recipe 6.4 shows how.

SET is also used to assign values to stored program parameters, local variables, and system variables. For examples, see Chapter 11 and Recipe 22.1.

1.9 Customizing a mysql Prompt

Problem

You opened several connections in different terminal windows and want to visually distinguish them.

Solution

Set a mysql prompt to a custom value.

Discussion

You can customize a mysql prompt by providing the --prompt option on start: 

$ mysql --prompt="MySQL Cookbook> "
MySQL Cookbook>

If the client has already been started, you can use the prompt command to change it interactively: 

mysql> prompt MySQL Cookbook> 
PROMPT set to 'MySQL Cookbook> '
MySQL Cookbook>

The command prompt, like other mysql commands, supports a short version: \R: 

mysql> \R MySQL Cookbook> 
PROMPT set to 'MySQL Cookbook> '
MySQL Cookbook>

To specify the prompt value in the configuration file, put the prompt option under the [mysql] section: 

[mysql]
prompt="MySQL Cookbook> "

Quotes are optional and required only when you want to have special characters, such as a space at the end of the prompt string.

Finally, you can specify a prompt using the environment variable MYSQL_PS1: 

$ export MYSQL_PS1="MySQL Cookbook> "
$ mysql
MySQL Cookbook>

To reset a prompt to its default value, run the prompt command without arguments: 

MySQL Cookbook> prompt
Returning to default PROMPT of mysql> 
mysql>
Tip

If you used the MYSQL_PS1 environment variable, the prompt default will be the value of the MYSQL_PS1 variable instead of mysql.

The mysql prompt is highly customizable. You can set it to show the current date, time, user account, default database, server host, and other information about your database connection. You will find the full list of supported options in the MySQL User Reference Manual.

To have a user account in the prompt, use either the special sequence \u to display just a user name or \U to show the full user account: 

mysql> prompt \U> 
PROMPT set to '\U> '
cbuser@localhost>

If you connect to MySQL servers on different machines, you may want to see the MySQL server host name in the prompt. A special sequence, \h, exists just for this: 

mysql> \R \h> 
PROMPT set to '\h> '
Delly-7390>

To have the current default database in the prompt, use the special sequence \d: 

mysql> \R \d> 
PROMPT set to '\d> '
(none)> use cookbook
Database changed
cookbook>

mysql supports multiple options to include time into the prompt. You can have full date and time information or just part of it: 

mysql> prompt \R:\m:\s> 
PROMPT set to '\R:\m:\s> '
15:30:10>
15:30:10> prompt \D> 
PROMPT set to '\D> '
Sat Sep 19 15:31:19 2020>

Warning

You cannot specify the current day of the month unless you use the full current date. This was reported at MySQL Bug #72071 and is still not fixed.

Special sequences can be combined together and with any other text, mysql uses the UTF-8 character set, and, if your terminal supports UTF-8 too, you can use smiley characters to make your prompt more impressive. For example, to have on hand information about the connected user account, MySQL host, default database, and current time, you can set the prompt to \u@\h [📁\d] (🕑\R:\m:\s)> : 

mysql> prompt \u@\h [📁\d] (🕑\R:\m:\s)> 
PROMPT set to '\u@\h [📁\d] (🕑\R:\m:\s)> '
cbuser@Delly-7390 [📁cookbook] (🕑16:15:41)>

1.10 Using External Programs

Problem

You want to use an external program without leaving the mysql client command prompt.

Solution

Use the system command to call a program.

Discussion

While MySQL allows you to generate random passwords for its own internal user accounts, it still does not have an internal function for generating a safe user password for all other cases. Run the system command to use one of the Operating System tools: 

mysql> system openssl rand -base64 16
p1+iSG9rveeKc6v0+lFUHA==

\! is a short version of the system command: 

mysql> \! pwgen -synBC 16 1
Nu=3dWvrH7o_tWiE

pwgen may not be installed on your operating system. You need to install the pwgen package before running this example.

system is a command of the mysql client and is executed locally, using permissions belonging to the client. By default, the MySQL server is running as user mysql, though you can connect using any user account. In this case, you’ll be able to access only those programs and files that are permitted for your operating system account. Thus, regular users cannot access the data directory, which belongs to the special user mysqld process is running as: 

mysql> select @@datadir;
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0,00 sec)

mysql> system ls /var/lib/mysql/
ls: cannot open directory '/var/lib/mysql/': Permission denied
mysql> \! id
uid=1000(sveta) gid=1000(sveta) groups=1000(sveta)

For the same reason, system does not execute any command on the remote server.

You can use any program, specify options, redirect output, and pipe it to other commands. One useful insight the operating system can give you is how much physical resources are occupied by the mysqld process and compare it with data collected internally by the MySQL server itself.

MySQL stores information about memory usage in the Performance Schema. Its companion sys schema contains views, allowing you to access this information easily. Particularly, you can find the total amount of allocated memory in human-readable format by querying the sys.memory_global_total view: 

mysql> SELECT * FROM sys.memory_global_total;
+-----------------+
| total_allocated |
+-----------------+
| 253.90 MiB      |
+-----------------+
1 row in set (0.00 sec)

mysql> \! ps -o rss hp `pidof mysqld` | awk '{print $1/1024}'
298.66

The chain of the operating system requests statistics about physical memory usage from the operating system and converts it into human-readable format. This example shows that not all allocated memory is instrumented inside the MySQL server.

Note that you need to run mysql client on the same machine with your MySQL server for this to work.

1.11 Filtering and Processing Output

Warning

This recipe works only on Unix platforms!

Problem

You want to change the output format of the MySQL client beyond its built-in capabilities.

Solution

Set pager to a chain of commands, filtering output the way you want.

Discussion

Sometimes the formatting capabilities of the mysql client do not allow you to work with the result set easily. For example, the number of returned rows could be too big to fit the screen. Or the number of columns may make the result too wide to comfortably read it on the screen. Standard operating system pagers, such as less or more, allow you to work with long and wide texts more comfortably.

You can specify which pager to use either by providing the --pager option when you start mysql client or by using the pager command and its shorter version, \P. You can specify any argument for the pager.

To tell mysql to use less as a pager, specify the --pager=less option or assign this value interactively. Provide configuration parameters for the command the same way you do when you’re working in your favorite shell. In the following example, we specified options -F and -X, so less exits if the result set is small enough to fit the screen and works normally when needed: 

mysql> pager less -F -X
PAGER set to 'less -F -X'
mysql> SELECT * FROM city;
+----------------+----------------+----------------+
| state          | capital        | largest        |
+----------------+----------------+----------------+
| Alabama        | Montgomery     | Birmingham     |
| Alaska         | Juneau         | Anchorage      |
| Arizona        | Phoenix        | Phoenix        |
| Arkansas       | Little Rock    | Little Rock    |
| California     | Sacramento     | Los Angeles    |
| Colorado       | Denver         | Denver         |
| Connecticut    | Hartford       | Bridgeport     |
| Delaware       | Dover          | Wilmington     |
| Florida        | Tallahassee    | Jacksonville   |
| Georgia        | Atlanta        | Atlanta        |
| Hawaii         | Honolulu       | Honolulu       |
| Idaho          | Boise          | Boise          |
| Illinois       | Springfield    | Chicago        |
| Indiana        | Indianapolis   | Indianapolis   |
| Iowa           | Des Moines     | Des Moines     |
| Kansas         | Topeka         | Wichita        |
| Kentucky       | Frankfort      | Louisville     |
:
mysql> SELECT * FROM movies;
+----+------+----------------------------+
| id | year | movie                      |
+----+------+----------------------------+
|  1 | 1997 | The Fifth Element          |
|  2 | 1999 | The Phantom Menace         |
|  3 | 2001 | The Fellowship of the Ring |
|  4 | 2005 | Kingdom of Heaven          |
|  5 | 2010 | Red                        |
|  6 | 2011 | Unknown                    |
+----+------+----------------------------+
6 rows in set (0,00 sec)

You can use pager not only to beautify output but also to run any command that can process text. One common use is to search for a pattern in the data, printed by the diagnostic statement, using grep. For example, to watch only History list length in the long SHOW ENGINE INNODB STATUS output, use \P grep "History list length." Once you are done with the search, reset the pager with the empty pager command or instruct mysql to disable pager and print to STDOUT using nopager or \n: 

mysql> \P grep "History list length"
PAGER set to 'grep "History list length"'
mysql> SHOW ENGINE INNODB STATUS\G
History list length 30
1 row in set (0,00 sec)

mysql> SELECT SLEEP(60);
1 row in set (1 min 0,00 sec)

mysql> SHOW ENGINE INNODB STATUS\G
History list length 37
1 row in set (0,00 sec)

mysql> nopager
PAGER set to stdout

Another useful option during diagnostics is sending output nowhere. For example, to measure the effectiveness of a query, you may want to examine session status variable Handler_*. In this case, you’re not interested in the result of the query but only in the output of the following diagnostic command. Even more, you may want to send diagnostic data to professional database consultants but do not want them to see actual query output due to security considerations.

In this case, instruct pager to use a hashing function or to send output to nowhere: 

mysql> pager md5sum
PAGER set to 'md5sum'
mysql> SELECT 'Output of this statement is a hash';
8d83fa642dbf6a2b7922bcf83bc1d861  -
1 row in set (0,00 sec)

mysql> pager cat > /dev/null
PAGER set to 'cat > /dev/null'
mysql> SELECT 'Output of this statement goes to nowhere';
1 row in set (0,00 sec)

mysql> pager
Default pager wasn't set, using stdout.
mysql> SELECT 'Output of this statement is visible';

+-------------------------------------+
| Output of this statement is visible |
+-------------------------------------+
| Output of this statement is visible |
+-------------------------------------+
1 row in set (0,00 sec)
Tip

To redirect the output of a query, information messages, and all commands you type into a file, use pager cat > FILENAME. To redirect to a file and still see the output, use the tee command and its short version, \T. The built-in tee command works on both UNIX and Windows platforms.

You can chain together pager commands using pipes. For example, to print the content of the limbs table in different font styles, set pager to a chain of calls as in the following list:

  1. tr -d ' ' to remove extra spaces

  2. awk -F'|' '{print "+"$2"+\033[3m"$3"\033[0m+⁠\033[1m"$4"\033​[0m"$5"+"}' to add styles to the text

  3. column -s '+' -t' for nicely formatted output

mysql> \P tr -d ' ' | ↩
awk -F'|' '{print "+"$2"+\033[3m"$3"\033[0m+\033[1m"$4"\033[0m"$5"+"}' | ↩
column -s '+' -t
PAGER set to 'tr -d ' ' | ↩
awk -F'|' '{print "+"$2"+\033[3m"$3"\033[0m+\033[1m"$4"\033[0m"$5"+"}' | ↩
column -s '+' -t'
mysql> select * from limbs;
                  
thing       legs  arms
                  
human       2     2
insect      6     0
squid       0     10
fish        0     0
centipede   99    0
table       4     0
armchair    4     2
phonograph  0     1
tripod      3     0
PegLegPete  1     2
spacealien  NULL  NULL
                  
11 rows in set (0,00 sec)

Get MySQL Cookbook, 4th Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial