4.5.1.6 mysql Client Tips客户端提示

This section provides information about techniques for more effective use of mysql and about mysql operational behavior.本节提供有关更有效地使用mysql的技术和mysql操作行为的信息。

Input-Line Editing输入行编辑

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 libedit or readline library).在Unix上,键序列取决于用于构建mysql的输入库(例如,libeditreadline库)。

Documentation for the libedit and readline libraries is available online. libeditreadline库的文档可以在网上找到。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 readline, Control+W deletes the word before the cursor and Control+U deletes everything before the current cursor position. 在读行中,Control+W删除游标前的单词,Control+U删除当前游标位置前的所有内容。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):如果mysql是使用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在启动时显示绑定。

Disabling Interactive History禁用交互式历史记录

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 .mysql_history file, if it exists (previous entries are recalled otherwise). 要在Unix上禁用交互式历史记录,请先删除.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选项,则可以回忆起在当前会话期间输入的输入行。

Unicode Support on WindowsWindows上的Unicode支持

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 MySQL command line client - Unicode. Windows安装程序在MySQL菜单中创建了一个名为MySQL命令行客户端-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字符集:

  1. Open a console window.打开控制台窗口。

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

  3. Execute mysql.exe with the --default-character-set=utf8 (or utf8mb4) option. 使用--default-character-set=utf8(或utf8mb4)选项执行mysql.exeThis 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 --default-character-set=utf8 (or utf8mb4) option to mysql.exe.快捷方式应将控制台字体设置为Lucida console或其他兼容的Unicode字体,并将--default-character-set=utf8(或utf8mb4)选项传递给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:或者,创建一个只设置控制台字体的快捷方式,并在myini文件的[mysql]组中设置字符集:

[mysql]
default-character-set=utf8
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:一些查询结果在垂直显示时比通常的水平表格式更具可读性。通过用\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)
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(或--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. 安全更新模式对于您可能已经发出UPDATEDELETE语句但忘记了指示要修改哪些行的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_updatessql_select_limitmax_join_size系统变量的会话值:

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

The SET statement affects statement processing as follows:SET语句对语句处理的影响如下:

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:要指定不同于1000和1000000的结果集限制,您可以在调用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子句中指定了键,UPDATEDELETE语句也可能在安全更新模式下产生错误:

As of MySQL 8.0.13, safe-updates mode also includes these behaviors:从MySQL 8.0.13开始,安全更新模式还包括以下行为:

Disabling mysql Auto-Reconnect禁用mysql自动重新连接

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 --skip-reconnect option.如果连接丢失时让mysql终止并出错很重要,您可以使用--skip-reconnect选项启动mysql客户端。

For more information about auto-reconnect and its effect on state information when a reconnection occurs, see Automatic Reconnection Control.有关自动重新连接及其在重新连接时对状态信息的影响的更多信息,请参阅自动重新连接控制

mysql Client Parser Versus Server Parsermysql客户端解析器与服务器解析器

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:这可能会导致某些结构的治疗差异。示例: