MySQL客户端的进阶操作

• GreatSQL社区原创内容未经授权不得随意使用，转载请联系小编并注明来源。
• GreatSQL是MySQL的国产分支版本，使用上与MySQL一致。
• 作者：土豆娃娃
• 文章来源：社区投稿

引言

作为一个MySQL数据库从业者，我们最常用的工具就是mysql了，然而如何让它变的更好用，你了解吗？

mysql Client Commands

作为连接MySQL数据库的工具，mysql其实有很多非常有用的命令设置，有一些是我们日常使用的，比如\g、\G、\q，也有我们不太常用的\P、\T。今天分享一下我对这些设置的理解，希望能对大家在日常的工作中，有所帮助。

支持哪些设置

mysql提供了help命令，用以说明支持哪些命令的设置，我们先看一下

Great[(none)]> help ;

For server side help, type 'help contents'

?

显示帮助信息，列出所有支持的操作命令，这个最简单，就不做进一步说明了。

clear(\c)

MySQL手册介绍为清除当前输入的SQL语句，我个人更愿意理解为撤销已输入SQL的执行。看下面的例子：

Great[test]> select * from tt1;
+------+------+------+------+
| id | dd | c1 | c2 |
+------+------+------+------+
| 1 | aaa | NULL | NULL |
| 2 | bbb | NULL | NULL |
| 3 | NULL | NULL | NULL |
| 4 | 8 | NULL | NULL |
| 5 | NULL | NULL | NULL |
+------+------+------+------+
5 rows in set (0.01 sec)

Great[test]> delete from tt1
-> where id = 5
-> and c1 is not null
->

当我输入完上面的SQL时，发现逻辑未考虑全，不想执行这个SQL了，怎么办呢？这里有好几种解决办法：你可以ctrl+c 终止命令的执行，也可以关闭当前的mysql客户端，甚至关闭操作命令的电脑。然后有一种标准且简单的方式，就是在命令的最后加上\c，就可以实现：

Great[test]> delete from tt1
-> where id = 5
-> and c1 is not null
-> \c
Great[test]>

可以看到，在命令窗口最后输入\c后，SQL不会被执行，并且重新启动新的命令行接收客户端输入。需要注意的是：\c和需要取消的SQL间，不能有分隔符(默认为分号;)，否则会先执行分隔符前面的SQL，然后再执行\c，这时就达不到取消已输入SQL执行的效果了。

connect(\r)

重新连接数据库服务端，支持重连过程中，指定database名字和连接主机。

这个功能看起来好像没什么新奇的，客户端执行了SQL，就算连接超时了，默认情况下，自动就会重新连接服务端。

但是在MGR、主从复制场景下，如果所有实例的维护账号都具有相同的用户名、密码、端口，那么通过\r就很方便的切换多个实例进行维护，而不需要重新输入其他连接信息

Great[test]> select @@report_host;
+---------------+
| @@report_host |
+---------------+
| 172.16.50.82 |
+---------------+
1 row in set (0.00 sec)

Great[test]>

重连信息中的ip地址，也可以是在/etc/hosts中配置的主机名

Great[test]> select @@report_host;
+---------------+
| @@report_host |
+---------------+
| 172.16.50.81 |
+---------------+
1 row in set (0.00 sec)

Great[test]>

delimiter(\d)

自定义分隔符，在创建、修改存储过程、函数、触发器、事件、视图都会用到，替换用于替换默认的分号分隔符。

edit(\e)

官网解释说命令用于编辑当前输入SQL命令，默认的编辑器是vi，也可以通过设置环境变量EDITOR来改变成其他的编辑器，比如调整为vim编辑器export EDITOR=$(which vim)。

有这个命令后，输出错误的SQL，就不需要再用\c终止了，而是直接在其后加上\e进行编辑，修改成正确的后，再执行。

比如我实际上想要执行的命令中tt2表不存在，那么只需要通过\e更新SQL语句中的表名字就可以继续执行，不需要再重新编辑整条SQL。

Great[test]> select * from test.tt2 \e

vi中替换表名字的操作就不再演示了，编辑后的执行情况如下：

Great[test]> select * from test.tt2 \e
-> ;
+------+------+------+------+
| id | dd | c1 | c2 |
+------+------+------+------+
| 1 | aaa | NULL | NULL |
| 2 | bbb | NULL | NULL |
| 3 | NULL | NULL | NULL |
| 4 | 8 | NULL | NULL |
| 5 | NULL | NULL | NULL |
+------+------+------+------+
5 rows in set (0.01 sec)

Great[test]>

另外在测试过程中，还发现\e可以对上一次执行的SQL进行编辑，即单独执行\e时，其实是对上次执行的SQL命令做编辑。

Great[test]> \e
select * from test.tt1

这里有人就会想到，我按向上的方向键，也能编辑上一条SQL呀，何必这么麻烦，这里有下面的SQL情况，有多次换行，或者结构更复杂的SQL

Great[test]> select * from test.z1
-> join test.z2
-> using(id)
-> limit 3;
+------+------+------+
| id | name | name |
+------+------+------+
| 1 | 11 | 11 |
| 2 | 22 | 22 |
| 11 | 11 | 11 |
+------+------+------+
3 rows in set (0.00 sec)

如果按上的方向键，整个SQL语句结构就发生了变化，本来规整的SQL语句，变成了很长的一行，很难调整，但是使用\e就不会有这种情况，会保持之前的输入结构，更方便语句的调整

Great[test]> select * from test.z1
-> join test.z2
-> using(id)
-> limit 3;
+------+------+------+
| id | name | name |
+------+------+------+
| 1 | 11 | 11 |
| 2 | 22 | 22 |
| 11 | 11 | 11 |
+------+------+------+
3 rows in set (0.00 sec)

select * from test.z1
join test.z2
using(id)
limit 3

另外，有同学经常苦恼，我昨天才输入的SQL命令，如果需要重新执行，又得输入一遍，其实这里也有一个小技巧，通过快捷键CTRL+R，输入SQL中的关键信息(比如表名字)，就能快速翻出对应的SQL，如果匹配出来的行不是想要的SQL，可以继续按CTRL+R继续上翻，直到查找到需要的SQL，当然，也有可能需要的SQL已经被清理出历史记录中，这种情况是无法被找到的。

ego(\G)

提交SQL语句到服务器，并且将返回的数据列式显示。

exit(\q)

退出mysql客户端连接。

go(\g)

提交SQL语句到服务器。

pager(\P)

设置pager规则，对查询结果执行pager规则后，再输出结果。这也是一个非常有用的设置，我们常常因为processlist结果太多而不方便查看，需要通过单行grep或者查询information_schema.processlist来实现，有这个工具后，我们看看效果

Great[test]> show processlist;
+---------+-----------------+--------------------+------+---------+---------+--------------------------------------------------------+----------------------------------+------------+-----------+---------------+
| Id | User | Host | db | Command | Time | State | Info | Time_ms | Rows_sent | Rows_examined |
+---------+-----------------+--------------------+------+---------+---------+--------------------------------------------------------+----------------------------------+------------+-----------+---------------+
| 6 | event_scheduler | localhost | NULL | Daemon | 5019248 | Waiting on empty queue | NULL | 5019247326 | 0 | 0 |
| 26 | system user | | NULL | Connect | 5018577 | waiting for handler commit | Group replication applier module | 5018576436 | 0 | 0 |
| 29 | system user | | NULL | Query | 1010 | Slave has read all relay log; waiting for more updates | NULL | 1010045 | 0 | 0 |
| 30 | system user | | NULL | Query | 1010 | Waiting for an event from Coordinator | NULL | 1010045 | 0 | 0 |
| 31 | system user | | NULL | Query | 100958 | Waiting for an event from Coordinator | NULL | 100956966 | 0 | 0 |
| 32 | system user | | NULL | Query | 100958 | Waiting for an event from Coordinator | NULL | 100956966 | 0 | 0 |
| 33 | system user | | NULL | Connect | 5018577 | Waiting for an event from Coordinator | NULL | 5018576419 | 0 | 0 |
| 34 | system user | | NULL | Connect | 5018577 | Waiting for an event from Coordinator | NULL | 5018576418 | 0 | 0 |
| 35 | system user | | NULL | Connect | 5018577 | Waiting for an event from Coordinator | NULL | 5018576417 | 0 | 0 |
| 36 | system user | | NULL | Connect | 5018577 | Waiting for an event from Coordinator | NULL | 5018576415 | 0 | 0 |
| 37 | system user | | NULL | Connect | 5018577 | Waiting for an event from Coordinator | NULL | 5018576413 | 0 | 0 |
+---------+-----------------+--------------------+------+---------+---------+--------------------------------------------------------+----------------------------------+------------+-----------+---------------+
14 rows in set (0.00 sec)

