This section provides information about techniques for more effective use of mysql and about mysql operational behavior.本节提供有关更有效地使用mysql的技术和mysql操作行为的信息。
mysql supports input-line editing, which enables you to modify the current input line in place or recall previous input lines. mysql支持输入行编辑,这使您能够就地修改当前输入行或调用以前的输入行。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.Backspace删除游标前的字符,并在游标位置键入新字符。要输入该行,请按Enter键。
On Windows, the editing key sequences are the same as supported for command editing in console windows. 在Windows上,编辑键序列与控制台窗口中支持的命令编辑相同。On Unix, the key sequences depend on the input library used to build mysql (for example, the 在Unix上,键序列取决于用于构建mysql的输入库(例如,libedit
or readline
library).libedit
或readline
库)。
Documentation for the libedit
and readline
libraries is available online. libedit
和readline
库的文档可以在网上找到。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
..editrc
用于libedit
,.inputrc
用于readline
。
For example, in 例如,在libedit
, Control+W deletes everything before the current cursor position and Control+U deletes the entire line. libedit
中,Control+W删除当前游标位置之前的所有内容,Control+U删除整行。In 在读行中,Control+W删除游标前的单词,Control+U删除当前游标位置前的所有内容。readline
, Control+W deletes the word before the cursor and Control+U deletes everything before the current cursor position. If mysql was built using 如果mysql是使用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):libedit
构建的,那么喜欢这两个键的readline
行为的用户可以将以下行放入.editrc
文件中(必要时创建文件):
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
. .editrc
末尾临时放置一行“only bind”。mysql shows the bindings when it starts.mysql在启动时显示绑定。
The up-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.mysql支持部分或完全禁用交互历史记录,具体取决于主机平台。
On Windows, the history is stored in memory. Alt+F7 deletes all input lines stored in memory for the current history buffer. 在Windows上,历史记录存储在内存中。Alt+F7删除当前历史缓冲区内存中存储的所有输入行。It also deletes the list of sequential numbers in front of the input lines displayed with F7 and recalled (by number) with F9. 它还删除了F7显示和F9调用(按数字)的输入行前面的序列号列表。New input lines entered after you press Alt+F7 repopulate the current history buffer. 按Alt+F7后输入的新输入行将重新填充当前历史缓冲区。Clearing the buffer does not prevent logging to the Windows Event Viewer, if the 如果使用--syslog
option was used to start mysql. --syslog
选项启动mysql,则清除缓冲区不会阻止记录到Windows事件查看器。Closing the console window also clears the current history buffer.关闭控制台窗口也会清除当前历史缓冲区。
To disable interactive history on Unix, first delete the 要在Unix上禁用交互式历史记录,请先删除.mysql_history
file, if it exists (previous entries are recalled otherwise). .mysql_history
文件(如果存在)(否则将调用以前的条目)。Then start mysql with the 然后使用--histignore="*"
option to ignore all new input lines. --histignore="*"
选项启动mysql,忽略所有新的输入行。To re-enable the recall (and logging) behavior, restart mysql without the option.要重新启用回调(和日志记录)行为,请在不使用该选项的情况下重新启动mysql。
If you prevent the 如果您阻止创建.mysql_history
file from being created (see Controlling the History File) and use --histignore="*"
to start the mysql client, the interactive history recall facility is disabled fully. .mysql_history
文件(请参阅控制历史文件)并使用--histignore="*"
启动mysql客户端,则交互式历史回忆功能将完全禁用。Alternatively, if you omit the 或者,如果省略--histignore
option, you can recall the input lines entered during the current session.--histignore
选项,则可以回忆起在当前会话期间输入的输入行。
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. Windows提供基于UTF-16LE的API,用于对控制台进行读写操作;Windows的mysql客户端能够使用这些API。The Windows installer creates an item in the MySQL menu named Windows安装程序在MySQL菜单中创建了一个名为MySQL命令行客户端-Unicode的项目。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.此项调用mysql客户端,其属性设置为使用Unicode通过控制台与mysql服务器通信。
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:要手动利用此支持,请在使用兼容Unicode字体的控制台中运行mysql,并将默认字符集设置为支持与服务器通信的Unicode字符集:
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.转到控制台窗口属性,选择字体选项卡,然后选择Lucida console或其他兼容的Unicode字体。这是必要的,因为控制台窗口默认使用DOS光栅字体启动,而该字体不适合Unicode。
Execute mysql.exe with the 使用--default-character-set=utf8
(or utf8mb4
) option. --default-character-set=utf8
(或utf8mb4
)选项执行mysql.exe
。This option is necessary because 此选项是必需的,因为utf16le
is one of the character sets that cannot be used as the client character set. utf16le
是不能用作客户端字符集的字符集之一。See Impermissible Client Character Sets.请参见不允许的客户端字符集。
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.)通过这些更改,mysql使用Windows API使用UTF-16LE与控制台通信,并使用UTF-8与服务器通信。(前面提到的菜单项设置了刚才描述的字体和字符集。)
To avoid those steps each time you run mysql, you can create a shortcut that invokes mysql.exe. 为了避免每次运行mysql时都出现这些步骤,您可以创建一个调用mysql.exe的快捷方式。The shortcut should set the console font to Lucida Console or some other compatible Unicode font, and pass the 快捷方式应将控制台字体设置为Lucida console或其他兼容的Unicode字体,并将--default-character-set=utf8
(or utf8mb4
) option to mysql.exe.--default-character-set=utf8
(或utf8mb4
)选项传递给mysql.exe。
Alternatively, create a shortcut that only sets the console font, and set the character set in the 或者,创建一个只设置控制台字体的快捷方式,并在myini文件的[mysql]组中设置字符集:[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:一些查询结果在垂直显示时比通常的水平表格式更具可读性。通过用\G而不是分号终止查询,可以垂直显示查询。例如,包含换行符的较长文本值在垂直输出时通常更容易阅读:
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
(或--i-am-a-dummy
,具有相同的效果)。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. UPDATE
或DELETE
语句但忘记了指示要修改哪些行的WHERE
子句的情况很有帮助。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. --safe-updates
,您只能通过指定标识行的键值或LIMIT
子句或两者来修改行。This helps prevent accidents. Safe-updates mode also restricts 这有助于防止事故发生。安全更新模式还限制了产生(或估计会产生)非常大的结果集的SELECT
statements that produce (or are estimated to produce) very large result sets.SELECT
语句。
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:--safe-updates
选项使mysql在连接到mysql服务器时执行以下语句,以设置sql_safe_updates
、sql_select_limit
和max_join_size
系统变量的会话值:
SET sql_safe_updates=1, sql_select_limit=1000, max_join_size=1000000;
The SET
statement affects statement processing as follows:SET
语句对语句处理的影响如下:
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:sql_safe_updates
会导致UPDATE
和DELETE
语句产生错误,如果它们没有在WHERE
子句中指定键约束,或者提供LIMIT
子句,或者两者都没有。例如:
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.sql_select_limit
设置为1000会导致服务器将所有SELECT
结果集限制为1000行,除非该语句包含LIMIT
子句。
Setting 如果服务器估计它必须检查超过1000000行组合,则将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.max_join_size
设置为1000000
会导致多个表SELECT
语句产生错误。
To specify result set limits different from 1,000 and 1,000,000, you can override the defaults by using the 要指定不同于1000和1000000的结果集限制,您可以在调用mysql时使用--select-limit
and --max-join-size
options when you invoke mysql:--select-limit
和--max-join-size
选项覆盖默认值:
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:WHERE
子句中指定了键,UPDATE
和DELETE
语句也可能在安全更新模式下产生错误:
Range access on the index cannot be used if memory usage exceeds that permitted by the 如果内存使用量超过range_optimizer_max_mem_size
system variable. range_optimizer_max_mem_size
系统变量允许的范围,则无法使用索引的范围访问。The optimizer then falls back to a table scan. See Limiting Memory Use for Range Optimization.然后,优化器返回到表扫描。请参阅限制内存使用以优化范围。
If key comparisons require type conversion, the index may not be used (see Section 8.3.1, “How MySQL Uses Indexes”). 如果键比较需要类型转换,则可以不使用索引(请参阅第8.3.1节,“MySQL如何使用索引”)。Suppose that an indexed string column 假设使用c1
is compared to a numeric value using WHERE c1 = 2222
. WHERE c1=2222
将索引字符串列c1
与数值进行比较。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.对于此类比较,将字符串值转换为数字,并对操作数进行数值比较(参阅第12.3节,“表达式求值中的类型转换”),从而防止使用索引。如果启用了安全更新模式,则会发生错误。
As of MySQL 8.0.13, safe-updates mode also includes these behaviors:从MySQL 8.0.13开始,安全更新模式还包括以下行为:
带EXPLAIN
with UPDATE
and DELETE
statements does not produce safe-updates errors. UPDATE
和DELETE
语句的EXPLAIN
不会产生安全更新错误。This enables use of 这允许使用EXPLAIN
plus SHOW WARNINGS
to see why an index is not used, which can be helpful in cases such as when a range_optimizer_max_mem_size
violation or type conversion occurs and the optimizer does not use an index even though a key column was specified in the WHERE
clause.EXPLAIN
和SHOW WARNINGS
来查看为什么不使用索引,这在发生range_optimizer_max_mem_size
违规或类型转换,并且即使在WHERE
子句中指定了键列,优化器也不使用索引的情况下非常有用。
When a safe-updates error occurs, the error message includes the first diagnostic that was produced, to provide information about the reason for failure. 当发生安全更新错误时,错误消息包括产生的第一个诊断,以提供有关失败原因的信息。For example, the message may indicate that the 例如,该消息可能表示超出了range_optimizer_max_mem_size
value was exceeded or type conversion occurred, either of which can preclude use of an index.range_optimizer_max_mem_size
值或发生了类型转换,这两种情况都可能阻止使用索引。
For multiple-table deletes and updates, an error is produced with safe updates enabled only if any target table uses a table scan.对于多个表的删除和更新,只有当任何目标表使用表扫描时,才会在启用安全更新的情况下产生错误。
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. 如果mysql客户端在发送语句时失去了与服务器的连接,它会立即自动尝试重新连接到服务器并再次发送语句。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. 但是,即使mysql成功重新连接,您的第一个连接也已结束,您之前的所有会话对象和设置都将丢失:临时表、自动提交模式以及用户定义和会话变量。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. @a
用户变量已随连接丢失,重新连接后未定义。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 如果连接丢失时让mysql终止并出错很重要,您可以使用--skip-reconnect
option.--skip-reconnect
选项启动mysql客户端。
For more information about auto-reconnect and its effect on state information when a reconnection occurs, see 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. mysql客户端在客户端使用的解析器与服务器端mysqld服务器使用的完整解析器不同。This can lead to differences in treatment of certain constructs. Examples:这可能会导致某些结构的治疗差异。示例:
The server parser treats strings delimited by 如果启用了ANSI_QUOTES SQL模式,服务器解析器会将由"
characters as identifiers rather than as plain strings if the ANSI_QUOTES
SQL mode is enabled."
字符分隔的字符串视为标识符,而不是纯字符串。
The mysql client parser does not take the mysql客户端解析器不考虑ANSI_QUOTES
SQL mode into account. ANSI_QUOTES
SQL模式。It treats strings delimited by 无论是否启用ANSI_QUOTES,它都将以"
, '
, and `
characters the same, regardless of whether ANSI_QUOTES
is enabled."
、'
和`
字符分隔的字符串视为相同。
Within 在/*! ... */
and /*+ ... */
comments, the mysql client parser interprets short-form mysql commands. /*! ... */
和/*+ ... */
注释内部,mysql客户端解析器解释简短的mysql命令。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 如果希望mysql不在注释中解释短格式命令,一个部分的解决方法是使用--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).--binary-mode
选项,这会导致在非交互模式下禁用所有mysql命令,但\C
和\d
除外(用于通过管道传输到mysql或使用source
命令加载的输入)。