获取mysql帮助信息

 在开发或测试环境在碰到mysql相关故障时，如果在没有网络的情况下，我们可能通过mysql自带的帮助信息获得相关的语法等相关信息。如果有网络的话当然可以查询官网或者百度，官网的文档链接http://dev.mysql.com/doc/。

1.mysql客户端的help

 和oracle的sqlplus一样，可以通过mysql客户端的一些设置更改客户端的一些设置，包括输入/输出方式，提示符，客户端字符集，警告信息是否显示等等。

mysql> help
 
For information about MySQL products and services, visit:
   http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
   http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
   https://shop.mysql.com/
 
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.   --重新连接，通常用于被剔除或异常断开后重新连接，SQL*plus下也有这样一个connect命令
delimiter (\d) Set statement delimiter.                                       --设置命令终止符，缺省为；，比如我们可以设定为/来表示语句结束 
edit      (\e) Edit command with $EDITOR.                                     --编辑缓冲区的上一条SQL语句到文件，缺省调用vi，文件会放在/tmp路径下
ego       (\G) Send command to mysql server, display result vertically.       --控制结果显示为垂直显示
exit      (\q) Exit mysql. Same as quit.                                      --退出mysql
go        (\g) Send command to mysql server.                                  --发送命令到mysql服务
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.       --设置pager方式，可以设置为调用more,less等等，主要是用于分页显示
print     (\p) Print current command.                     
prompt    (\R) Change your mysql prompt.                                      --改变mysql的提示符  
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'    --注意这里的描述help contents将获得服务器端的相关帮助信息

测试其中的几个：

\c就是表示当前的命令取消

mysql> select aaaa \c

\r或connect 就是重新连接数据库

mysql> \r l5m
Connection id:    9
Current database: l5m

pager的输出方式 

mysql> pager more
PAGER set to 'more'
mysql> select table_name,table_type,engine from information_schema.tables;

| ROUTINES                                             | SYSTEM VIEW | InnoDB
   |
| SCHEMATA                                             | SYSTEM VIEW | MEMORY
   |
---More---
#以more的方式显示，需要按任一键后显示下一页


mysql> pager tail -5;
PAGER set to 'tail -5'

mysql>  select table_name,table_type,engine from information_schema.tables;
| x$wait_classes_global_by_latency                     | VIEW        | NULL               |
| x$waits_by_host_by_latency                           | VIEW        | NULL               |
| x$waits_by_user_by_latency                           | VIEW        | NULL               |
| x$waits_global_by_latency                            | VIEW        | NULL               |
+------------------------------------------------------+-------------+--------------------+
287 rows in set (0.00 sec)

#只显示最后5行的数据

mysql> nopager;
PAGER set to stdout
#改为默认方法

system 是执行shell命令，而source是直接执行脚本里面的命令

mysql> system more query.sql
use l5m
#相当于在shell环境下执行more query.sql命令

mysql> source query.sql
Database changed
#直接执行query.sql的脚本

prompt修改提示符

mysql> prompt SessionA>
PROMPT set to 'SessionA>'
SessionA>prompt;
Returning to default PROMPT of mysql>

charset设置客户端的字符集

mysql> charset gbk;
Charset changed
mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.7.21, for linux-glibc2.12 (x86_64) using  EditLine wrapper

Connection id:          12
Current database:       l5m
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.21-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    gbk
Conn.  characterset:    gbk
UNIX socket:            /u01/mysql/mysql.sock
Uptime:                 1 hour 4 min 12 sec

Threads: 1  Questions: 71  Slow queries: 0  Opens: 124  Flush tables: 1  Open tables: 117  Queries per second avg: 0.018
--------------

mysql>  charset  utf8
Charset changed
mysql> \s
--------------
mysql  Ver 14.14 Distrib 5.7.21, for linux-glibc2.12 (x86_64) using  EditLine wrapper

Connection id:          12
Current database:       l5m
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.21-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:            /u01/mysql/mysql.sock
Uptime:                 1 hour 4 min 28 sec

Threads: 1  Questions: 75  Slow queries: 0  Opens: 124  Flush tables: 1  Open tables: 117  Queries per second avg: 0.019
--------------

2.服务器端相关的help

客户端help的最后一行显示For server side help, type 'help contents' 

mysql> help contents;
You asked for help about help category: "Contents"
For more information, type 'help <item>', where <item> is one of the following
categories:
   Account Management
   Administration
   Compound Statements
   Data Definition
   Data Manipulation
   Data Types
   Functions
   Functions and Modifiers for Use with GROUP BY
   Geographic Features
   Help Metadata
   Language Structure
   Plugins
   Procedures
   Storage Engines
   Table Maintenance
   Transactions
   User-Defined Functions
   Utility

要查询那一个部分的内容，直接输入help + 内容，如下

mysql> help Account Management
You asked for help about help category: "Account Management"
For more information, type 'help <item>', where <item> is one of the following
topics:
   ALTER USER
   CREATE USER
   DROP USER
   GRANT
   RENAME USER
   REVOKE
   SET PASSWORD

