MySQL Client中一些被遗忘的功能

这篇博客不算是技术博客,不过mysql client的一些命令确实有很多有意思的地方。这些“奇巧淫技”也确实很有用,方便日常的操作。\G、\c、\q、\P、\R这些常用的就不说了,其他的一些命令对我来说,根本不是被遗忘了,而是我小白了。那今天就填补一下这些小白,主要参考Dimitri的博客DBA 101: Sometimes forgotten functionality in the MySQL client

如果不知道还有那些其他的mysql client命令,那么请使用help或者\?。

1、help,\? 查看帮助文档

仔细阅读下面的command list!如果都看明白了,请继续看下去,验证一下是否我理解错了或者你理解错了,^.^

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. 
               Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.

For server side help, type 'help contents'

2、设置CLI提示\R, prompt 

mysql>\R \u@\h [\d]>

PROMPT set to '\u@\h [\d]>'

msandbox@localhost [test]>

3、编辑模式\e,eidit

进入vi/vim中编辑sql,编辑完成之后保存,回到mysql CLI中,输入“;”后按enter键即可执行刚才编辑的sql语句。

msandbox@localhost [(none)]>\e
use test;
select * from vvv limit 1;
(wq保存代码)

->;

msandbox@localhost [(none)]>\e
->;
Query OK, 0 rows affected (0.01 sec)
+------+------------+
| id   | name       |
+------+------------+
|    1 | sylar chen |
+------+------------+
1 row in set (0.01 sec)

4、记录操作日志\T,tee

差不多就是linux tee命令的功能,把执行的sql语句以及输出结果保存到指定的文件中。

\t,取消把查询和输出结果记录到文件中。

msandbox@localhost [test]>\T /tmp/sql.log
Logging to file '/tmp/sql.log'

5、执行系统命令\!, system

msandbox@localhost [test]>\! cat /tmp/sql.log
msandbox@localhost [test]>
msandbox@localhost [test]>
msandbox@localhost [test]>use test
Database changed
msandbox@localhost [test]>select * from vvv;
+------+------------+
| id   | name       |
+------+------------+
|    1 | sylar chen |
|   22 | chen       |
+------+------------+
2 rows in set (0.00 sec)

6、查看当前连接的状态信息\s,status

msandbox@localhost [test]>\s
--------------
/usr/local/sandbox/boxes/5.5.36/bin/mysql  
Ver 14.14 Distrib 5.5.36, for linux2.6 (i686) using readline 5.1

Connection id:		22
Current database:	test
Current user:		msandbox@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		'/tmp/sql.log'
Using delimiter:	;
Server version:		5.5.36-log MySQL Community Server (GPL)
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8
Db     characterset:	utf8
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/tmp/mysql_sandbox5536.sock
Uptime:			3 days 3 hours 9 min 44 sec

Threads: 1  Questions: 190  Slow queries: 0  Opens: 38  
            Flush tables: 1  Open tables: 31  Queries per second avg: 0.000
--------------

7、分页显示\P,pager
\P less 通过linux less方式显示输出结果
\P more 通过linux more方式显示输出结果
\P cat /tmp/page.txt 把输出结果保存到/tmp/page.txt中

再次数\p或者输入\n取消把pager输出。

另外把结果输出到 md5sum中,获取md5值,可以用来比较不同select语句的执行结果是否相同。

msandbox@localhost [test]>select * from vvv;
9153498ba9729f0fcef990584c018562  -
2 rows in set (0.00 sec)

msandbox@localhost [test]>select id,name from vvv;
9153498ba9729f0fcef990584c018562  -
2 rows in set (0.00 sec)
此条目发表在mysql分类目录,贴了, 标签。将固定链接加入收藏夹。

MySQL Client中一些被遗忘的功能》有 2 条评论

  1. 西北狼说:

    总结的很好啊

发表评论

电子邮件地址不会被公开。 必填项已用*标注

您可以使用这些HTML标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>