This section describes some techniques that can help you use mysql more effectively.
mysql supports input-line editing, which enables you to modify the current input line in place or recall previous input lines. For example, the left-arrow and right-arrow keys move horizontally within the current input line, and the up-arrow and down-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, press Enter.
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 build
mysql (for example, the
Documentation for the
readline 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:
For example, in
Control+W deletes everything before the
current cursor position and Control+U deletes
the entire line. In
Control+W deletes the word before the cursor
and Control+U deletes everything before the
current cursor position. If mysql was built
libedit, a user who prefers the
readline behavior for these two keys can
put the following lines in the
file (creating the file if necessary):
bind "^W" ed-delete-prev-word bind "^U" vi-kill-line-prev
To see the current set of key bindings, temporarily put a line
that says only
bind at the end of
.editrc. mysql will
show the bindings when it starts.
Windows provides APIs based on UTF-16LE for reading from and
writing to the console; the mysql client
for Windows is able to use these APIs. The Windows installer
creates an item in the MySQL menu named
line client - Unicode. This item invokes the
mysql client with properties set to
communicate through the console to the MySQL server using
To take advantage of this support manually, run mysql 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:
Open a console window.
Go to the console window properties, select the font tab, and choose Lucida Console or some other compatible Unicode font. This is necessary because console windows start by default using a DOS raster font that is inadequate for Unicode.
Execute mysql.exe with the
utf8mb4) option. This option is necessary because
utf16leis one of the character sets that cannot be used as the client character set. See Impermissible Client Character Sets.
With those changes, mysql will use 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 run
mysql, you can create a shortcut that
invokes mysql.exe. The shortcut should set
the console font to Lucida Console or some other compatible
Unicode font, and pass the
utf8mb4) option to
Alternatively, create a shortcut that only sets the console
font, and set the character set in the
[mysql] group of your
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:50 time_zone: +0200 mail_from: Monty reply: email@example.com mail_to: "Thimble Smith" <firstname.lastname@example.org> sbj: UTF-8 txt: >>>>> "Thimble" == Thimble Smith writes: Thimble> Hi. I think this is a good idea. Is anyone familiar Thimble> with UTF-8 or Unicode? Otherwise, I'll put this on my Thimble> TODO list and see what happens. Yes, please do that. Regards, Monty file: inbox-jani-1 hash: 190402944 1 row in set (0.09 sec)
For beginners, a useful startup option is
which has the same effect). It is helpful for cases when you
might have issued a
WHERE clause. Normally, such
a statement deletes all rows from the table. With
--safe-updates, you can delete
rows only by specifying the key values that identify them.
This helps prevent accidents.
SET sql_safe_updates=1, sql_select_limit=1000, max_join_size=1000000;
statement has the following effects:
UPDATE tbl_name SET not_key_column=val WHERE key_column=val; UPDATE tbl_name SET not_key_column=val LIMIT 1;
The server limits all large
SELECTresults to 1,000 rows unless the statement includes a
The server aborts multiple-table
SELECTstatements that probably need to examine more than 1,000,000 row combinations.
To specify limits different from 1,000 and 1,000,000, you can
override the defaults by using the
shell> mysql --safe-updates --select_limit=500 --max_join_size=10000
If the mysql 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 if mysql 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 away No connection. Trying to reconnect... Connection id: 1 Current database: test Query OK, 1 row affected (1.30 sec) mysql> SELECT * FROM t; +------+ | a | +------+ | NULL | +------+ 1 row in set (0.05 sec)
@a user variable has been lost with the
connection, and after the reconnection it is undefined. If it
is important to have mysql terminate with
an error if the connection has been lost, you can start the
mysql client with the
For more information about auto-reconnect and its effect on state information when a reconnection occurs, see Section 27.8.20, “C API Automatic Reconnection Control”.