(mysql.info) mysql-commands
Info Catalog
(mysql.info) mysql-command-options
(mysql.info) mysql
(mysql.info) batch-commands
8.5.2 `mysql' Commands
----------------------
`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:
*Option* *Description*
`\v' The server version
`\d' The default database
`\h' The server host
`\p' The current TCP/IP port or socket file
`\u' Your username
`\U' Your full `USER_NAME@HOST_NAME' account name
`\\' A literal ‘`\'’ backslash character
`\n' A newline character
`\t' A tab character
`\ ' A space (a space follows the backslash)
`\_' A space
`\R' The current time, in 24-hour military time (0-23)
`\r' The current time, standard 12-hour time (1-12)
`\m' Minutes of the current time
`\y' The current year, two digits
`\Y' The current year, four digits
`\D' The full current date
`\s' Seconds of the current time
`\w' The current day of the week in three-letter format
(Mon, Tue, ...)
`\P' am/pm
`\o' The current month in numeric format
`\O' The current month in three-letter format (Jan, Feb,
...)
`\c' A counter that increments for each statement you issue
`\S' Semicolon
`\'' Single quote
`\"' Double quote
‘`\'’ 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
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>
Info Catalog
(mysql.info) mysql-command-options
(mysql.info) mysql
(mysql.info) batch-commands
automatically generated byinfo2html