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

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 [Link to Come].

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

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

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 Recipe 1.5 and Recipe 1.6.

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 FilesMySQLMySQL Server 8.0inmysql"

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 [Link to Come]).

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.

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

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.

# 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

% mysql

% 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 --print-defaults

% my_print_defaults client mysqldump

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

% mysql -u root -p
Enter password:        ← enter MySQL root account password here

% mysql --skip-password

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

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, 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 Recipe 1.6).

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 Recipe 1.7).

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 [Link to Come]). 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 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
);

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

Recipe 4.6 discusses mysqldump further.

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

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

The rest of this section shows how to control mysql output 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)

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

% 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

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

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

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

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

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

% 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

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

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

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

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

#!/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/	/","/g;     # put "," between column values
  s/^/"/;         # add " before the first value
  s/$/"/;         # add " after the last value
  print;          # print the result
}

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

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

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(). [Link to Come] 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. Recipe 4.4 shows how.

SET is also used to assign values to stored program parameters and local variables, and to system variables. For examples, see [Link to Come] and [Link to Come].

Problem

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

Solution

Set mysql prompt to custom value

Discussion

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

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

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

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

Command prompt, like other mysql commands, supports short version: R.

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

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

[mysql]
prompt="MySQL Cookbook> "
        

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

Finally, you can specify prompt using environment variable MYSQL_PS1:

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

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

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

mysql prompt is highly customizable. You can set it to show 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 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 test
Database changed
test> 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>  
        

Special sequences can be combined together and with any other text. mysql uses UTF8 character set and you can use smiley characters to make your prompt more impressive. For example, to have on hand information about connected user account, MySQL host, default database and current time you can set prompt to u@h [ߓ✤] (ߕᜒ:m:s)> :

mysql> prompt u@h [ߓ✤] (ߕᜒ:m:s)> 
PROMPT set to 'u@h [ߓ✤] (ߕᜒ:m:s)> '
cbuser@Delly-7390 [ߓ⣯okbook] (ߕᱶ:15:41)> 
        

Problem

You want to use external program without leaving MySQL command prompt.

Solution

Use commands system to call a program.

Discussion

MySQL does not have an internal function which could be used to generate a safe user password. Run command system 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
          

You may use any program, specify options, redirect output and pipe it to other commands. One useful insight which you can get from the operating system 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 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 view sys.memory_global_total.

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

mysql> ! pidstat -r -p `pidof mysqld` | tail -n -1 | awk '{print $7}' | awk '{print $1/1024}'
298.66
          

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

system is a command of the mysql client and executed locally, using permissions belonging to the client. By default MySQL Server is running as user mysql though you can connect using any user account. In this case you will be able to access only those programs and files which are permitted for your operating system account. Thus regular user cannot access data directory, that 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.

Problem

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

Solution

Set pager to a chain of commands, filtering output in a way you wish.

Discussion

Sometimes formatting capabilities of the MySQL command line 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 makes 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 wide texts more comfortably.

You can specify which pager to use either by providing option --pager when you start mysql client or interactively, by using 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 option --pager=less or assign this value interactively. Provide configuration parameters for the command similarly as when you are working in your favorite shell. In the example below I specified options -F and -X so less exits if 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 to run any command which 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 pager with empty pager command or instruct mysql to disable pager and print to STDOUT using nopager or .

mysql> P grep "History list length"
PAGER set to 'grep "History list length"'
mysql> SHOW ENGINE INNODB STATUSG
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 STATUSG
History list length 37
1 row in set (0,00 sec)

mysql> nopager
PAGER set to stdout
          

Another useful option during diagnostic is sending output nowhere. For example, to measure effectiveness of a query you may want to examine session status variables Handler_*. In this case you are 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)
          

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

1. tr -d ' ' to remove extra spaces

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

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

mysql> P tr -d ' ' | ↩
awk -F'|' '{print "+"$2"+33[3m"$3"33[0m+33[1m"$4"33[0m"$5"+"}' | ↩
column -s '+' -t
PAGER set to 'tr -d ' ' | ↩
awk -F'|' '{print "+"$2"+33[3m"$3"33[0m+33[1m"$4"33[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   100   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)