Movatterモバイル変換

This section provides information about techniques for more effective use ofmysql and aboutmysql operational behavior.

Input-Line Editing

mysql supports input-line editing, which enables you to modify the current input line in place or recall previous input lines. For example, theleft-arrow andright-arrow keys move horizontally within the current input line, and theup-arrow anddown-arrow keys move up and down through the set of previously entered lines.Backspace deletes the character before the cursor and typing new characters enters them at the cursor position. To enter the line, pressEnter.

On Windows, the editing key sequences are the same as supported for command editing in console windows. On Unix, the key sequences depend on the input library used to buildmysql (for example, thelibedit orreadline library).

Documentation for thelibedit andreadline libraries is available online. To change the set of key sequences permitted by a given input library, define key bindings in the library startup file. This is a file in your home directory:.editrc forlibedit and.inputrc forreadline.

For example, inlibedit,Control+W deletes everything before the current cursor position andControl+U deletes the entire line. Inreadline,Control+W deletes the word before the cursor andControl+U deletes everything before the current cursor position. Ifmysql was built usinglibedit, a user who prefers thereadline behavior for these two keys can put the following lines in the.editrc file (creating the file if necessary):

bind "^W" ed-delete-prev-wordbind "^U" vi-kill-line-prev

To see the current set of key bindings, temporarily put a line that says onlybind at the end of.editrc.mysql shows the bindings when it starts.

Disabling Interactive History

Theup-arrow key enables you to recall input lines from current and previous sessions. In cases where a console is shared, this behavior may be unsuitable.mysql supports disabling the interactive history partially or fully, depending on the host platform.

On Windows, the history is stored in memory.Alt+F7 deletes all input lines stored in memory for the current history buffer. It also deletes the list of sequential numbers in front of the input lines displayed withF7 and recalled (by number) withF9. New input lines entered after you pressAlt+F7 repopulate the current history buffer. Clearing the buffer does not prevent logging to the Windows Event Viewer, if the--syslog option was used to startmysql. Closing the console window also clears the current history buffer.

To disable interactive history on Unix, first delete the.mysql_history file, if it exists (previous entries are recalled otherwise). Then startmysql with the--histignore="*" option to ignore all new input lines. To re-enable the recall (and logging) behavior, restartmysql without the option.

If you prevent the.mysql_history file from being created (seeControlling the History File) and use--histignore="*" to start themysql client, the interactive history recall facility is disabled fully. Alternatively, if you omit the--histignore option, you can recall the input lines entered during the current session.

Unicode Support on Windows

Windows provides APIs based on UTF-16LE for reading from and writing to the console; themysql client for Windows is able to use these APIs. The Windows installer creates an item in the MySQL menu namedMySQL command line client - Unicode. This item invokes themysql client with properties set to communicate through the console to the MySQL server using Unicode.

To take advantage of this support manually, runmysql within a console that uses a compatible Unicode font and set the default character set to a Unicode character set that is supported for communication with the server:

With those changes,mysql uses the Windows APIs to communicate with the console using UTF-16LE, and communicate with the server using UTF-8. (The menu item mentioned previously sets the font and character set as just described.)

To avoid those steps each time you runmysql, you can create a shortcut that invokesmysql.exe. The shortcut should set the console font to Lucida Console or some other compatible Unicode font, and pass the--default-character-set=utf8mb4 (orutf8mb3) option tomysql.exe.

Alternatively, create a shortcut that only sets the console font, and set the character set in the[mysql] group of yourmy.ini file:

[mysql]default-character-set=utf8mb4   # or utf8mb3

Displaying Query Results Vertically

Some query results are much more readable when displayed vertically, instead of in the usual horizontal table format. Queries can be displayed vertically by terminating the query with \G instead of a semicolon. For example, longer text values that include newlines often are much easier to read with vertical output:

mysql> SELECT * FROM mails WHERE LENGTH(txt) < 300 LIMIT 300,1\G*************************** 1. row ***************************  msg_nro: 3068     date: 2000-03-01 23:29:50time_zone: +0200mail_from: Jones    reply: jones@example.com  mail_to: "John Smith" <smith@example.com>      sbj: UTF-8      txt: >>>>> "John" == John Smith writes:John> Hi.  I think this is a good idea.  Is anyone familiarJohn> with UTF-8 or Unicode? Otherwise, I'll put this on myJohn> TODO list and see what happens.Yes, please do that.Regards,Jones     file: inbox-jani-1     hash: 1904029441 row in set (0.09 sec)

Using Safe-Updates Mode (--safe-updates)

For beginners, a useful startup option is--safe-updates (or--i-am-a-dummy, which has the same effect). Safe-updates mode is helpful for cases when you might have issued anUPDATE orDELETE statement but forgotten theWHERE clause indicating which rows to modify. Normally, such statements update or delete all rows in the table. With--safe-updates, you can modify rows only by specifying the key values that identify them, or aLIMIT clause, or both. This helps prevent accidents. Safe-updates mode also restrictsSELECT statements that produce (or are estimated to produce) very large result sets.

The--safe-updates option causesmysql to execute the following statement when it connects to the MySQL server, to set the session values of thesql_safe_updates,sql_select_limit, andmax_join_size system variables:

SET sql_safe_updates=1, sql_select_limit=1000, max_join_size=1000000;

TheSET statement affects statement processing as follows:

To specify result set limits different from 1,000 and 1,000,000, you can override the defaults by using the--select-limit and--max-join-size options when you invokemysql:

mysql --safe-updates --select-limit=500 --max-join-size=10000

It is possible forUPDATE andDELETE statements to produce an error in safe-updates mode even with a key specified in theWHERE clause, if the optimizer decides not to use the index on the key column:

As of MySQL 8.0.13, safe-updates mode also includes these behaviors:

Disabling mysql Auto-Reconnect

If themysql client loses its connection to the server while sending a statement, it immediately and automatically tries to reconnect once to the server and send the statement again. However, even ifmysql succeeds in reconnecting, your first connection has ended and all your previous session objects and settings are lost: temporary tables, the autocommit mode, and user-defined and session variables. Also, any current transaction rolls back. This behavior may be dangerous for you, as in the following example where the server was shut down and restarted between the first and second statements without you knowing it:

mysql> SET @a=1;Query OK, 0 rows affected (0.05 sec)mysql> INSERT INTO t VALUES(@a);ERROR 2006: MySQL server has gone awayNo connection. Trying to reconnect...Connection id:    1Current database: testQuery OK, 1 row affected (1.30 sec)mysql> SELECT * FROM t;+------+| a    |+------+| NULL |+------+1 row in set (0.05 sec)

The@a user variable has been lost with the connection, and after the reconnection it is undefined. If it is important to havemysql terminate with an error if the connection has been lost, you can start themysql client with the--skip-reconnect option.

For more information about auto-reconnect and its effect on state information when a reconnection occurs, seeAutomatic Reconnection Control.

mysql Client Parser Versus Server Parser

Themysql client uses a parser on the client side that is not a duplicate of the complete parser used by themysqld server on the server side. This can lead to differences in treatment of certain constructs. Examples: