MySQL语句之show

最新推荐文章于 2025-07-02 16:52:17 发布

jesseyoung

最新推荐文章于 2025-07-02 16:52:17 发布

阅读量4.9k

点赞数

CC 4.0 BY-SA版权

分类专栏：数据仓库-MySQL 文章标签： mysql show

本文链接：https://blog.youkuaiyun.com/JesseYoung/article/details/38264887

数据仓库-MySQL 专栏收录该内容

41 篇文章

订阅专栏

本文详细介绍了MySQL中各种SHOW语句的用途和基本语法，包括SHOW DATABASES、SHOW TABLES、SHOW COLUMNS、SHOW CREATE TABLE等，以及用于查看服务器状态、存储引擎、触发器、事件、函数和过程的语句。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1 简介

SHOW有多种形式，可以提供有关数据库、表、列或服务器状态的信息。

2 创建测试数据

create table show_table(    
    c1 int not null auto_increment,     
    c2 varchar(10),     
    c3 int(10),     
    primary key(c1)    
)engine=innodb;    
insert into show_table values('', 'name1', 001);    
insert into show_table values('', 'name2', 002);    
insert into show_table values('', 'name3', 003);    
insert into show_table values('', 'name4', 004);    
insert into show_table values('', 'name5', 005);
 
create table show_table_sec(    
    c1 int not null auto_increment,     
    c2 varchar(10),     
    c3 int(10),     
    primary key(c1)    
)engine=innodb;

3 详细验证

3.1 SHOW AUTHORS 语句

作用：

显示mysql项目组人员信息。

基本语法：

SHOW AUTHORS

实例：

SHOW AUTHORS;

3.2 SHOW BINARY LOGS 语句

作用：

列出数据库服务器的二进制信息。

基本语法：

SHOW BINARY LOGS
SHOW MASTER LOGS

实例：

mysql> SHOW BINARY LOGS; 
mysql> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |     27338 |
| mysql-bin.000002 |   1035309 |
| mysql-bin.000003 |       126 |
| mysql-bin.000004 |       126 |
+------------------+-----------+

3.3 SHOW BINLOG EVENTS 语句

作用：

显示binlog日志信息，如果没有指定日志名称，则显示第一个二进制日志的信息。

基本语法：

SHOW BINLOG EVENTS
   [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

实例：

mysql> show binlog events;
mysql> show binlog events in 'mysql-bin.000002';
mysql> show binlog events in 'mysql-bin.000002' from 107;
mysql> show binlog events in 'mysql-bin.000002' from 107 limit 3;
mysql> show binlog events in 'mysql-bin.000002' from 107 limit 2,3;

注：‘from 107 limit 3’ 从107开始取出3个事件。
‘from 107 limit 2,3’从107开始，偏移两个事件后取出3个事件。

3.4 SHOW CHARACTER SET 语句

作用：

SHOW CHARACTER SET语句用于显示所有可用的字符集，LIKE子句指示哪些字符集名称可以匹配，where子句列出所选字符集的信息。

基本语法：

SHOW CHARACTER SET
    [LIKE 'pattern' | WHERE expr]

实例：

show character set;
show character set like 'utf%';
show character set where charset = 'utf8';

注：Maxlen列显示用于存储一个字符的最大的字节数目。

3.5 SHOW COLLATION 语句

作用：

SHOW COLLATION SET语句用于显示所有可用的字符集校对，LIKE子句指示哪些字符集校对名称可以匹配，where子句列出所选字符集校对的信息。

基本语法：

SHOW COLLATION
    [LIKE 'pattern' | WHERE expr]

实例：

show collation;
show collation like 'utf8%';
show collation where Collation='utf8_bin';

注：Default列指示对于其字符集，整序值是否是默认值。Compiled指示字符集是否被编辑到服务器中。Sortlen与对字符串（在字符集中表达）分类所需的存储器的数量有关。

3.6 SHOW COLUMNS 语句

作用：

SHOW COLUMNS显示在一个给定表中的各列的信息。对于试图，本语句也起作用,FULL关键词会使得输出中包含您拥有的权限，并包含对每一列各自的评注。可以使用db_name.tbl_name作为tbl_name FROM db_name语法的另一种形式。
SHOW FIELDS是SHOW COLUMNS的同义词

基本语法：

SHOW [FULL] COLUMNS {FROM | IN} tbl_name [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

实例：

show columns in show_table ;
show columns from show_table;
show columns from show_table from test;
show full columns from show_table from test;
show full columns from show_table from test like 'c%';
show full columns from show_table from test where field like 'c%';

3.7 SHOW CONTRIBUTORS 语句

作用：

显示mysql源码的贡献者。

基本语法：

SHOW CONTRIBUTORS

实例：

mysql> SHOW CONTRIBUTORS;
+-------------------+---------------------+-------------------------------------+
| Name              | Location            | Comment                             |
+-------------------+---------------------+-------------------------------------+
| Ronald Bradford   | Brisbane, Australia | EFF contribution for UC2006 Auction |
| Sheeri Kritzer    | Boston, Mass. USA   | EFF contribution for UC2006 Auction |
| Mark Shuttleworth | London, UK.         | EFF contribution for UC2006 Auction |
+-------------------+---------------------+-------------------------------------+

3.8 SHOW CREATE DATABASE 语句

作用：

显示用于创建给定数据库CREATE DATABASE语句。也可以使用SHOW CREATE SCHEMA。

基本语法：

SHOW CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

实例：

show create database test;
show create schema test;
show create database if not exists test;

3.9 SHOW CREATE EVENT 语句

作用：显示创建事件（event）的信息

基本语法：

SHOW CREATE EVENT event_name

实例：

mysql> SET GLOBAL event_scheduler = ON;
mysql> drop event if exists show_event;

mysql> CREATE EVENT test.show_event
ON SCHEDULE
every  1 HOUR
DO
insert into show_table values('', 'name6', 006);

mysql> show create event show_event \G

3.10 SHOW CREATE FUNCTION 语句

作用：显示创建函数的信息

基本语法：

SHOW CREATE FUNCTION func_name

实例：参考SHOW CREATE PROCEDURE

3.11 SHOW CREATE PROCEDURE 语句

作用：显示创建存储过程的信息

基本语法：

SHOW CREATE PROCEDURE proc_name

实例：

mysql> DROP PROCEDURE  IF  EXISTS test.show_procedure;

mysql> delimiter //  
create procedure test.show_procedure()   
begin   
    insert into show_table values('', 'name7', 007);
end  
//  
delimiter ; 

mysql> call test.show_procedure();
mysql> SHOW CREATE PROCEDURE test.show_procedure() \G

3.12 SHOW CREATE TABLE 语句

作用：

显示用于创建给定表的CREATE TABLE语句。本语句对视图（trigger）也起作用。

基本语法：

SHOW CREATE TABLE tbl_name

实例：

mysql> SHOW CREATE TABLE show_table \G
*************************** 1. row ***************************
       Table: show_table
Create Table: CREATE TABLE `show_table` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` varchar(10) DEFAULT NULL,
  `c3` int(10) DEFAULT NULL,
  PRIMARY KEY (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=latin1

3.13 SHOW CREATE TRIGGER 语句

作用：显示触发器的创建信息

基本语法：

SHOW CREATE TRIGGER trigger_name

实例：

mysql> drop trigger if exists show_trigger;

mysql> delimiter //
create trigger show_trigger 
after insert on show_table 
for each row 
begin 
  insert into show_table_sec(c1, c2, c3) values(new.c1, new.c2, new.c3);
end//
delimiter ;

mysql> show create trigger show_trigger \G

注：创建一个触发器show_trigger，当表show_table 有数据插入时，插入数据会同步插入到表show_table_sec。

3.14 SHOW CREATE VIEW 语句

作用：显示视图的创建信息。

基本语法：

SHOW CREATE VIEW view_name

实例：

mysql> drop view if exists show_view;
mysql> CREATE VIEW show_view AS SELECT c2, c3 from show_table where c1 < 5;
mysql> show create view show_view;

3.15 SHOW DATABASES 语句

作用：

SHOW DATABASES可以在MySQL服务器主机上列举数据库。也可以使用mysqlshow命令得到此清单。您只能看到您拥有某些权限的数据库，除非您拥有全局SHOW DATABASES权限。
与SHOW SCHEMAS作用及用法相同。

基本语法：

SHOW {DATABASES | SCHEMAS}
    [LIKE 'pattern' | WHERE expr]

实例：

mysql> show databases;
mysql> show schemas;
mysql> show databases like 'te%';
[root@localhost ~]# /usr/local/mysql/bin/mysqlshow 
+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

3.16 SHOW ENGINE 语句

作用：

SHOW ENGINE显示存储引擎的日志或状态信息.

基本语法：

SHOW ENGINE engine_name {STATUS | MUTEX}

实例：

mysql> show engine innodb status \G
mysql> show engine innodb mutex;
+--------+----------------+-------------+
| Type   | Name           | Status      |
+--------+----------------+-------------+
| InnoDB | log0log.c:771  | os_waits=1  |
| InnoDB | buf0buf.c:1159 | os_waits=1  |
| InnoDB | log0log.c:832  | os_waits=24 |
+--------+----------------+-------------+

3.17 SHOW ENGINES 语句

作用：

SHOW ENGINES显示存储引擎的状态信息。对于检查一个存储引擎是否被支持，或者对于查看默认引擎是什么，本语句十分有用。

基本语法：

SHOW [STORAGE] ENGINES

实例：

mysql> show engines;
mysql> show storage engines;

注：Support值指示某个存储引擎是否被支持，并指示哪个是默认引擎。例如，如果服务器以--default-tabletype=InnoDB选项为起始，则InnoDB行的Support值为DEFAULT值。

3.18 SHOW ERRORS 语句

作用：

本语句与SHOW WARNINGS接近，不过该语句只显示错误，不同时显示错误、警告和注意。
LIMIT子句与SELECT语句具有相同的语法，请参见13.2.7节，“SELECT语法”。
SHOW COUNT(*) ERRORS语句显示错误的数目。您也可以从error_count变量中找回此数目：
SHOW COUNT(*) ERRORS;
SELECT @@error_count;

基本语法：

SHOW ERRORS [LIMIT [offset,] row_count]
SHOW COUNT(*) ERRORS

实例：

mysql> show errors;
mysql> show errors limit 2;
mysql> show errors limit 1,2;
mysql> show count(*) errors;

3.19 SHOW EVENTS 语句

作用：显示已创建的事件信息。

基本语法：

SHOW EVENTS [{FROM | IN} schema_name]
    [LIKE 'pattern' | WHERE expr]

实例：

show events;
show events in test;
show events from test;
show events from test like 'show%';
show events from test where db ='test';
show events from test where definer like 'root@%' \G

3.20 SHOW FUNCTION CODE 语句

作用：需要mysql在编译时开启debug模式才能工作。

基本语法：

SHOW FUNCTION CODE func_name

实例：同下面3.28

3.21 SHOW FUNCTION STATUS 语句

作用：需要mysql在编译时开启debug模式才能工作。

基本语法：同下面3.29

SHOW FUNCTION STATUS
    [LIKE 'pattern' | WHERE expr]

实例：

3.22 SHOW GRANTS 语句

作用：

本语句列出了在为MySQL用户账户复制权限时必须发布的GRANT语句。

基本语法：

SHOW GRANTS [FOR user]

实例：

mysql> show grants;
mysql> show grants for current_user;
mysql> show grants for current_user();
mysql> show grants for root@localhost;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+

3.23 SHOW INDEX 语句

作用：

SHOW INDEX会返回表索引信息。SHOW KEYS,SHOW INDEXES是SHOW INDEX的同义词。也可以使用mysqlshow -k db_name tbl_name命令列举一个表的索引。

基本语法：

SHOW {INDEX | INDEXES | KEYS}
    {FROM | IN} tbl_name
    [{FROM | IN} db_name]
    [WHERE expr]

实例：

show index from show_table;
show indexes from show_table;
show keys from show_table;

show index in show_table;
show index from show_table in test;
show index from test.show_table;
show index from show_table in test where Index_type like 'btree%';

3.24 SHOW MASTER STATUS 语句

作用：显示master主机二进制日志信息。

基本语法：

SHOW MASTER STATUS

实例：

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000012 |    14412 |              |                  |
+------------------+----------+--------------+------------------+

3.25 SHOW OPEN TABLES 语句

作用：显示打开的表

基本语法：

SHOW OPEN TABLES [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

实例：

mysql> show open tables;
mysql> show open tables from test;
mysql> show open tables in test;
mysql> show open tables from test like 'show%';
mysql> show open tables from test where in_use < 4;
mysql> show open tables from test where name_locked < 4;
+----------+----------------+--------+-------------+
| Database | Table          | In_use | Name_locked |
+----------+----------------+--------+-------------+
| test     | show_table_sec |      0 |           0 |
| test     | show_table     |      0 |           0 |
| test     | show_view      |      0 |           0 |
+----------+----------------+--------+-------------+

3.26 SHOW PLUGINS 语句

作用：显示数据库存储引擎信息

基本语法：

SHOW PLUGINS

实例：

mysql> select * from information_schema.plugins;
mysql> show plugins;
+--------------------------+----------+--------------------+---------+-------------+
| Name                     | Status   | Type               | Library | License     |
+--------------------------+----------+--------------------+---------+-------------+
| binlog                   | ACTIVE   | STORAGE ENGINE     | NULL    | PROPRIETARY |
| mysql_native_password    | ACTIVE   | AUTHENTICATION     | NULL    | PROPRIETARY |
| mysql_old_password       | ACTIVE   | AUTHENTICATION     | NULL    | PROPRIETARY |
| MEMORY                   | ACTIVE   | STORAGE ENGINE     | NULL    | PROPRIETARY |
| MyISAM                   | ACTIVE   | STORAGE ENGINE     | NULL    | PROPRIETARY |
| CSV                      | ACTIVE   | STORAGE ENGINE     | NULL    | PROPRIETARY |
| MRG_MYISAM               | ACTIVE   | STORAGE ENGINE     | NULL    | PROPRIETARY |
| InnoDB                   | ACTIVE   | STORAGE ENGINE     | NULL    | PROPRIETARY |
| INNODB_TRX               | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |
| INNODB_LOCKS             | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |
| INNODB_LOCK_WAITS        | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |
| INNODB_CMP               | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |
| INNODB_CMP_RESET         | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |
| INNODB_CMPMEM            | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |
| INNODB_CMPMEM_RESET      | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |
| INNODB_BUFFER_PAGE       | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |
| INNODB_BUFFER_PAGE_LRU   | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |
| INNODB_BUFFER_POOL_STATS | ACTIVE   | INFORMATION SCHEMA | NULL    | PROPRIETARY |
| PERFORMANCE_SCHEMA       | ACTIVE   | STORAGE ENGINE     | NULL    | PROPRIETARY |
| FEDERATED                | DISABLED | STORAGE ENGINE     | NULL    | PROPRIETARY |
| BLACKHOLE                | ACTIVE   | STORAGE ENGINE     | NULL    | PROPRIETARY |
| ARCHIVE                  | ACTIVE   | STORAGE ENGINE     | NULL    | PROPRIETARY |
| partition                | ACTIVE   | STORAGE ENGINE     | NULL    | PROPRIETARY |
+--------------------------+----------+--------------------+---------+-------------+

3.27 SHOW PRIVILEGES 语句

作用：显示当前mysql服务器所支持的权限

基本语法：

SHOW PRIVILEGES

实例：

mysql> show privileges;
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege               | Context                               | Comment                                               |
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Alter                   | Tables                                | To alter the table                                    |
| Alter routine           | Functions,Procedures                  | To alter or drop stored functions/procedures          |
| Create                  | Databases,Tables,Indexes              | To create new databases and tables                    |
| Create routine          | Databases                             | To use CREATE FUNCTION/PROCEDURE                      |
| Create temporary tables | Databases                             | To use CREATE TEMPORARY TABLE                         |
| Create view             | Tables                                | To create new views                                   |
| Create user             | Server Admin                          | To create new users                                   |
| Delete                  | Tables                                | To delete existing rows                               |
| Drop                    | Databases,Tables                      | To drop databases, tables, and views                  |
| Event                   | Server Admin                          | To create, alter, drop and execute events             |
| Execute                 | Functions,Procedures                  | To execute stored routines                            |
| File                    | File access on server                 | To read and write files on the server                 |
| Grant option            | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess   |
| Index                   | Tables                                | To create or drop indexes                             |
| Insert                  | Tables                                | To insert data into tables                            |
| Lock tables             | Databases                             | To use LOCK TABLES (together with SELECT privilege)   |
| Process                 | Server Admin                          | To view the plain text of currently executing queries |
| Proxy                   | Server Admin                          | To make proxy user possible                           |
| References              | Databases,Tables                      | To have references on tables                          |
| Reload                  | Server Admin                          | To reload or refresh tables, logs and privileges      |
| Replication client      | Server Admin                          | To ask where the slave or master servers are          |
| Replication slave       | Server Admin                          | To read binary log events from the master             |
| Select                  | Tables                                | To retrieve rows from table                           |
| Show databases          | Server Admin                          | To see all databases with SHOW DATABASES              |
| Show view               | Tables                                | To see views with SHOW CREATE VIEW                    |
| Shutdown                | Server Admin                          | To shut down the server                               |
| Super                   | Server Admin                          | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc.   |
| Trigger                 | Tables                                | To use triggers                                       |
| Create tablespace       | Server Admin                          | To create/alter/drop tablespaces                      |
| Update                  | Tables                                | To update existing rows                               |
| Usage                   | Server Admin                          | No privileges - allow connect only                    |
+-------------------------+---------------------------------------+-------------------------------------------------------+

3.28 SHOW PROCEDURE CODE 语句

作用：需要mysql在编译时开启debug模式才能工作。

基本语法：

SHOW PROCEDURE CODE proc_name

实例：

3.29 SHOW PROCEDURE STATUS 语句

作用：需要mysql在编译时开启debug模式才能工作。

基本语法：

SHOW PROCEDURE STATUS
    [LIKE 'pattern' | WHERE expr]

实例：

3.30 SHOW PROCESSLIST 语句

作用：SHOW PROCESSLIST显示哪些线程正在运行。您也可以使用mysqladmin processlist语句得到此信息。如果有SUPER权限，可以看到所有线程。

基本语法：

SHOW [FULL] PROCESSLIST

实例：

mysql> show processlist;
mysql> show full processlist;

也可使用：bin/mysqladmin processlist -i 2

3.31 SHOW PROFILE 语句

作用：

show profile是由Jeremy Cole捐献给MySQL社区版本的。默认的是关闭的，但是会话级别可以开启这个功能。开启它可以让MySQL收集在执行语句的时候所使用的资源。为了统计报表,把profiling设为1。

基本语法：

SHOW PROFILE [type [, type] ... ]
    [FOR QUERY n]
    [LIMIT row_count [OFFSET offset]]

type:
    ALL
  | BLOCK IO
  | CONTEXT SWITCHES
  | CPU
  | IPC
  | MEMORY
  | PAGE FAULTS
  | SOURCE
  | SWAPS

ALL：显示所有信息
BLOCK IO：displays counts for block input and output operations
CONTEXT SWITCHES：displays counts for voluntary and involuntary context switches
CPU：displays user and system CPU usage times
IPC：displays counts for messages sent and received
MEMORY：is not currently implemented
PAGE FAULTS：displays counts for major and minor page faults
SOURCE：displays the names of functions from the source code, together with the name and line number of the file in which the function occurs
SWAPS：displays swap counts

实例：

mysql> SELECT @@profiling;
mysql> SET profiling = 1;
mysql> select count(*) from show_table;

mysql> show profile;
mysql> show profile all;
mysql> show profile block io;
mysql> show profile context switches;
mysql> show profile cpu;
mysql> show profile ipc;
mysql> show profile memory;
mysql> show profile page faults;
mysql> show profile source;
mysql> show profile swaps;

mysql> show profile all for query 1;
mysql> show profile all for query 1 limit 2;
mysql> show profile all for query 1 limit 0,4;

3.32 SHOW PROFILES 语句

作用：同上，参考3.13

基本语法：

SHOW PROFILES

实例：同上，参考3.13

3.33 SHOW RELAYLOG EVENTS 语句

作用：

显示复制从机的relaylog信息

基本语法：

SHOW RELAYLOG EVENTS
   [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

实例：

show relaylog events;
show relaylog events in 'mysql-relay-bin.000001';
show relaylog events in 'mysql-relay-bin.000001' from 6;
show relaylog events in 'mysql-relay-bin.000001' from 6 limit 3;
show relaylog events in 'mysql-relay-bin.000001' from 6 limit 2,3;

3.34 SHOW SLAVE HOSTS 语句

作用：显示所有在主机注册的从机。

基本语法：

SHOW SLAVE HOSTS

实例：

mysql> SHOW SLAVE HOSTS;
+------------+-----------+------+-----------+
| Server_id  | Host      | Port | Master_id |
+------------+-----------+------+-----------+
|  192168010 | connect1  | 3306 | 192168011 |
| 1921680101 | connect2  | 3306 | 192168011 |
+------------+-----------+------+-----------+

3.35 SHOW SLAVE STATUS 语句

作用：显示slave从机复制线程的信息。

基本语法：

SHOW SLAVE STATUS

实例：

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: localhost
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 3
              Master_Log_File: gbichot-bin.005
          Read_Master_Log_Pos: 79
               Relay_Log_File: gbichot-relay-bin.005
                Relay_Log_Pos: 548
        Relay_Master_Log_File: gbichot-bin.005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 79
              Relay_Log_Space: 552
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 8
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids: 0
             Master_Server_Id: 1

3.36 SHOW STATUS 语句

作用：

SHOW STATUS提供服务器状态信息。此信息也可以使用mysqladmin extended-status命令获得。使用GLOBAL选项，您可以得到所有MySQL连接的状态值。使用SESSION，可以得到所有当前连接的状态值。如果两个选项都不使用，则默认值为SESSION。

基本语法：

SHOW [GLOBAL | SESSION] STATUS
    [LIKE 'pattern' | WHERE expr]

实例：

mysql> show status;
mysql> show global status;
mysql> show session status;
mysql> show status like 'innodb_buffer%';
mysql> show status where variable_name like 'innodb_buffer%';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| Innodb_buffer_pool_pages_data         | 196   |
| Innodb_buffer_pool_pages_dirty        | 0     |
| Innodb_buffer_pool_pages_flushed      | 223   |
| Innodb_buffer_pool_pages_free         | 7995  |
| Innodb_buffer_pool_pages_misc         | 1     |
| Innodb_buffer_pool_pages_total        | 8192  |
| Innodb_buffer_pool_read_ahead_rnd     | 0     |
| Innodb_buffer_pool_read_ahead         | 0     |
| Innodb_buffer_pool_read_ahead_evicted | 0     |
| Innodb_buffer_pool_read_requests      | 1839  |
| Innodb_buffer_pool_reads              | 145   |
| Innodb_buffer_pool_wait_free          | 0     |
| Innodb_buffer_pool_write_requests     | 785   |
+---------------------------------------+-------+

3.37 SHOW TABLE STATUS 语句

作用：

SHOW TABLE STATUS的性质与SHOW TABLE类似，不过，可以提供每个表的大量信息。也可以使用mysqlshow --status db_name命令得到此清单。本语句也显示视图信息。

基本语法：

SHOW TABLE STATUS [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

实例：

show table status;
show table status from test;
show table status in test;
show table status like 'show_%';
show table status from test like 'show_%';;
show table status from test where engine like 'innodb%';

3.38 SHOW TABLES 语句

作用：

SHOW TABLES列举了给定数据库中的非TEMPORARY表。也可以使用mysqlshow db_name命令得到此清单。本命令也列举数据库中的其它视图。支持FULL修改符，这样SHOW FULL TABLES就可以显示第二个输出列。对于一个表，第二列的值为BASE TABLE；对于一个视图，第二列的值为VIEW。

基本语法：

SHOW [FULL] TABLES [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

实例：

mysql> show tables;
mysql> show full tables;
mysql> show tables from test;
mysql> show tables in test;
mysql> show tables like 'show_%';
mysql> show tables from test like 'show_%';
mysql> show full tables from test where table_type like 'base%';
+----------------+------------+
| Tables_in_test | Table_type |
+----------------+------------+
| show_table     | BASE TABLE |
| show_table_sec | BASE TABLE |
+----------------+------------+

3.39 SHOW TRIGGERS 语句

作用：

SHOW TRIGGERS列出了目前被MySQL服务器定义的触发程序。

基本语法：

SHOW TRIGGERS [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

实例：

mysql> show triggers;
mysql> show triggers from test;
mysql> show triggers in test;
mysql> show triggers like 'show_%';
mysql> show triggers from test like 'show_%';
mysql> show triggers where `trigger` like 'show_%';
mysql> show triggers from test where `table` like 'show_%';

3.40 SHOW VARIABLES 语句

作用：

SHOW VARIABLES显示了部门MySQL系统变量的值。本信息也可以使用mysqladmin variables命令获得。使用GLOBAL选项，您可以获得被用于MySQL新连接的值。使用SESSION，您可以得到对于当前连接起效的值。如果您两个选项都不使用，默认值为SESSION。

基本语法：

SHOW [GLOBAL | SESSION] VARIABLES
    [LIKE 'pattern' | WHERE expr]

实例：

mysql> show variables;
mysql> show global variables;
mysql> show session variables;
mysql> show variables like 'innodb_f%';
mysql> show variables where variable_name like 'innodb_f%';
+--------------------------------+----------+
| Variable_name                  | Value    |
+--------------------------------+----------+
| innodb_fast_shutdown           | 1        |
| innodb_file_format             | Antelope |
| innodb_file_format_check       | ON       |
| innodb_file_format_max         | Antelope |
| innodb_file_per_table          | OFF      |
| innodb_flush_log_at_trx_commit | 1        |
| innodb_flush_method            |          |
| innodb_force_load_corrupted    | OFF      |
| innodb_force_recovery          | 0        |
+--------------------------------+----------+

3.41 SHOW WARNINGS 语句

作用：

SHOW WARNINGS显示由上一个生成消息的语句导致的错误、警告和注意消息。如果上一个使用表的语句未生成消息，则什么也不显示。SHOW ERRORS是其相关语句，只显示错误。

基本语法：

SHOW WARNINGS [LIMIT [offset,] row_count]
SHOW COUNT(*) WARNINGS

实例：

mysql> show warnings;
mysql> show warnings limit 5;
mysql> show warnings limit 2,5;
mysql> show count(*) warnings;
+-------------------------+
| @@session.warning_count |
+-------------------------+
|                       0 |
+-------------------------+

****************************************************************************************

原文地址：http://blog.youkuaiyun.com/jesseyoung/article/details/38264887

博客主页：http://blog.youkuaiyun.com/jesseyoung
****************************************************************************************