mysql－－SHOW命令大全

SHOW AUTHORS

顾名思义，这个要展示的是各位MYSQL开发者的信息，包括姓名，住址及相关注解

e.g.1

mysql> show authors\G;

*************************** 1. row ***************************

    Name: Brian (Krow) Aker

Location: Seattle, WA, USA

 Comment: Architecture, archive, federated, bunch of little stuff :)

*************************** 2. row ***************************

    Name: Venu Anuganti

Location:

 Comment: Client/server protocol (4.1)

 

 

SHOW BINARY LOGS

SHOW MASTER LOGS

显示server上的二进制日志文件，其中BINARY==MASTER.

e.g.1

mysql> show binary logs;

+-------------------+-----------+

| Log_name          | File_size |

+-------------------+-----------+

| mysqld-bin.000001 |       125 |

| mysqld-bin.000002 |       125 |

| mysqld-bin.000003 |  50481050 |

| mysqld-bin.000004 |       125 |

| mysqld-bin.000005 |       125 |

| mysqld-bin.000006 |       106 |

+-------------------+-----------+

6 rows in set (0.02 sec)

 

e.g.2

mysql> show master logs;

+-------------------+-----------+

| Log_name          | File_size |

+-------------------+-----------+

| mysqld-bin.000001 |       125 |

| mysqld-bin.000002 |       125 |

| mysqld-bin.000003 |  50481050 |

| mysqld-bin.000004 |       125 |

| mysqld-bin.000005 |       125 |

| mysqld-bin.000006 |       106 |

+-------------------+-----------+

6 rows in set (0.00 sec)

 

SHOW BINLOG EVENTS

   [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

显示二进制文件的相关信息。如果未指定日志文件名，则默认展示第一个二进制文件相关信息；其中LIMIT语法同SELECT.

e.g.1

mysql> show binlog events in 'mysqld-bin.000002'\G;

*************************** 1. row ***************************

   Log_name: mysqld-bin.000002

        Pos: 4

 Event_type: Format_desc

  Server_id: 1

End_log_pos: 106

       Info: Server ver: 5.1.52-log, Binlog ver: 4

*************************** 2. row ***************************

   Log_name: mysqld-bin.000002

        Pos: 106

 Event_type: Stop

  Server_id: 1

End_log_pos: 125

       Info:

2 rows in set (0.00 sec)

  

 

SHOW CHARACTER SET

[LIKE 'pattern' | WHERE expr]

show character set显示MYSQL中的所有字符集信息。可用LIKE或WHERE子句进行过滤。如 show character set like ‘u%’,列出所有以u开头的字符集.

e.g.1

mysql> show character set like 'u%';

+---------+-----------------+-------------------+--------+

| Charset | Description     | Default collation | Maxlen |

+---------+-----------------+-------------------+--------+

| ujis    | EUC-JP Japanese | ujis_japanese_ci  |      3 |

| utf8    | UTF-8 Unicode   | utf8_general_ci   |      3 |

| ucs2    | UCS-2 Unicode   | ucs2_general_ci   |      2 |

+---------+-----------------+-------------------+--------+

3 rows in set (0.07 sec)

  

 

SHOW COLLATION

[LIKE 'pattern' | WHERE expr]

 

show collation 显示所有的排序规则。可用LIKE或WHERE子句进行过滤。如 show collation like ‘u%’,列出所有以u开头的排序规则.

e.g.1

mysql> show collation like 'latin1%';

+-------------------+---------+----+---------+----------+---------+

| Collation         | Charset | Id | Default | Compiled | Sortlen |

+-------------------+---------+----+---------+----------+---------+

| latin1_german1_ci | latin1  |  5 |         | Yes      |       1 |

| latin1_swedish_ci | latin1  |  8 | Yes     | Yes      |       1 |

| latin1_danish_ci  | latin1  | 15 |         | Yes      |       1 |

| latin1_german2_ci | latin1  | 31 |         | Yes      |       2 |

| latin1_bin        | latin1  | 47 |         | Yes      |       1 |

| latin1_general_ci | latin1  | 48 |         | Yes      |       1 |

| latin1_general_cs | latin1  | 49 |         | Yes      |       1 |

| latin1_spanish_ci | latin1  | 94 |         | Yes      |       1 |

+-------------------+---------+----+---------+----------+---------+

8 rows in set (0.00 sec)

 

 

SHOW [FULL] COLUMNS {FROM | IN} tbl_name [{FROM | IN} db_name]

[LIKE 'pattern' | WHERE expr]

 

显示某个数据库的某张表的列的信息，可通过like或where子句过虑。from tbl_naem in db_name可用db_name.tbl_name替代。

e.g.1

mysql> show columns from admin_user in rec_bak like 'u%';

+------------+-------------+------+-----+---------+----------------+

| Field      | Type        | Null | Key | Default | Extra          |

+------------+-------------+------+-----+---------+----------------+

| user_id    | smallint(5) | NO   | PRI | NULL    | auto_increment |

| user_code  | varchar(20) | NO   |     |         |                |

| user_name  | varchar(50) | NO   |     |         |                |

| user_name2 | varchar(30) | YES  |     | NULL    |                |

+------------+-------------+------+-----+---------+----------------+

4 rows in set (0.00 sec)

 

 

SHOW CONTRIBUTORS

很显然

e.g.1

mysql> show contributors\G;

*************************** 1. row ***************************

    Name: Ronald Bradford

Location: Brisbane, Australia

 Comment: EFF contribution for UC2006 Auction

*************************** 2. row ***************************

    Name: Sheeri Kritzer

Location: Boston, Mass. USA

 Comment: EFF contribution for UC2006 Auction

*************************** 3. row ***************************

    Name: Mark Shuttleworth

Location: London, UK.

 Comment: EFF contribution for UC2006 Auction

3 rows in set (0.00 sec)

 

 

SHOW CREATE {DATABASE | SCHEMA} db_name

语句；其中database==schema.

e.g.1

mysql> show create database mysql\G;

*************************** 1. row ***************************

       Database: mysql

Create Database: CREATE DATABASE `mysql` /*!40100 DEFAULT CHARACTER SET latin1 */

1 row in set (0.00 sec)

 

 

SHOW CREATE EVENT event_name

  显示指定event的create event语句.

 

SHOW CREATE FUNCTION func_name

  显示指定func_name的create function语句.

 

SHOW CREATE PROCEDURE proc_name

  显示指定proc_name的create procedure语句.

 

SHOW CREATE TABLE tbl_name

  显示指定tal_name的create table语句.

 

SHOW CREATE TRIGGER trigger_name

  显示指定trigger_name的create trigger语句.

 

SHOW CREATE VIEW view_name

显示指定view_name的create view语句.

e.g.1

mysql> show create view v_test\G;

*************************** 1. row ***************************

                View: v_test

         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_test` AS select `b`.`barcode` AS `barcode`,`g`.`goods_sn` AS `goods_sn`,`g`.`goods_name` AS `goods_name`,`c`.`color_code` AS `color_code`,`c`.`color_name` AS `color_name`,`s`.`size_code` AS `size_code`,`s`.`size_name` AS `size_name` from (((`goods_barcode` `b` join `goods` `g` on((`b`.`goods_id` = `g`.`goods_id`))) join `color` `c` on((`b`.`color_id` = `c`.`color_id`))) join `size` `s` on((`b`.`size_id` = `s`.`size_id`)))

character_set_client: utf8

collation_connection: utf8_general_ci

1 row in set (0.00 sec)

 

 

SHOW {DATABASES | SCHEMAS}

[LIKE 'pattern' | WHERE expr]

  show databases显示server上所有的数据库信息，可用like或where子句过虑；其中databases==schmas.

e.g.1

mysql> show databases like 'r%';

+---------------+

| Database (r%) |

+---------------+

| rec_bak       |

+---------------+

1 row in set (0.00 sec)

 

 

SHOW ENGINE engine_name {STATUS | MUTEX}

显示相关存储引擎的一些操作信息.

e.g.1

mysql> show engine innodb status\G;

*************************** 1. row ***************************

  Type: InnoDB

  Name:

Status:

=====================================

110817 20:15:59 INNODB MONITOR OUTPUT

=====================================

Per second averages calculated from the last 18 seconds

----------

SEMAPHORES

----------

OS WAIT ARRAY INFO: reservation count 120, signal count 120

Mutex spin waits 0, rounds 1220, OS waits 61

RW-shared spins 6, OS waits 3; RW-excl spins 56, OS waits 56

------------

TRANSACTIONS

------------

Trx id counter 0 3072

Purge done for trx's n:o < 0 0 undo n:o < 0 0

History list length 0

LIST OF TRANSACTIONS FOR EACH SESSION:

……………………………………………………………………………………..

 

 

SHOW [STORAGE] ENGINES

显示server上存储引擎的状态信息

 

e.g.1

mysql> show storage engines\G;

*************************** 1. row ***************************

      Engine: MRG_MYISAM

     Support: YES

     Comment: Collection of identical MyISAM tables

Transactions: NO

          XA: NO

  Savepoints: NO

*************************** 2. row ***************************

      Engine: CSV

     Support: YES

     Comment: CSV storage engine

Transactions: NO

          XA: NO

  Savepoints: NO

*************************** 3. row ***************************

      Engine: MyISAM

     Support: DEFAULT

     Comment: Default engine as of MySQL 3.23 with great performance

Transactions: NO

          XA: NO

  Savepoints: NO

*************************** 4. row ***************************

      Engine: InnoDB

     Support: YES

     Comment: Supports transactions, row-level locking, and foreign keys

Transactions: YES

          XA: YES

  Savepoints: YES

*************************** 5. row ***************************

      Engine: MEMORY

     Support: YES

     Comment: Hash based, stored in memory, useful for temporary tables

Transactions: NO

          XA: NO

  Savepoints: NO

5 rows in set (0.00 sec)

 

SHOW ERRORS [LIMIT [offset,] row_count]

SHOW COUNT(*) ERRORS

显示错误信息

  统计错误信息数量

 

e.g.1

mysql> show errors;

Empty set (0.00 sec)

 

mysql> show count(*) errors;

+-----------------------+

| @@session.error_count |

+-----------------------+

|                     0 |

+-----------------------+

1 row in set (0.02 sec)

 

 

SHOW EVENTS [{FROM | IN} schema_name]

[LIKE 'pattern' | WHERE expr]

列出schema_name模式下，所有满足like或where条件的事件；

  Show events；列出当前模式下的所有事件.

e.g.1

mysql> show events from rec_bak;

Empty set (0.03 sec)

 

 

SHOW FUNCTION CODE func_name

  显示存储函数func_name的code

 

SHOW FUNCTION STATUS

[LIKE 'pattern' | WHERE expr]

显示（满足like或where条件的）存储函数的状态信息

 

e.g.1

mysql> show function status like 'p_%';

Empty set (0.00 sec)

 

SHOW GRANTS [FOR user]

 

e.g.1

  mysql> SHOW GRANTS FOR 'root'@'localhost';

+---------------------------------------------------------------------+

| Grants for root@localhost                                           |

+---------------------------------------------------------------------+

| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |

+---------------------------------------------------------------------+

 

 

SHOW {INDEX | INDEXES | KEYS}

    {FROM | IN} tbl_name

    [{FROM | IN} db_name]

[WHERE expr]

 

e.g.1

  mysql> show index from huojia in rec_bak\G;

*************************** 1. row ***************************

       Table: huojia

  Non_unique: 0

    Key_name: PRIMARY

Seq_in_index: 1

 Column_name: hj_id

   Collation: A

 Cardinality: 1205

    Sub_part: NULL

      Packed: NULL

        Null:

  Index_type: BTREE

     Comment:

1 row in set (0.00 sec)

 

e.g.2

mysql> show keys from rec_bak.huojia\G;

*************************** 1. row ***************************

       Table: huojia

  Non_unique: 0

    Key_name: PRIMARY

Seq_in_index: 1

 Column_name: hj_id

   Collation: A

 Cardinality: 1205

    Sub_part: NULL

      Packed: NULL

        Null:

  Index_type: BTREE

     Comment:

1 row in set (0.00 sec)

 

e.g.3

mysql> show keys from goods in rec_bak\G;

*************************** 1. row ***************************

       Table: goods

  Non_unique: 0

    Key_name: PRIMARY

Seq_in_index: 1

 Column_name: goods_id

   Collation: A

 Cardinality: 18085

    Sub_part: NULL

      Packed: NULL

        Null:

  Index_type: BTREE

     Comment:

*************************** 2. row ***************************

       Table: goods

  Non_unique: 1

    Key_name: goods_sn

Seq_in_index: 1

 Column_name: goods_sn

   Collation: A

 Cardinality: 18085

    Sub_part: NULL

      Packed: NULL

        Null:

  Index_type: BTREE

     Comment:

……………………………………………………………………………………………………………………………………………………………………

*************************** 11. row ***************************

       Table: goods

  Non_unique: 1

    Key_name: ghs_id

Seq_in_index: 1

 Column_name: ghs_id

   Collation: A

 Cardinality: 2

    Sub_part: NULL

      Packed: NULL

        Null: YES

  Index_type: BTREE

     Comment:

11 rows in set (0.36 sec)

 

 

SHOW INNODB STATUS

==show engine innodb status

 

SHOW MASTER STATUS

显示主机（相对于slave）的bin-log

 

mysql> show master status;

+-------------------+----------+--------------+------------------+

| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+-------------------+----------+--------------+------------------+

| mysqld-bin.000006 |      106 |              |                  |

+-------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

 

 

SHOW OPEN TABLES [{FROM | IN} db_name]

[LIKE 'pattern' | WHERE expr]

显示在表缓存中打开的，非临时表的信息

 

mysql> show open tables in mysql;

+----------+--------------+--------+-------------+

| Database | Table        | In_use | Name_locked |

+----------+--------------+--------+-------------+

| mysql    | servers      |      0 |           0 |

| mysql    | db           |      0 |           0 |

| mysql    | host         |      0 |           0 |

| mysql    | columns_priv |      0 |           0 |

| mysql    | user         |      0 |           0 |

| mysql    | event        |      0 |           0 |

| mysql    | tables_priv  |      0 |           0 |

| mysql    | procs_priv   |      0 |           0 |

+----------+--------------+--------+-------------+

 

 

SHOW PLUGINS

显示server插件信息

 

mysql> show plugins;

+------------+--------+----------------+---------+---------+

| Name       | Status | Type           | Library | License |

+------------+--------+----------------+---------+---------+

| binlog     | ACTIVE | STORAGE ENGINE | NULL    | GPL     |

| partition  | ACTIVE | STORAGE ENGINE | NULL    | GPL     |

| CSV        | ACTIVE | STORAGE ENGINE | NULL    | GPL     |

| MEMORY     | ACTIVE | STORAGE ENGINE | NULL    | GPL     |

| InnoDB     | ACTIVE | STORAGE ENGINE | NULL    | GPL     |

| MyISAM     | ACTIVE | STORAGE ENGINE | NULL    | GPL     |

| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL    | GPL     |

+------------+--------+----------------+---------+---------+

7 rows in set (0.01 sec)

 

 

SHOW PRIVILEGES

显示MYSQL SERVER支持的所有系统权限

 

mysql> show privileges\G;

*************************** 1. row ***************************

Privilege: Alter

  Context: Tables

  Comment: To alter the table

*************************** 2. row ***************************

Privilege: Alter routine

  Context: Functions,Procedures

  Comment: To alter or drop stored functions/procedures

*************************** 3. row ***************************

Privilege: Create

  Context: Databases,Tables,Indexes

  Comment: To create new databases and tables

 

SHOW PROCEDURE CODE proc_name

该语句仅能在支持debugging扩展的server上执行。它展示的是一个内部实现的命名存储过程。

 

mysql> DELIMITER //

mysql> CREATE PROCEDURE p1 ()

    -> BEGIN

    ->   DECLARE fanta INT DEFAULT 55;

    ->   DROP TABLE t2;

    ->   LOOP

    ->     INSERT INTO t3 VALUES (fanta);

    ->     END LOOP;

    ->   END//

Query OK, 0 rows affected (0.00 sec)

 

mysql> SHOW PROCEDURE CODE p1//

+-----+----------------------------------------+

| Pos | Instruction                            |

+-----+----------------------------------------+

|   0 | set fanta@0 55                         |

|   1 | stmt 9 "DROP TABLE t2"                 |

|   2 | stmt 5 "INSERT INTO t3 VALUES (fanta)" |

|   3 | jump 2                                 |

+-----+----------------------------------------+

4 rows in set (0.00 sec)

 

 

 

SHOW PROCEDURE STATUS

[LIKE 'pattern' | WHERE expr]

这个也是mysql的一个扩展功能，它展示的是一个存储过程的特有属性，如名字、类型、创建者、修改日期等等

 

mysql> SHOW PROCEDURE STATUS LIKE 'sp1'\G

*************************** 1. row ***************************

                  Db: test

                Name: sp1

                Type: PROCEDURE

             Definer: testuser@localhost

            Modified: 2004-08-03 15:29:37

             Created: 2004-08-03 15:29:37

       Security_type: DEFINER

             Comment:

character_set_client: latin1

collation_connection: latin1_swedish_ci

  Database Collation: latin1_swedish_ci

 

 

 

SHOW [FULL] PROCESSLIST

展示sever上正在跑着的线程

 

mysql> show processlist\G;

*************************** 1. row ***************************

     Id: 3

   User: root

   Host: localhost

     db: rec_bak

Command: Query

   Time: 0

  State: NULL

   Info: show processlist

1 row in set (0.00 sec)

 

 

SHOW PROFILES

 

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

 

显示会话中语句执行时的资源分配概要

 

mysql> SELECT @@profiling;

+-------------+

| @@profiling |

+-------------+

|           0 |

+-------------+

1 row in set (0.00 sec)

 

mysql> SET profiling = 1;

Query OK, 0 rows affected (0.00 sec)

 

mysql> DROP TABLE IF EXISTS t1;

Query OK, 0 rows affected, 1 warning (0.00 sec)

 

mysql> CREATE TABLE T1 (id INT);

Query OK, 0 rows affected (0.01 sec)

 

mysql> SHOW PROFILES;

+----------+----------+--------------------------+

| Query_ID | Duration | Query                    |

+----------+----------+--------------------------+

|        0 | 0.000088 | SET PROFILING = 1        |

|        1 | 0.000136 | DROP TABLE IF EXISTS t1  |

|        2 | 0.011947 | CREATE TABLE t1 (id INT) |

+----------+----------+--------------------------+

3 rows in set (0.00 sec)

 

mysql> SHOW PROFILE;

+----------------------+----------+

| Status               | Duration |

+----------------------+----------+

| checking permissions | 0.000040 |

| creating table       | 0.000056 |

| After create         | 0.011363 |

| query end            | 0.000375 |

| freeing items        | 0.000089 |

| logging slow query   | 0.000019 |

| cleaning up          | 0.000005 |

+----------------------+----------+

7 rows in set (0.00 sec)

 

mysql> SHOW PROFILE FOR QUERY 1;

+--------------------+----------+

| Status             | Duration |

+--------------------+----------+

| query end          | 0.000107 |

| freeing items      | 0.000008 |

| logging slow query | 0.000015 |

| cleaning up        | 0.000006 |

+--------------------+----------+

4 rows in set (0.00 sec)

 

mysql> SHOW PROFILE CPU FOR QUERY 2;

+----------------------+----------+----------+------------+

| Status               | Duration | CPU_user | CPU_system |

+----------------------+----------+----------+------------+

| checking permissions | 0.000040 | 0.000038 |   0.000002 |

| creating table       | 0.000056 | 0.000028 |   0.000028 |

| After create         | 0.011363 | 0.000217 |   0.001571 |

| query end            | 0.000375 | 0.000013 |   0.000028 |

| freeing items        | 0.000089 | 0.000010 |   0.000014 |

| logging slow query   | 0.000019 | 0.000009 |   0.000010 |

| cleaning up          | 0.000005 | 0.000003 |   0.000002 |

+----------------------+----------+----------+------------+

7 rows in set (0.00 sec)

 

 

 

SHOW SCHEDULER STATUS

相关事件调度的调试信息。适用于5.1.11调试版，在5.1.12及以后的版本不再支持该语句。其输入可能如下：

+--------------------------------+---------------------+

| Name                           | Value               |

+--------------------------------+---------------------+

| scheduler state                | INITIALIZED         |

| thread_id                      | NULL                |

| scheduler last locked at       | init_scheduler::313 |

| scheduler last unlocked at     | init_scheduler::318 |

| scheduler waiting on condition | 0                   |

| scheduler workers count        | 0                   |

| scheduler executed events      | 0                   |

| scheduler data locked          | 0                   |

| queue element count            | 1                   |

| queue data locked              | 0                   |

| queue data attempting lock     | 0                   |

| queue last locked at           | create_event::218   |

| queue last unlocked at         | create_event::222   |

| queue last attempted lock at   | ::0                 |

| queue waiting on condition     | 0                   |

| next activation at             |    0-00-00 00:00:00 |

+--------------------------------+---------------------+

 

SHOW SLAVE HOSTS

展示在master上登记过的slave机器的相关信息

mysql> SHOW SLAVE HOSTS;

+------------+-----------+------+-----------+

| Server_id  | Host      | Port | Master_id |

+------------+-----------+------+-----------+

|  192168010 | iconnect2 | 3306 | 192168011 |

| 1921680101 | athena    | 3306 | 192168011 |

+------------+-----------+------+-----------+

 

 

SHOW SLAVE STATUS

显示slave线程基本参数的状态信息

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:

 

 

SHOW [GLOBAL | SESSION] STATUS

[LIKE 'pattern' | WHERE expr]

显示server的相关状态信息

 

mysql> show status like 'Up%';

+---------------------------+-------+

| Variable_name             | Value |

+---------------------------+-------+

| Uptime                    | 13301 |

| Uptime_since_flush_status | 13301 |

+---------------------------+-------+

2 rows in set (0.00 sec)

 

 

SHOW TABLE STATUS [{FROM | IN} db_name]

[LIKE 'pattern' | WHERE expr]

有点类似于show tables，但该语提供了更多的非临时表的相关信息。

 

e.g.q

mysql> show table status from rec_bak like 'admin_user'\G;

*************************** 1. row ***************************

           Name: admin_user

         Engine: InnoDB

        Version: 10

     Row_format: Compact

           Rows: 44

 Avg_row_length: 372

    Data_length: 16384

Max_data_length: 0

   Index_length: 0

      Data_free: 9437184

 Auto_increment: 48

    Create_time: 2011-08-15 16:11:41

    Update_time: NULL

     Check_time: NULL

      Collation: utf8_general_ci

       Checksum: NULL

 Create_options: avg_row_length=1170

        Comment:

1 row in set (0.00 sec)

 

e.g.2

mysql> show tables from rec_bak like 'admin_user'\G;

*************************** 1. row ***************************

Tables_in_rec_bak (admin_user): admin_user

1 row in set (0.00 sec)

 

 

SHOW TRIGGERS [{FROM | IN} db_name]

    [LIKE 'pattern' | WHERE expr]

列出某数据库内定义的triggers.

mysql> SHOW TRIGGERS LIKE 'acc%'\G

*************************** 1. row ***************************

             Trigger: ins_sum

               Event: INSERT

               Table: account

           Statement: SET @sum = @sum + NEW.amount

              Timing: BEFORE

             Created: NULL

            sql_mode:

             Definer: myname@localhost

character_set_client: latin1

collation_connection: latin1_swedish_ci

  Database Collation: latin1_swedish_ci

 

 

SHOW [GLOBAL | SESSION] VARIABLES

[LIKE 'pattern' | WHERE expr]

列出mysql的系统变量

 

mysql> show variables like 'log_bin%';

+---------------------------------+-------+

| Variable_name                   | Value |

+---------------------------------+-------+

| log_bin                         | ON    |

| log_bin_trust_function_creators | OFF   |

| log_bin_trust_routine_creators  | OFF   |

+---------------------------------+-------+

3 rows in set (0.00 sec)

 

 

SHOW WARNINGS [LIMIT [offset,] row_count]

SHOW COUNT(*) WARNINGS

类似show errors；不过该语句显示的是当前会话中最后一个语句执行所产的的错误、警告等相关信息。

 

e.g.1

mysql> show war;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'war' at line 1

mysql> show warnings\G;

*************************** 1. row ***************************

  Level: Error

   Code: 1064

Message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'war' at line 1

1 row in set (0.00 sec)

 

e.g.2

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql              |

| rec_bak            |

| test               |

+--------------------+

4 rows in set (0.03 sec)

 

mysql> show warnings\G;

Empty set (0.00 sec)