MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
This section provides information about techniques for more effective use of mysql and about mysql operational behavior.
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
libedit
or readline
library).
Documentation for the libedit
and
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: .editrc
for libedit
and .inputrc
for readline
.
For example, in libedit
,
Control+W deletes everything before the current
cursor position and Control+U deletes the
entire line. In readline
,
Control+W deletes the word before the cursor
and Control+U deletes everything before the
current cursor position. If mysql was built
using libedit
, a user who prefers the
readline
behavior for these two keys can put
the following lines in the .editrc
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 then
shows 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 MySQL command line
client - Unicode
. This item invokes the
mysql client with properties set to
communicate through the console to the MySQL server using
Unicode.
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
--default-character-set=utf8
(or utf8mb4
) option. This option is
necessary because utf16le
is one of the
character sets that cannot be used as the client character
set. See
Impermissible Client Character Sets.
With those changes, mysql uses the Windows APIs to communicate with the console using UTF-16LE, and communicates 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
--default-character-set=utf8
(or
utf8mb4
) option to
mysql.exe.
Alternatively, create a shortcut that only sets the console
font, and set the character set in the
[mysql]
group of your
my.ini
file:
[mysql] default-character-set=utf8
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: 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 familiar
John> with UTF-8 or Unicode? Otherwise, I'll put this on my
John> TODO list and see what happens.
Yes, please do that.
Regards,
Jones
file: inbox-jani-1
hash: 190402944
1 row in set (0.09 sec)
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 an
UPDATE
or
DELETE
statement but forgotten
the WHERE
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 a LIMIT
clause, or both.
This helps prevent accidents. Safe-updates mode also restricts
SELECT
statements that produce
(or are estimated to produce) very large result sets.
The --safe-updates
option causes
mysql to execute the following statement when
it connects to the MySQL server, to set the session values of
the sql_safe_updates
,
sql_select_limit
, and
max_join_size
system variables:
SET sql_safe_updates=1, sql_select_limit=1000, max_join_size=1000000;
The SET
statement affects statement processing as follows:
Enabling sql_safe_updates
causes UPDATE
and
DELETE
statements to produce
an error if they do not specify a key constraint in the
WHERE
clause, or provide a
LIMIT
clause, or both. For example:
UPDATEtbl_name
SETnot_key_column
=val
WHEREkey_column
=val
; UPDATEtbl_name
SETnot_key_column
=val
LIMIT 1;
Setting sql_select_limit
to
1,000 causes the server to limit all
SELECT
result sets to 1,000
rows unless the statement includes a
LIMIT
clause.
Setting max_join_size
to
1,000,000 causes multiple-table
SELECT
statements to produce
an error if the server estimates it must examine more than
1,000,000 row combinations.
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
invoke mysql:
mysql --safe-updates --select-limit=500 --max-join-size=10000
It is possible for UPDATE
and
DELETE
statements to produce an
error in safe-updates mode even with a key specified in the
WHERE
clause, if the optimizer decides not to
use the index on the key column. For example, if key comparisons
require type conversion, the index may not be used (see
Section 8.3.1, “How MySQL Uses Indexes”). Suppose that an indexed string
column c1
is compared to a numeric value
using WHERE c1 = 2222
. For such comparisons,
the string value is converted to a number and the operands are
compared numerically (see Section 12.3, “Type Conversion in Expression Evaluation”),
preventing use of the index. If safe-updates mode is enabled, an
error occurs.
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)
The @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
--skip-reconnect
option.
For more information about auto-reconnect and its effect on state information when a reconnection occurs, see C API Automatic Reconnection Control.
The mysql client uses a parser on the client side that is not a duplicate of the complete parser used by the mysqld server on the server side. This can lead to differences in treatment of certain constructs. Examples:
The server parser treats strings delimited by
"
characters as identifiers rather than
as plain strings if the
ANSI_QUOTES
SQL mode is
enabled.
The mysql client parser does not take the
ANSI_QUOTES
SQL mode into account. It
treats strings delimited by "
,
'
, and `
characters
the same, regardless of whether
ANSI_QUOTES
is enabled.
Within /*! ... */
comments, the
mysql client parser interprets short-form
mysql commands. The server parser does
not interpret them because these commands have no meaning on
the server side.
If it is desirable for mysql not to
interpret short-form commands within comments, a partial
workaround is to use the
--binary-mode
option, which
causes all mysql commands to be disabled
except \C
and \d
in
noninteractive mode (for input piped to
mysql or loaded using the
source
command).