mysql

The mysql client is an interactive program that allows you to connect to the server, issue SQL statements, and view the results. mysql can also be used in batch mode to execute statements that are stored in a file if you redirect the input of the command to read from that file. For example:

% mysql -u sampadm -p -h cobra.snake.net sampdb < my_query_file

In interactive mode, when mysql starts, it displays a mysql> prompt to indicate that it's waiting for input. To issue a query, type it in (using multiple lines if necessary) and then indicate the end of the query by typing ';' (semicolon) or \g. mysql sends the query to the server, displays the results, and then prints another prompt to indicate that it's ready for another query. \G also terminates a query, but causes query results to be displayed vertically (that is, with one column value per output line).

mysql varies the prompt to indicate what it's waiting for as you enter input lines. The mysql> prompt is the primary prompt, displayed at the beginning of each query. The other prompts are secondary prompts, displayed to obtain additional lines for the current query.

Prompt	Meaning
mysql>	Waiting for the first line of a new query
->	Waiting for the next line of the current query
'>	Waiting for completion of a single-quoted string in the current query
">	Waiting for completion of a double-quoted string in the current query
`>	Waiting for completion of a quoted identifier in the current query

The '> and "> prompts indicate that you've begun a single-quoted or double-quoted string on a previous line and have not yet entered the terminating quote. Similarly, `> indicates an unterminated quoted identifier. Usually, you see these prompts when you've forgotten to terminate a string or identifier. If that's the case, to escape from string- collection mode, enter the appropriate matching quote that is indicated by the prompt, followed by \c to cancel the current query.

On Unix, when mysql is used in interactive mode, it saves statements in a history file. The name of this file is $HOME/.mysql_history by default or can be specified explicitly by setting the MYSQL_HISTFILE environment variable. If you don't want to save a history file, remove .mysql_history and then either create a symbolic link named .mysql_history that points to /dev/null or set MYSQL_HISTFILE to /dev/null.

Some options suppress use of the history file. Generally, these are options that indicate non-interactive use of mysql, such as --batch, --html, and --quick.

Statements can be recalled from the command history and re-issued, either with or without further editing. The following table lists some of these editing commands.

Key Sequence	Meaning
Up arrow, Ctrl-P	Recall previous line
Down arrow, Ctrl-N	Recall next line
Left arrow, Ctrl-B	Move cursor left (backward)
Right arrow, Ctrl-F	Move cursor right (forward)
Escape B	Move backward one word
Escape F	Move forward one word
Ctrl-A	Move cursor to beginning of line
Ctrl-E	Move cursor to end of line
Ctrl-D	Delete character under cursor
Delete	Delete character to left of cursor
Escape D	Delete word
Escape Backspace	Delete word to left of cursor
Ctrl-K	Erase everything from cursor to end of line
Ctrl-_	Undo last change; can be repeated

Under Windows, the line-editing capabilities are not available. If you're using a Windows NT-based system, mysql supports the arrow keys for moving up and down through input lines or left and right within lines, but not the other editing commands.

Usage

mysql [options] [db_name]

If you specify a db_name argument, that database becomes the default database for your session. If you specify no db_name argument, mysql starts with no default database and you'll need to either qualify all table references with a database name or issue a USE db_name statement to specify a default database.

Standard Options Supported by mysql

--character-sets-dir      --password                --silent
--compress                --pipe                    --socket
--debug                   --port                    --user
--default-character-set   --protocol                --verbose
--help                    --set-variable            --version
--host                    --shared-memory-base-name

mysql also supports the standard SSL options.

--silent and --verbose can be given multiple times for increased effect.

Options Specific to mysql

• --auto-rehash (boolean)

  When mysql starts, it can hash database, table, and column names to construct a data structure that allows for fast completion of names. You can type the initial part of a name when entering a query and then press Tab. mysql will complete the name unless it's ambiguous; press Tab again to see the possible completions.

  Name hashing is on by default, although it does not take effect until you have selected a default database. --skip-auto-rehash suppresses hash calculation, which allows mysql to start up more quickly, particularly if you have many tables.

  If hashing has been disabled and you want to use name completion after starting mysql, you can use the rehash command at the mysql> prompt.

