mysql The mysql client is an interactive program that allows you to connect to the server, issue queries, and view the results. mysql can also be used in batch mode to execute queries 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 up, 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 current query | | "> | Waiting for completion of a double-quoted string in 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. Usually, this happens when you've forgotten to terminate a string. 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. When mysql is used in interactive mode, it saves queries in a history file. This file is $HOME/.mysql_history by default or can be specified explicitly by setting the MYSQL_HISTORY environment variable. Queries can be recalled from the command history and re-issued, either with or without further editing. The following list shows 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 Ctrl-B | Move backward one word | | Escape Ctrl-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; may be repeated | 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. On Windows, command-line editing in mysql might not work. In this case, you can try the mysqlc client, which is similar to mysql but has command editing compiled in specially. (That's an advantage, but the corresponding disadvantage is that mysqlc hasn't been updated for a while, so it doesn't understand newer options or internal commands.) mysqlc requires the cygwinb19.dll library. If that library is not already installed in the same directory where mysqlc is installed (the bin directory of your MySQL distribution), look in the distribution's lib directory and copy the library to the MySQL bin directory or to your Windows system directory. Usage mysql [options] [db_name] If you specify a db_name argument, that database becomes the current (default) database for your session. If you specify no db_name argument, mysql starts with no current 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 --host --silent --compress --password --socket --debug --pipe --user --default-character-set --port --verbose --help --set-variable --version --character-sets-dir and --default-character-set can be used as of MySQL 3.23.15. As of MySQL 4, 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 up, 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.) Name hashing is on by default; --skip-auto-hash 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. This option was introduced in MySQL 4.0.2. Prior to 4.0.2, use --no-auto-rehash to disable name hashing. --batch, -B Run in batch mode. Query results are displayed 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. 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 with query results. This option was introduced in MySQL 4.0.2. --database=db_name, -D db_name The default database. This option was introduced in MySQL 3.22.29. --debug-info, -T (boolean) Print debugging information when the program terminates. --execute=query, -e query Execute the query and quit. You should enclose the query in quotes to prevent your shell from interpreting it as multiple command-line arguments. Multiple queries can be given; separate them by semicolons in the query string. --force, -f (boolean) Normally when mysql reads queries from a file, it exits if an error occurs. This option causes mysql to keep processing queries, regardless of errors. --html, -H (boolean) Produce HTML output. This option became functional in MySQL 3.22.26. --i-am-a-dummy (boolean) This option is synonymous with --safe-updates. It was introduced in MySQL 3.23.11. --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. This option was introduced in MySQL 3.22.21. --line-numbers (boolean) Display line numbers in error messages. This is the default; to suppress line numbers, use --skip-line-numbers. This option was introduced in MySQL 4.0.2. From 3.22.5 to 4.0.2, line numbers can be disabled with either --skip-line-numbers or -L. --local-infile (boolean) Allow or disallow LOAD DATA LOCAL. As of MySQL 3.23.49, LOCAL capabilities may 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: Use --disable-local-infile as of MySQL 4.0.2, --local-infile=0 before that. This option has no effect 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 --disable-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 statements.) This option has a complicated history. Named commands are enabled by default up through MySQL 3.23.22 and disabled by default thereafter. They can be disabled using --no-named-commands from MySQL 3.23.11 to 3.23.22, enabled with --enable-named-commands from 3.23.23 to 4.0.1, and controlled either way with the boolean option --named-commands thereafter. --no-auto-rehash, -A See the description for --auto-rehash; --no-auto-rehash is deprecated as of MySQL 4.0.2. --no-beep, -b (boolean) Don't emit a beep when an error occurs. This option was introduced in MySQL 4.0.2. --no-named-commands, -g See the description for --named-commands; --no-named-commands is deprecated as of MySQL 4.0.2. --no-pager See the description for --pager; this option was introduced in MySQL 3.23.28 but is deprecated in favor of --disable-pager as of MySQL 4.0.2. --no-tee See the description for --tee; this option was introduced in MySQL 3.23.28 but is deprecated in favor of --disable-tee as of MySQL 4.0.2. --one-database, -o This option is used when updating databases from the contents of an 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 --disable-pager. --pager was introduced in MySQL 3.23.28. Prior to 4.0.2, disable paging with --no-pager rather than --disable-pager. --prompt=str Change the primary prompt from mysql> to the string defined by str. The string can contain special sequences, as described in the "mysql Prompt Definition Sequences" section" later in this appendix. This option was introduced in MySQL 4.0.2. --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 can allow some large queries 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. --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 queries that mistakenly change or wipe out all or large parts of a table. In addition, result sets produced by non-join and join retrievals are limited to one thousand and one million rows, respectively. These limits can be changed by setting the select_limit and max_join_size variables. This option was introduced in MySQL 3.23.11. --skip-column-names, -N Suppress display of column names as column headers in query results. You can also achieve this effect by specifying the --silent option twice. This option was introduced in MySQL 3.22.20. The -N form is deprecated as of MySQL 4.0.2. See the description for --column-names. --skip-line-numbers, -L Suppress line numbers in error messages. This option was introduced in MySQL 3.22.5. The -L form is deprecated as of MySQL 4.0.2. 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 --disable-tee. This option does not work in batch mode. It was introduced in MySQL 3.23.28. Prior to 4.0.2, disable the tee file with --no-tee rather than --disable-tee. --unbuffered, -n (boolean) Flush the buffer used for communication with the server after each query. --vertical, -E Print query results vertically that 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 can be useful when a query produces very long lines. If this option is not specified, you can turn on vertical display format for individual queries by terminating them with \G rather than with ';' or \g. This option was introduced in MySQL 3.22.5. --wait, -w If a connection to the server cannot be established, wait and retry. --xml, -X (boolean) Produce XML output. This option was introduced in MySQL 4.0.0. Variables for mysql The following mysql variables can be set using the instructions in the "Setting Program Variables" section earlier in this appendix. connect_timeout The number of seconds to wait before timing out when attempting to connect to the server. This variable was introduced in MySQL 3.23.28. max_allowed_packet The maximum size of the buffer used for communication between the server and the client. max_join_size Limits the number of rows returned by SELECT statements that involve joins if the --safe-updates option is given. This variable was introduced in MySQL 3.23.11. 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. select_limit Limits the number of rows returned by SELECT statements if the --safe-updates option is given. This variable was introduced in MySQL 3.23.11. mysql Commands In addition to allowing you to send SQL statements to the MySQL server, mysql understands several of its own internal commands. Each command must be given on a single line; a semicolon at the end of the line is unnecessary but allowed. 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. Note that if you have disabled named commands (for example, with the --disable-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; 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. 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. This command was introduced in MySQL 3.22.11. 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. nopager, \n Disable the pager and send output to the standard output. This command was introduced in MySQL 3.23.28. It is unavailable under Windows. notee, \t Stop writing to the tee file. This command was (notee) command (notee) command (notee) command (notee) commandintroduced in MySQL 3.23.28. pager [program], \P [program] Send output through the paging program specified by program or through the program specified in the PAGER environment variable. This command was introduced in MySQL 3.23.28. It 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 the "mysql Prompt Definition Sequences" section later in this appendix. To revert the prompt to the default, specify prompt or \R with no argument. This command was introduced in MySQL 4.0.2. 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 queries contained in the named file. For Windows filenames that include backslash ('\') pathname separators, specify them using slash ('/') instead. This command was introduced in MySQL 3.23.9. status, \s Retrieve and display status information from the server. This is useful if you want to check the server version, current database, whether the connection is secure, and so forth. system command, \! command Execute command using your default shell. This option was introduced in MySQL 4.0.1. It is unavailable under Windows. tee file_name, \T file_name Copy output to the end of the named file. This command was introduced in MySQL 3.23.28. use db_name, \u db_name Select the given database to make it the current 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. Within the prompt definition, the special escape sequences shown in the following table can be used. | Sequence | Meaning | | \c | Current input line | | \d | Current 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 name, or named pipe name | | \P | a.m./p.m. 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 | | \y | Year (2-digit) | | \Y | Year (4-digit) | | \w | Weekday name, three letters | | \' | Single quote | | \" | Double quote | | \_ | Space character | | \ | Space character (the sequence is backslash-space) | | \\ | Literal '\' | | \n | Newline (linefeed) | | \x | Literal 'x' for any 'x' not listed above | |
