1.14.1 Problem

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

1.14.2 Solution

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

1.14.3 Discussion

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

• Type in a partial database, table, or column name and then hit the Tab key.

• If the partial name is unique, mysql completes it for you. Otherwise, you can hit Tab again to see the possible matches.

• Enter additional characters and hit Tab again once to complete it or twice to see the new set of matches.

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

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

[mysql]
no-auto-rehash

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

1.12.1 Problem

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

1.12.2 Solution

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

1.12.3 Discussion

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

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

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

1.13 Repeating and Editing Queries

1.13.1 Problem

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

1.13.2 Solution

Use mysql 's built-in query editor.

1.13.3 Discussion

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

The input-line editing capabilities in mysql are based on the GNU Readline library. You can read the documentation for this library to find out more about the many editing functions that are available. For more information, check the Bash manual, available online at http://www.gnu.org/manual/.

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

To recall a statement that was entered on multiple lines, the editing procedure is a little trickier than for single-line statements. In this case, you must recall and reenter each successive line of the query in order. For example, if you've entered a two-line query that contains a mistake, press Up Arrow twice to recall the first line. Make any modifications necessary and press Return. Then press Up Arrow twice more to recall the second line. Modify it, press Return, and the query will execute.

Under Windows, mysql allows statement recall only for NT-based systems. For versions such as Windows 98 or Me, you can use the special mysqlc client program instead. However, mysqlc requires an additional library file, cygwinb19.dll. If you find a copy of this library in the same directory where mysqlc is installed (the bin dir under the MySQL installation directory), you should be all set. If the library is located in the MySQL lib directory, copy it into your Windows system directory. The command looks something like this; you should modify it to reflect the actual locations of the two directories on your system:

C:\> copy C:\mysql\lib\cygwinb19.dll C:\Windows\System

After you make sure the library is in a location where mysqlc can find it, invoke mysqlc and it should be capable of input-line editing.

One unfortunate consequence of using mysqlc is that it's actually a fairly old program. (For example, even in MySQL 4.x distributions, mysqlc dates back to 3.22.7.) This means it doesn't understand newer statements such as SOURCE.

1.11 Selecting a Database

1.11.1 Problem

You want to tell mysql which database to use.

1.11.2 Solution

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

1.11.3 Discussion

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

% mysql cookbook

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

% mysql -h  host  -p -u  user  cookbook 

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

mysql> USE cookbook;
Database changed

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

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

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

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

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

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

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

1.10 Issuing Queries

1.10.1 Problem

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

1.10.2 Solution

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

1.10.3 Discussion

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

Example queries in this book are shown with SQL keywords like SELECT in uppercase for distinctiveness, but that's simply a typographical convention. You can enter keywords in any lettercase.

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

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

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

1.8.1 Problem

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

1.8.2 Solution

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

1.8.3 Discussion

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

% mysql
mysql: Command not found.

Or like this under Windows:

C:\> mysql
Bad command or invalid filename

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

% /usr/local/mysql/bin/mysql

Or like this under Windows:

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

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

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

I've pointed out the importance of the search path here because I receive many questions from people who aren't aware of the existence of such a thing, and who consequently try to do all their MySQL-related work in the bin directory where mysql is installed. This seems particularly common among Windows users. Perhaps the reason is that, except for Windows NT and its derivatives, the Windows Help application seems to be silent on the subject of the command interpreter search path or how to set it. (Apparently, Windows Help considers it dangerous for people to know how to do something useful for themselves.)

Another way for Windows users to avoid typing the pathname or changing into the mysql directory is to create a shortcut and place it in a more convenient location. That has the advantage of making it easy to start up mysql just by opening the shortcut. To specify command-line 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 a shortcut corresponding to each set of options you need—for example, one shortcut to connect as an ordinary user for general work and another to connect as the MySQL root user for administrative purposes.

1.8.1 Problem

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

1.8.2 Solution

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

1.8.3 Discussion

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

% mysql
mysql: Command not found.

Or like this under Windows:

C:\> mysql
Bad command or invalid filename

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

% /usr/local/mysql/bin/mysql

Or like this under Windows:

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

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

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

I've pointed out the importance of the search path here because I receive many questions from people who aren't aware of the existence of such a thing, and who consequently try to do all their MySQL-related work in the bin directory where mysql is installed. This seems particularly common among Windows users. Perhaps the reason is that, except for Windows NT and its derivatives, the Windows Help application seems to be silent on the subject of the command interpreter search path or how to set it. (Apparently, Windows Help considers it dangerous for people to know how to do something useful for themselves.)