• --batch, -B

  Run in batch mode. mysql displays query results in tab-delimited format (each row on a separate line with tabs between column values). This is especially convenient for generating output that you want to import into another program, such as a spreadsheet application. Query results include an initial row of column headings by default. To suppress these headings, use the --skip-column-names option.

• --column-names (boolean)

  Display column names as column headers in query results. Use --skip-column-names to suppress display of column names. You can also achieve that effect by specifying the --silent option twice.

• --database=db_name, -D db_name

  Specify the default database.

• --debug-info, -T (boolean)

  Print debugging information when the program terminates.

• --delimiter=str

  Set the statement delimiter. The default delimiter is the semicolon (';').

• --execute=statement, -e statement

  Execute the statement and quit. You should enclose the statement in quotes to prevent your shell (command interpreter) from treating it as multiple command-line arguments. Multiple statements can be given; separate them by semicolons in the statement string.

• --force, -f (boolean)

  Normally when mysql reads statements from a file, it exits if an error occurs. This option causes mysql to continue processing statements, regardless of errors.

• --html, -H (boolean)

  Produce HTML output.

• --i-am-a-dummy (boolean)

  This option is synonymous with --safe-updates.

• --ignore-spaces, -i

  Cause the server to ignore spaces between function names and the '(' character that introduces the argument list. Normally, function names must be followed immediately by the parenthesis with no intervening spaces. This option causes function names to be treated as reserved words.

• --line-numbers (boolean)

  Display line numbers in error messages. This is the default; to suppress line numbers, use --skip-line-numbers.

• --local-infile (boolean)

  Allow or disallow LOAD DATA LOCAL. The LOCAL capability might be present but disabled by default. If LOAD DATA LOCAL results in an error, try again after invoking mysql with the --local-infile option. This option can also be used to disable LOCAL if it is enabled, for example, with --disable-local-infile.

  This option is ineffective if the server has been configured to disallow use of LOCAL.

• --named-commands, -G (boolean)

  Allow long forms of mysql's internal commands at the beginning of any input line. If this capability is disabled with --skip-named-commands, long commands are allowed only at the primary prompt and disallowed at the secondary prompts. (That is, they are disallowed on second and subsequent lines of a multiple-line statement.)

• --no-auto-rehash, -A

  This option is deprecated in favor of --skip-auto-rehash. See the description for --auto-rehash.

• --no-beep, -b (boolean)

  Don't emit a beep when an error occurs.

• --no-named-commands, -g

  This option is deprecated. See the description for --named-commands.

• --no-pager

  This option is deprecated in favor of --skip-pager. See the description for --pager.

• --no-tee

  This option is deprecated in favor of --skip-tee. See the description for --tee.

• --one-database, -o

  This option is used when updating databases from the contents of a binary or update log file. It tells mysql to update only the default database (the database named on the command line). Updates to other databases are ignored. If no database is named on the command line, no updates are performed.

• --pager[=program]

  Use a paging program (for example, /bin/more or /bin/less) to display long query results one page at a time. If program is missing, the paging program is determined from the value of the PAGER environment variable. Output paging is unavailable in batch mode, and does not work under Windows. Paging can be disabled with --skip-pager.

• --prompt=str

  Change the primary prompt from mysql> to the string defined by str. The string can contain special sequences, as described in "mysql Prompt Definition Sequences."

• --quick, -q

  Normally mysql retrieves the entire result of a query from the server before displaying it. This option causes each row to be displayed as it is retrieved, which uses much less memory and may allow some large statements to be performed successfully that would fail otherwise. However, this option should not be specified for interactive use; if the user pauses the output or suspends mysql, the server continues to wait, which can interfere with other clients.

• --raw, -r (boolean)

  Write column values without escaping any special characters. This option is used in conjunction with the --batch option.

