
MySQL客户端的进阶操作
引言
作为一个MySQL数据库从业者,我们最常用的工具就是mysql了,然而如何让它变的更好用,你了解吗?
mysql Client Commands
作为连接MySQL数据库的工具,mysql其实有很多非常有用的命令设置,有一些是我们日常使用的,比如\g、\G、\q,也有我们不太常用的\P、\T。今天分享一下我对这些设置的理解,希望能对大家在日常的工作中,有所帮助。
支持哪些设置
mysql提供了help命令,用以说明支持哪些命令的设置,我们先看一下
?
显示帮助信息,列出所有支持的操作命令,这个最简单,就不做进一步说明了。
clear(\c)
MySQL手册介绍为清除当前输入的SQL语句,我个人更愿意理解为撤销已输入SQL的执行。看下面的例子:
当我输入完上面的SQL时,发现逻辑未考虑全,不想执行这个SQL了,怎么办呢?这里有好几种解决办法:你可以ctrl+c 终止命令的执行,也可以关闭当前的mysql客户端,甚至关闭操作命令的电脑。然后有一种标准且简单的方式,就是在命令的最后加上\c
,就可以实现:
可以看到,在命令窗口最后输入\c
后,SQL不会被执行,并且重新启动新的命令行接收客户端输入。需要注意的是:\c
和需要取消的SQL间,不能有分隔符(默认为分号;
),否则会先执行分隔符前面的SQL,然后再执行\c
,这时就达不到取消已输入SQL执行的效果了。
connect(\r)
重新连接数据库服务端,支持重连过程中,指定database名字和连接主机。
这个功能看起来好像没什么新奇的,客户端执行了SQL,就算连接超时了,默认情况下,自动就会重新连接服务端。
但是在MGR、主从复制场景下,如果所有实例的维护账号都具有相同的用户名、密码、端口,那么通过\r
就很方便的切换多个实例进行维护,而不需要重新输入其他连接信息
重连信息中的ip地址,也可以是在/etc/hosts中配置的主机名
delimiter(\d)
自定义分隔符,在创建、修改存储过程、函数、触发器、事件、视图都会用到,替换用于替换默认的分号分隔符。
edit(\e)
官网解释说命令用于编辑当前输入SQL命令,默认的编辑器是vi,也可以通过设置环境变量EDITOR来改变成其他的编辑器,比如调整为vim编辑器export EDITOR=$(which vim)
。
有这个命令后,输出错误的SQL,就不需要再用\c
终止了,而是直接在其后加上\e
进行编辑,修改成正确的后,再执行。
比如我实际上想要执行的命令中tt2表不存在,那么只需要通过\e
更新SQL语句中的表名字就可以继续执行,不需要再重新编辑整条SQL。
vi中替换表名字的操作就不再演示了,编辑后的执行情况如下:
另外在测试过程中,还发现\e
可以对上一次执行的SQL进行编辑,即单独执行\e
时,其实是对上次执行的SQL命令做编辑。
这里有人就会想到,我按向上的方向键,也能编辑上一条SQL呀,何必这么麻烦,这里有下面的SQL情况,有多次换行,或者结构更复杂的SQL
如果按上的方向键,整个SQL语句结构就发生了变化,本来规整的SQL语句,变成了很长的一行,很难调整,但是使用\e
就不会有这种情况,会保持之前的输入结构,更方便语句的调整
另外,有同学经常苦恼,我昨天才输入的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来实现,有这个工具后,我们看看效果
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]域进行配置
4、如果session中通过\R xxx
临时修改了命令行提示信息,可以通过单独执行\R
来恢复默认设置。
quit(\q)
退出当前session连接。
auto-rehash(#)
在使用mysql客户端连接服务器时,默认情况下会自动收集一些元数据信息,在后续输入SQL命令时可以通过tab键补齐命令,比如补齐表名字、列名字。
但是如果在session中新建了表,或者给表上新增了字段,是无法通过tab键补齐的,这时通过\#
命令刷新元数据信息,之后就能对新的DDL结构进行补齐
source(\.)
命令后需要跟一个文件名,\. filename
会对filename文件中的内容按标准SQL进行解析执行。
status(\s)
输出本次连接的相关信息及服务器的一些信息,如果连接时指定了--safe-updates
,还会输出查询限制相关的信息,\s
输出了很多有用的信息,可仔细阅读。
system(!)
使用默认命令解释器执行给定命令,简单的说就是返回到操作系统执行\!
之后的命令,比如下面
在8.0.19之前,只支持在unix系统中使用该命令,到8.0.19后,在windows中也支持了该命令。
tee(\T)
将所有执行的SQL命令及输出结果保存到指定文件中。这在调测、生产维护过程中,都是非常有用的一个功能,特别是一些安全要求高的环境中,控制台只能显示几十行命令时,想要查找之前执行的命令及执行的结果比较难,此时就能用上\T
了。
如果想要记录每个人登录数据库,做了哪些操作,由于tee不支持根据当前时间动态产生日志文件名,我们可以这样设置
我们发现指定的日志目录中,已经有了日志文件,并且记录了所有执行的SQL及输出结果
notee(\t)
取消\T
设置,不再记录操作信息到日志文件中。
use(\u)
切换当前连接的database。
warnings(\W)
在执行完SQL语句后,立即显示warning信息,不需要再手动执行show warnings;
了。
nowarnings(\w)
在执行完SQL语句后,不立即显示warning信息,需要手动执行show warnings;
才会显示warning信息。
resetconnection(\x)
以新连接的状态重新连接到服务器,并且进行一些信息的清理及复位,不需要再次进行权限验证。主要影响如下信息:
- 回滚所有活动的事务,并重置自动提交模式。
- 所有DML锁均已释放。
- 所有TEMPORARY table 均已关闭(并删除)。
- 会话系统变量将重新初始化。
- 用户变量设置丢失。
- 准备好的语句被释放。
- HANDLER关闭。
- LAST_INSERT_ID 置为 0。
- 用 GET_LOCK 释放。
可以参考8.0.26的代码libmysql.cc中4429~4444行
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)
文章转载自公众号:GreatSQL社区