Another way for Windows users to avoid typing the pathname or changing into the mysql directory is to create a shortcut and place it in a more convenient location. That has the advantage of making it easy to start up mysql just by opening the shortcut. To specify command-line 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 a shortcut corresponding to each set of options you need—for example, one shortcut to connect as an ordinary user for general work and another to connect as the MySQL root user for administrative purposes.

1.6 Protecting Option Files

1.6.1 Problem

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

1.6.2 Solution

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

1.6.3 Discussion

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

% chmod 600 .my.cnf

1.7 Mixing Command-Line and Option File Parameters

1.7.1 Problem

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

1.7.2 Solution

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

1.7.3 Discussion

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

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

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

%

mysql -p

1.5.1 Problem

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

1.5.2 Solution

Put the parameters in an option file.

1.5.3 Discussion

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

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

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

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

This format has the following general characteristics:

• Lines are written in groups. The first line of the group specifies the group name inside of square brackets, and the remaining lines specify options associated with the group. The example file just shown has a [client] group and a [mysql] group. Within a group, option lines are written in name=value format, where name corresponds to an option name (without leading dashes) and value is the option's value. If an option doesn't take any value (such as for the no-auto-rehash option), the name is listed by itself with no trailing =value part.

• If you don't need some particular parameter, just leave out the corresponding line. For example, if you normally connect to the default host (localhost), you don't need any host line. If your MySQL username is the same as your operating system login name, you can omit the user line.

• In option files, only the long form of an option is allowed. This is in contrast to command lines, where options often can be specified using a short form or a long form. For example, the hostname can be given using either -h hostname or --host=hostname on the command line; in an option file, only host=hostname is allowed.

• Options often are used for connection parameters (such as host, user, and password). However, the file can specify 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.

• The usual group for specifying client connection parameters is [client]. This group actually is used by all the standard MySQL clients, so by creating an option file to use with mysql, you make it easier to invoke other programs such as mysqldump and mysqladmin as well.

• You can define multiple groups in an option file. A common convention is for a program to look for parameters in the [client] group and in the group named after the program itself. This provides a convenient way to list general client parameters that you want all client programs to use, but still be able to 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 no-auto-rehash and pager options from the [mysql] group. (If you put the mysql-specific options in the [client] group, that will result in "unknown option" errors for all other programs that use the [client] group and they won't run properly.)

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

• Lines beginning with # or ; characters are ignored as comments. Blank lines are ignored, too.

• Option files must be plain text files. If you create an option file with a word processor that uses some non-text format by default, be sure to save the file explicitly as text. Windows users especially should take note of this.

• Options that specify file or directory pathnames should be written using / as the pathname separator character, even under Windows.

If you want to find out which options will be taken from option files by mysql, 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, mysql looks in both the [client] and [mysql] groups for options, so you can check which values it will take from option files like this:

% my_print_defaults client mysql

1.4 Starting and Terminating mysql

1.4.1 Problem

You want to start and stop the mysql program.

1.4.2 Solution

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

1.4.3 Discussion

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

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

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

mysql>

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

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

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

If you don't have a MySQL username and password, you need to obtain permission to use the MySQL server.

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

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

%

mysql -p

If you like, you can specify the password directly on the command line by using either -ppassword (note that there is no space after the -p) or --password=password. I don't recommend doing this on a multiple-user machine, because the password may be visible momentarily to other users who are running tools such as ps that report process information.

If you get an error message that mysql cannot be found or is an invalid command when you try to invoke it, that means your command interpreter doesn't know where mysql is installed. 

To terminate a mysql session, issue a QUIT statement:

mysql> QUIT

You can also terminate the session by issuing an EXIT statement or (under Unix) by typing Ctrl-D.

The way you specify connection parameters for mysql also applies to other MySQL programs such as mysqldump and mysqladmin. For example, some of the actions that mysqladmin can perform are available only to the MySQL root account, so you need to specify name and password options for that user:

% mysqladmin -p -u root shutdown
Enter password:

1.3 Creating a Database and a Sample Table

1.3.1 Problem

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

1.3.2 Solution

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

1.3.3 Discussion

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

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

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

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

If you don't want to enter the complete text of the INSERT statements (and I don't blame you),. And if you don't want to type in any of the statements.

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

The table is named limbs and contains three columns to records 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 column cannot be determined; NULL indicates "unknown value.")

Verify that the table contains what you expect by issuing a SELECT statement:

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

At this point, you're all set up with a database and a table that can be used to run some example queries.