• --reconnect (boolean)

  Automatically reconnect to the server if the connection is lost. Before MySQL 5.0.3, this option is enabled by default; disable it with --skip-reconnect.

• --safe-updates, -U (boolean)

  This option places some limits on what you can do and can be beneficial for new MySQL users:

  • Updates (statements that modify data) are allowed only if the records to be modified are identified by key values or if a LIMIT clause is used. This helps prevent statements that mistakenly change or wipe out all or large parts of a table.

  • Result sets produced by non-join retrievals are limited to one thousand rows unless a LIMIT clause is used. Retrievals that involve a join are disallowed if the optimizer estimates that it will need to examine more than one million rows. This helps prevent unintended generation of very large query results.

  These limits can be changed by setting the select_limit and max_join_size variables.

• --secure-auth (boolean)

  Do not connect to the server unless it supports the more secure password format introduced in MySQL 4.1. This option was introduced in MySQL 4.1.1.

• --sigint-ignore (boolean)

  Ignore SIGINT signals. On Unix, Ctrl-C typically sends a SIGINT and terminates the current program. Using --sigint-ignore prevents mysql from being terminated this way. This option was introduced in MySQL 4.1.6.

• --skip-column-names, -N

  The -N form of this option is deprecated. See the description for --column-names.

• --skip-line-numbers, -L

  The -L form of this option is deprecated. See the description for --line-numbers.

• --table, -t (boolean)

  Produce output in tabular format, with values in each row delimited by bars and lined up vertically. This is the default output format when mysql is not run in batch mode.

• --tee=file_name

  Append a copy of all output to the named file. Output copying can be disabled with --skip-tee. This option does not work in batch mode.

• --unbuffered, -n (boolean)

  After each query, flush the buffer used for communication with the server after each query.

• --vertical, -E

  Print query results verticallythat is, with each row of a query result displayed as a set of output lines, one column per line. (Each line consists of a column name and value.) The display for each row is preceded by a line indicating the row number within the result set. Vertical display format may be useful when a query produces very long lines.

  If this option is not specified, you can turn on vertical display format for individual statements by terminating them with \G rather than with ';' or \g.

• --wait, -w

  If a connection to the server cannot be established, wait and retry.

• --xml, -X (boolean)

  Produce XML output.

Variables for mysql

The following mysql variables can be set using the instructions given in "Setting Program Variables" earlier in this appendix.

• connect_timeout

  The number of seconds to wait before timing out when attempting to connect to the server. The default value is 0.

• max_allowed_packet

  The maximum size of the buffer used for communication between the server and the client. The default value is 16MB and the maximum is 1GB.

• max_join_size

  Places a limit on the execution of joins if the --safe-updates option is given. The server rejects joins for which it believes it will need to examine more than max_join_size rows. The default value is 1,000,000.

• net_buffer_length

  The initial size of the buffer used for communication between the server and the client. This buffer can be expanded up to max_allowed_packet bytes long. The default value is 16KB.

• select_limit

  Limits the number of rows returned by SELECT statements if the --safe-updates option is given. The default value is 1,000.

mysql Commands

In addition to allowing you to send SQL statements to the MySQL server, mysql implements several other commands internally. Each command must be given on a single line. Most of the commands have a long form consisting of a word, and a short form consisting of a backslash followed by a single letter. Commands in long form are not case sensitive. Commands in short form must be specified using the lettercase shown in the following list. A semicolon at the end of the line is unnecessary but allowed for long-form commands, but should be omitted for short-form commands.

Note that if you have disabled named commands (for example, with the --skip-named-commands option), long command names are recognized only at the primary mysql> prompt.

• clear, \c

  Clear (cancel) the current query. The current query is the query that you are in the process of typing in; this command does not cancel a query that has already been sent to the server and for which mysql is displaying output.

• connect [db_name [host_name]], \r [db_name [host_name]]

  Connect (or reconnect) to the given database on the given host. If the database name or hostname is missing, the most recently used values from the current mysql session are used.

