mysql

The mysql client is an interactive program that allows you to connect to the server, issue queries, and view the results. mysql may 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 paul -p -h pit-viper.snake.net samp_db < my_query_file
					

In interactive mode, when mysql starts up, it displays a prompt 'mysql>' 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.

mysql varies the prompt to indicate what it's waiting for as you enter input lines:

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 may be specified explicitly by setting the MYSQL_HISTORY environment variable. Queries may be recalled from the command history and re-issued, either with or without further editing. The following list shows some of these editing commands (the arrow keys may not work under Windows):

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.

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

--compress      --password      --silent        --version
--debug         --pipe          --socket
--help          --port          --user
--host          --set-variable  --verbose

--silent and --verbose may be specified multiple times for increased effect.

Options Specific to mysql

• -B, --batch
  

  Specifies that mysql should 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.

• -T, --debug-info
  

  Prints debugging information when the program terminates.

• -e query, --execute=query
  								

  Executes the query and quits. You should enclose the query in quotes to prevent the shell from interpreting it as multiple command-line arguments. Multiple queries may be specified by separating them with a semicolons in the query string.

• -f, --force
  

  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.

• -H, --html
  

  Produces HTML output. This option became functional in MySQL 3.22.26.

• -i, --ignore-space
  

  Instructs 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.

• -A, --no-auto-rehash
  

  When mysql starts up, it hashes 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.) This option suppresses hash calculation, which allows mysql to start up more quickly, particularly if you have many tables. If you want to use name completion after starting mysql, you can use the rehash command at the 'mysql>' prompt.

• -o, --one-database
  

  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.

• -q, --quick
  

  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 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.

• -r, --raw
  

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

• -N, --skip-column-names
  

  Suppresses display of column names as column headers in query results. You can also achieve this effect by specifying the --silent option twice.

• -L, --skip-line-numbers
  

  Does not write line numbers when errors occur.

• -t, --table
  

  Produces output in tabular format, with values in each row delimited by bars and lined up vertically.

• -n, --unbuffered
  

  Flushes the buffer used for communication with the server after each query.

• -E, --vertical
  

  Prints query results vertically—that is, with each row of a result set displayed as a set of lines, one line per column. 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 queries by terminating them with 'G' rather than with ';' or 'g'.

  This option was introduced in MySQL 3.22.5.

• -w, --wait
  

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

Variables for mysql

The following mysql variables can be set with the --set-variable option:

• max_allowed_packet

  The maximum size of the buffer used for communication between the server and the client.

• net_buffer_length

  The initial size of the buffer used for communication between the server and the client. This buffer may be expanded up to max_allowed_packet bytes long.

mysql Commands

mysql understands several commands itself in addition to allowing you to send SQL statements to the MySQL server. Each command must be given on a single line; no semicolon is necessary at the end of the 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:

• clear, c
  

  Clears (cancels) 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]], 
   [db_name[host_name]
  

  Connects 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
  

  Edits the current query. The editor to use is determined by the value of the EDITOR and VISUAL environment variables—or vi if neither of them is set.

• ego, G
  

  Sends the current query to the server and displays the result vertically. This command was introduced in MySQL 3.22.11.

• exit
  

  Same as quit.

• go, g, ;
  

  Sends the current query to the server and displays the result.

• help, h, ?
  

  Displays a help message describing the available mysql commands.

• print, p
  

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

• quit, q
  

  Quits mysql.

• rehash, #
  

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

• status, s
  

  Retrieves and displays status information from the server. This is useful if you want to check the server version and current database.

• use db_name, u db_name
  								

  Selects the given database to make it the current database.