MySQL Client中一些被遗忘的功能

vlambda
2020-05-23

MySQL Client中一些被遗忘的功能

mysql client的一些命令确实有很多有意思的地方。这些“奇巧淫技”也确实很有用，方便日常的操作。\G、\c、\q、\P、\R这些常用的就不说了，其他的一些命令对我来说，根本不是被遗忘了，而是我小白了。那今天就填补一下这些小白。

如果不知道还有那些其他的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)]>\euse 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.logLogging to file '/tmp/sql.log'

5、执行系统命令!, system

msandbox@localhost [test]>\! cat /tmp/sql.logmsandbox@localhost [test]>msandbox@localhost [test]>msandbox@localhost [test]>use testDatabase changedmsandbox@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: 22Current database: testCurrent user: msandbox@localhostSSL: Not in useCurrent pager: stdoutUsing outfile: '/tmp/sql.log'Using delimiter: ;Server version: 5.5.36-log MySQL Community Server (GPL)Protocol version: 10Connection: Localhost via UNIX socketServer characterset: utf8Db characterset: utf8Client characterset: utf8Conn. characterset: utf8UNIX socket: /tmp/mysql_sandbox5536.sockUptime: 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)