• delimiter str, \d str

  Set the query delimiter. The default delimiter is the semicolon (';'). The stored procedure parser recognizes only the semicolon as the statement delimiter, so this command can be used to redefine the delimiter for mysql while defining a stored procedure. For an example, see "Using Stored Procedures," in Chapter 2, "MySQL SQL Syntax and Use."

• edit, \e

  Edit the current query. mysql attempts to determine what editor to use by examining the EDITOR and VISUAL environment variables. If neither variable is set, mysql uses vi. This option is unavailable under Windows.

• ego, \G

  Send the current query to the server and display the result vertically.

• exit

  Same as quit.

• go, \g, ;

  Send the current query to the server and display the result.

• help, \h, ?, \?

  Display a help message describing the available mysql commands. If the help tables in the mysql database have been loaded, you can use help to get server-side help. Use help contents to get a list of help categories, help category for help on a particular category, or help keyword for help about the particular keyword (such as SELECT or UPDATE).

  Instructions for loading the help tables are provided in Appendix A, "Obtaining and Installing Software."

• nopager, \n

  Disable the pager and send output to the standard output. This command is unavailable under Windows.

• notee, \t

  Stop (notee) command (notee) command (notee) command (notee) command (notee) command writing to the tee file.

• pager [program], \P [program]

  Send output through the paging program specified by program, or through the program specified in the PAGER environment variable, if that variable is set and program is not given. This command is unavailable under Windows.

• print, \p

  Print the current query (the text of the query itself, not the results obtained by executing the query).

• prompt arguments, \R arguments

  Redefine the primary mysql> prompt. Everything following the first space after the prompt keyword becomes part of the prompt string, including other spaces. The string can contain special sequences, as described in "mysql Prompt Definition Sequences." To revert the prompt to the default, specify prompt or \R with no arguments.

• quit, \q

  Quit mysql.

• rehash, \#

  Recalculate the information needed for database, table, and column name completion. See the description for the --auto-rehash option.

• source file_name, \. file_name

  Read and execute the statements contained in the named file. For Windows filenames that include backslash ('\') pathname separators, specify them using slash ('/') instead.

• status, \s

  Retrieve and display status information from the server. This is useful if you want to check the server version, default database, whether the connection is secure, and so forth.

• system command, \! command

  Execute command using your default shell. This command is unavailable under Windows.

• tee file_name, \T file_name

  Copy output to the end of the named file.

• use db_name, \u db_name

  Select the given database to make it the default database.

mysql Prompt Definition Sequences

The MYSQL_PS1 environment variable, the --prompt option, or the prompt command can be used to redefine the primary mysql> query prompt that mysql prints. For example, to include the name of the default database in the prompt, use the prompt command as follows and then select different databases to see how the prompt follows the current selection:

% mysql
mysql> prompt \d>\_
PROMPT set to '\d>\_'
(none)> USE sampdb;
Database changed
sampdb> USE test;
Database changed
test>

The prompt keyword is followed by the prompt definition string. Within the definition, escape sequences that begin with backslashes indicate special prompt options. The \d and \_ sequences signify the default database name and a space. (If you set the prompt using the environment variable or the --prompt option, you might find it necessary to double the backslashes when specifying the prompt string.) The following table shows the complete list of available options.

Sequence	Meaning
\c	Current input line number
\d	Default database name, or "(none)" if no database is selected
\D	Full date and time
\h	Current host
\m	Minute
\o	Month number
\O	Month name, three letters
\p	Current port number, socket filename, named pipe name, or shared memory name
\P	am/pm indicator
\r	Hour (12-hour time)
\R	Hour (24-hour time)
\s	Second
\S	Semicolon
\t	Tab
\u	Current username, without hostname
\U	Current username, including hostname
\v	Server version
\w	Weekday name, three letters
\y	Year (two-digit)
\Y	Year (four-digit)
\'	Single quote
\"	Double quote
\_	Space character
\	Space character (the sequence is backslash-space)
\\	Literal '\'
\n	Newline (linefeed)
\x	Literal 'x' for any 'x' not otherwise listed