mysql> help  Administration
You asked for help about help category: "Administration"
For more information, type 'help <item>', where <item> is one of the following
topics:
   BINLOG
   CACHE INDEX
   FLUSH
   FLUSH QUERY CACHE
   HELP COMMAND
   KILL
   LOAD INDEX
   RESET
   SET
   SET CHARACTER SET
   SET NAMES
   SHOW
   SHOW BINARY LOGS
   SHOW BINLOG EVENTS
   SHOW CHARACTER SET
   SHOW COLLATION
   SHOW COLUMNS
   SHOW CREATE DATABASE
   SHOW CREATE EVENT
   SHOW CREATE FUNCTION
   SHOW CREATE PROCEDURE
   SHOW CREATE TABLE
   SHOW CREATE TRIGGER
   SHOW CREATE USER
   SHOW CREATE VIEW
   SHOW DATABASES
   SHOW ENGINE
   SHOW ENGINES
   SHOW ERRORS
   SHOW EVENTS
   SHOW FUNCTION CODE
   SHOW FUNCTION STATUS
   SHOW GRANTS
   SHOW INDEX
   SHOW MASTER STATUS
   SHOW OPEN TABLES
   SHOW PLUGINS
   SHOW PRIVILEGES
   SHOW PROCEDURE CODE
   SHOW PROCEDURE STATUS
   SHOW PROCESSLIST
   SHOW PROFILE
   SHOW PROFILES
   SHOW RELAYLOG EVENTS
   SHOW SLAVE HOSTS
   SHOW SLAVE STATUS
   SHOW STATUS
   SHOW TABLE STATUS
   SHOW TABLES
   SHOW TRIGGERS
   SHOW VARIABLES
   SHOW WARNINGS
   SHUTDOWN

可以接着help下面的flush:

mysql> help flush;
Name: 'FLUSH'
Description:
Syntax:
FLUSH [NO_WRITE_TO_BINLOG | LOCAL] {
    flush_option [, flush_option] ...
  | tables_option
}

flush_option: {
    BINARY LOGS
  | DES_KEY_FILE
  | ENGINE LOGS
  | ERROR LOGS
  | GENERAL LOGS
  | HOSTS
  | LOGS
  | PRIVILEGES
  | OPTIMIZER_COSTS
  | QUERY CACHE
  | RELAY LOGS [FOR CHANNEL channel]
  | SLOW LOGS
  | STATUS
  | USER_RESOURCES
}

tables_option: {
    TABLES
  | TABLES tbl_name [, tbl_name] ...
  | TABLES WITH READ LOCK
  | TABLES tbl_name [, tbl_name] ... WITH READ LOCK
  | TABLES tbl_name [, tbl_name] ... FOR EXPORT
}

即通过逐级help的方式即可获得与其主题相关的详细信息。

比较常用的show 命令，通常查看系统变量，状态变量等

mysql> help show;
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:

SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CREATE DATABASE db_name
SHOW CREATE EVENT event_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW CREATE TRIGGER trigger_name
SHOW CREATE VIEW view_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW EVENTS
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW MASTER STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PLUGINS
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW RELAYLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW SLAVE HOSTS
SHOW SLAVE STATUS [FOR CHANNEL channel]
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW [FULL] TABLES [FROM db_name] [like_or_where]
SHOW TRIGGERS [FROM db_name] [like_or_where]
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count]

like_or_where:
    LIKE 'pattern'
  | WHERE expr

If the syntax for a given SHOW statement includes a LIKE 'pattern'
part, 'pattern' is a string that can contain the SQL % and _ wildcard
characters. The pattern is useful for restricting statement output to
matching values.

Several SHOW statements also accept a WHERE clause that provides more
flexibility in specifying which rows to display. See
http://dev.mysql.com/doc/refman/5.7/en/extended-show.html.

URL: http://dev.mysql.com/doc/refman/5.7/en/show.html

以及set的帮助信息：

mysql> help set;
Name: 'SET'
Description:
Syntax:
SET variable_assignment [, variable_assignment] ...

variable_assignment:
      user_var_name = expr
    | param_name = expr
    | local_var_name = expr
    | [GLOBAL | SESSION]
        system_var_name = expr
    | [@@global. | @@session. | @@]
        system_var_name = expr

SET syntax for variable assignment enables you to assign values to
different types of variables that affect the operation of the server or
clients:

o System variables. See
  http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html.
  System variables also can be set at server startup, as described in
  http://dev.mysql.com/doc/refman/5.7/en/using-system-variables.html.
  (To display system variable names and values, use the SHOW VARIABLES
  statement; see [HELP SHOW VARIABLES].)

o User-defined variables. See
  http://dev.mysql.com/doc/refman/5.7/en/user-variables.html.

o Stored procedure and function parameters, and stored program local
  variables. See
  http://dev.mysql.com/doc/refman/5.7/en/stored-program-variables.html.

URL: http://dev.mysql.com/doc/refman/5.7/en/set-variable.html