Great[test]>

pager后面可以跟很多的shell命令，比如awk、grep、wc ，对结果集的处理，当SQL不方便处理而shell方便处理时，不用再使用-e参数每次都进行连接，然后处理，比如主从结构对Slave_IO_Running、Slave_SQL_Running的监控，可设置pager grep -E 'Slave_IO_Running|Slave_SQL_Running'。

pager的另外一个用途：经常有同学问，一些字段中是否有包含某些特定字符串，正常的SQL处理是需要写成col1 like '%abc%' or col2 like '%abc%'...，写出来后SQL结构相对复杂，通过pager设置pager grep -i abc; select * from tab即可方便查看。

nopager(\n)

pager的设置是整个session生命周期内都生效，通过执行nopager进行关闭设置

prompt(\R)

修改mysql客户端的命令行提示信息，支持显示的提示信息非常多，具体可以参见MySQL官网介绍。修改mysql客户端的命令行提示信息，也有好几种方式：

1、设置操作系统环境变量MYSQL_PS1 export MYSQL_PS1= " \D_\h_\p_\u > "，格式为"时间_主机_端口_用户名"

2、通过客户端的命令prompt修改 \R \D_\h_\p_\u >

3、通过my.cnf的[mysql]域进行配置

[mysql]
prompt="\R \D_\h_\p_\u > "

4、如果session中通过\R xxx临时修改了命令行提示信息，可以通过单独执行\R来恢复默认设置。

quit(\q)

退出当前session连接。

