mysql sends each SQL statement that you
issue to the server to be executed. There is also a set of
commands that mysql itself interprets. For
a list of these commands, type help or
\h at the mysql>
prompt:
mysql> help
List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
? (\?) Synonym for `help'.
clear (\c) Clear command.
connect (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter. NOTE: Takes the rest of the line as
new delimiter.
edit (\e) Edit command with $EDITOR.
ego (\G) Send command to mysql server, display result vertically.
exit (\q) Exit mysql. Same as quit.
go (\g) Send command to mysql server.
help (\h) Display this help.
nopager (\n) Disable pager, print to stdout.
notee (\t) Don't write into outfile.
pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print (\p) Print current command.
prompt (\R) Change your mysql prompt.
quit (\q) Quit mysql.
rehash (\#) Rebuild completion hash.
source (\.) Execute an SQL script file. Takes a file name as an argument.
status (\s) Get status information from the server.
system (\!) Execute a system shell command.
tee (\T) Set outfile [to_outfile]. Append everything into given
outfile.
use (\u) Use another database. Takes database name as argument.
warnings (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
Each command has both a long and short form. The long form is
not case sensitive; the short form is. The long form can be
followed by an optional semicolon terminator, but the short
form should not.
In the delimiter command, you should avoid
the use of the backslash (‘\’)
character because that is the escape character for MySQL.
The edit, nopager,
pager, and system
commands work only in Unix.
The status command provides some
information about the connection and the server you are using.
If you are running in --safe-updates mode,
status also prints the values for the
mysql variables that affect your queries.
To log queries and their output, use the
tee command. All the data displayed on the
screen is appended into a given file. This can be very useful
for debugging purposes also. You can enable this feature on
the command line with the --tee option, or
interactively with the tee command. The
tee file can be disabled interactively with
the notee command. Executing
tee again re-enables logging. Without a
parameter, the previous file is used. Note that
tee flushes query results to the file after
each statement, just before mysql prints
its next prompt.
By using the --pager option, it is possible
to browse or search query results in interactive mode with
Unix programs such as less,
more, or any other similar program. If you
specify no value for the option, mysql
checks the value of the PAGER environment
variable and sets the pager to that. Output paging can be
enabled interactively with the pager
command and disabled with nopager. The
command takes an optional argument; if given, the paging
program is set to that. With no argument, the pager is set to
the pager that was set on the command line, or
stdout if no pager was specified.
Output paging works only in Unix because it uses the
popen() function, which does not exist on
Windows. For Windows, the tee option can be
used instead to save query output, although this is not as
convenient as pager for browsing output in
some situations.
Here are a few tips about the pager
command:
-
You can use it to write to a file and the results go only
to the file:
mysql> pager cat > /tmp/log.txt
You can also pass any options for the program that you
want to use as your pager:
mysql> pager less -n -i -S
-
In the preceding example, note the -S
option. You may find it very useful for browsing wide
query results. Sometimes a very wide result set is
difficult to read on the screen. The -S
option to less can make the result set
much more readable because you can scroll it horizontally
using the left-arrow and right-arrow keys. You can also
use -S interactively within
less to switch the horizontal-browse
mode on and off. For more information, read the
less manual page:
shell> man less
-
You can specify very complex pager commands for handling
query output:
mysql> pager cat | tee /dr1/tmp/res.txt \
| tee /dr2/tmp/res2.txt | less -n -i -S
In this example, the command would send query results to
two files in two different directories on two different
filesystems mounted on /dr1 and
/dr2, yet still display the results
onscreen via less.
You can also combine the tee and
pager functions. Have a
tee file enabled and
pager set to less, and
you are able to browse the results using the
less program and still have everything
appended into a file the same time. The difference between the
Unix tee used with the
pager command and the
mysql built-in tee
command is that the built-in tee works even
if you do not have the Unix tee available.
The built-in tee also logs everything that
is printed on the screen, whereas the Unix
tee used with pager does
not log quite that much. Additionally, tee
file logging can be turned on and off interactively from
within mysql. This is useful when you want
to log some queries to a file, but not others.
The default mysql> prompt can be
reconfigured. The string for defining the prompt can contain
the following special sequences:
‘\’ followed by any other
letter just becomes that letter.
If you specify the prompt command with no
argument, mysql resets the prompt to the
default of mysql>.
You can set the prompt in several ways:
-
Use an environment variable. You can
set the MYSQL_PS1 environment variable
to a prompt string. For example:
shell> export MYSQL_PS1="(\u@\h) [\d]> "
-
Use a command-line option. You can
set the --prompt option on the command
line to mysql. For example:
shell> mysql --prompt="(\u@\h) [\d]> "
(user@host) [database]>
-
Use an option file. You can set the
prompt option in the
[mysql] group of any MySQL option file,
such as /etc/my.cnf or the
.my.cnf file in your home directory.
For example:
[mysql]
prompt=(\\u@\\h) [\\d]>\\_
In this example, note that the backslashes are doubled. If
you set the prompt using the prompt
option in an option file, it is advisable to double the
backslashes when using the special prompt options. There
is some overlap in the set of allowable prompt options and
the set of special escape sequences that are recognized in
option files. (These sequences are listed in
Section 4.3.2, “Using Option Files”.) The overlap may cause you
problems if you use single backslashes. For example,
\s is interpreted as a space rather
than as the current seconds value. The following example
shows how to define a prompt within an option file to
include the current time in
HH:MM:SS> format:
[mysql]
prompt="\\r:\\m:\\s> "
-
Set the prompt interactively. You can
change your prompt interactively by using the
prompt (or \R)
command. For example:
mysql> prompt (\u@\h) [\d]>\_
PROMPT set to '(\u@\h) [\d]>\_'
(user@host) [database]>
(user@host) [database]> prompt
Returning to default PROMPT of mysql>
mysql>