auto-rehash(#)

在使用mysql客户端连接服务器时，默认情况下会自动收集一些元数据信息，在后续输入SQL命令时可以通过tab键补齐命令，比如补齐表名字、列名字。

Great[test]> select * from tt(此时按tab键)
tt1 tt1.c1 tt1.c2 tt1.dd tt1.id ttt ttt.id

但是如果在session中新建了表，或者给表上新增了字段，是无法通过tab键补齐的，这时通过\#命令刷新元数据信息，之后就能对新的DDL结构进行补齐

Great[test]> create table tt2 as select * from tt1;
Query OK, 5 rows affected (0.09 sec)
Records: 5 Duplicates: 0 Warnings: 0

Great[test]> select * from tt(此时按tab键)
tt1 tt1.c1 tt1.c2 tt1.dd tt1.id ttt ttt.id
Great[test]> \#
Great[test]> select * from tt(此时按tab键)
tt1 tt1.c1 tt1.c2 tt1.dd tt1.id tt2 tt2.c1 tt2.c2 tt2.dd tt2.id ttt ttt.id
Great[test]> select * from tt

source(.)

命令后需要跟一个文件名，\. filename会对filename文件中的内容按标准SQL进行解析执行。

status(\s)

输出本次连接的相关信息及服务器的一些信息，如果连接时指定了--safe-updates，还会输出查询限制相关的信息，\s输出了很多有用的信息，可仔细阅读。

[#8#root@Great81 ~ 20:26:13]8 m5 3306 --safe-updates
greatsql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 916197

Great[(none)]>Great

system(!)

使用默认命令解释器执行给定命令，简单的说就是返回到操作系统执行\!之后的命令，比如下面

Great[(none)]> \! date
2022年 11月 16日 星期三 20:32:34 CST
Great[(none)]> \! pwd
/root
Great[(none)]> \! cd /Great
Great[(none)]> \! vmstat -w
procs -----------------------memory---------------------- ---swap-- -----io---- -system-- --------cpu--------
r b swpd free buff cache si so bi bo in cs us sy id wa st
1 0 0 358508 0 3117236 0 0 486 20 0 0 5 3 92 0 0
Great[(none)]>

在8.0.19之前，只支持在unix系统中使用该命令，到8.0.19后，在windows中也支持了该命令。

tee(\T)

将所有执行的SQL命令及输出结果保存到指定文件中。这在调测、生产维护过程中，都是非常有用的一个功能，特别是一些安全要求高的环境中，控制台只能显示几十行命令时，想要查找之前执行的命令及执行的结果比较难，此时就能用上\T了。

Great[(none)]> \T /root/a.log
Logging to file '/root/a.log'
Great[(none)]> select * from test.tt1;
+------+------+------+------+
| id | dd | c1 | c2 |
+------+------+------+------+
| 1 | aaa | NULL | NULL |
| 2 | bbb | NULL | NULL |
| 3 | NULL | NULL | NULL |
| 4 | 8 | NULL | NULL |
| 5 | NULL | NULL | NULL |
+------+------+------+------+
5 rows in set (0.01 sec)

Great[(none)]>

如果想要记录每个人登录数据库，做了哪些操作，由于tee不支持根据当前时间动态产生日志文件名，我们可以这样设置

## 首先创建一个log目录
mkdir -p /Great/logs/client/

mysql>

我们发现指定的日志目录中，已经有了日志文件，并且记录了所有执行的SQL及输出结果

[#9#root@Great81 ~ 20:51:43]9 cd /Great/logs/client/
[#10#root@Great81 /Great/logs/client 20:51:48]10 ll
总用量 8
-rw-r--r-- 1 root root 627 11月 16 20:48 2022-11-16_20-48.log
-rw-r--r-- 1 root root 3214 11月 16 20:50 2022-11-16_20-49.log
[#11#root@Great81 /Great/logs/client 20:51:48]11

notee(\t)

取消\T设置，不再记录操作信息到日志文件中。

use(\u)

切换当前连接的database。

warnings(\W)

在执行完SQL语句后，立即显示warning信息，不需要再手动执行show warnings;了。

nowarnings(\w)

在执行完SQL语句后，不立即显示warning信息，需要手动执行show warnings;才会显示warning信息。

resetconnection(\x)

以新连接的状态重新连接到服务器，并且进行一些信息的清理及复位，不需要再次进行权限验证。主要影响如下信息：

1. 回滚所有活动的事务，并重置自动提交模式。
2. 所有DML锁均已释放。
3. 所有TEMPORARY table 均已关闭(并删除)。
4. 会话系统变量将重新初始化。
5. 用户变量设置丢失。
6. 准备好的语句被释放。
7. HANDLER关闭。
8. LAST_INSERT_ID 置为 0。
9. 用 GET_LOCK 释放。

可以参考8.0.26的代码libmysql.cc中4429~4444行

int STDCALL mysql_reset_connection(MYSQL *mysql) {
DBUG_TRACE;
if (simple_command(mysql, COM_RESET_CONNECTION, nullptr, 0, 0))
return 1;
else {
mysql_detach_stmt_list(&mysql->stmts, "mysql_reset_connection");
/* reset some of the members in mysql */
mysql->insert_id = 0;
mysql->affected_rows = ~(uint64_t)0;
free_old_query(mysql);
mysql->status = MYSQL_STATUS_READY;
mysql_extension_bind_free(MYSQL_EXTENSION_PTR(mysql));
return 0;
}
}

query_attributes

通过query_attributes var1 value1 var2 value2来设置变量，然后通过mysql_query_attribute_string('var1')返回变量值value1，目前来看，并没有发现特殊的使用方法，毕竟我们也可以通过set @var1='value1'来设置。

使用体验

mysql Client Commands在某些场景下，可以极大的提高我们的操作效率，相对于完整的命令ego、pager、edit等，我更喜欢使用他们的简写命令\G、\P、\e，因为有时候完整命令不确定如何正确使用，会导致不生效，但是简写命令，是一定会生效的。

参考文章

[MySQL 8.0 Reference Manual 4.5.1.2 mysql Client Commands]
https://dev.mysql.com/doc/refman/8.0/en/mysql-commands.html

[MySQL 8.0 Reference Manual  9.6 Query Attributes]
https://dev.mysql.com/doc/refman/8.0/en/query-attributes.html