讲讲MySQL数据库中的show命令

数据库知多少 2021-04-26

1294

讲讲MySQL数据库中的show命令

平常我们经常使用show命令来查看数据库的各种信息：数据库、字符集、表、索引、状态、日志、警告信息等，今天我们来做个总结，看看show到底有哪些功能。我们来通过示例来看一看吧。

数据库信息

Current user: root@localhost

SSL: Not in use

Server version: 8.0.18 MySQL Community Server - GPL

Protocol version: 10

Connection: Localhost via UNIX socket

show命令语法

先来看看show命令语法：

SHOW 语法：
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

show命令常用示例

显示MySQL实例中所有数据库的名称：

show databases;

root@MYSQL:[DB((none))]>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| dkf                |
| mysql              |
| performance_schema |
| sentineldb         |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

也可以根据条件进行匹配：

show databases like '%dkf%';

root@MYSQL:[DB((none))]>show databases like '%dkf%';
+------------------+
| Database (%dkf%) |
+------------------+
| dkf              |
+------------------+
1 row in set (0.01 sec)

显示当前数据库中所有表的名称：

show tables;

root@MYSQL:[DB((none))]>use dkf;
Database changed
root@MYSQL:[DB(dkf)]>show tables;
+---------------+
| Tables_in_dkf |
+---------------+
| dkf_test      |
| dkftab        |
| my_test       |
+---------------+
3 rows in set (0.00 sec)

也可以不切换数据库，显示特定数据库中的表：

show tables from database_name;

root@MYSQL:[DB(mysql)]>show tables from dkf;
+---------------+
| Tables_in_dkf |
+---------------+
| dkf_test      |
| dkftab        |
| my_test       |
+---------------+
3 rows in set (0.00 sec)

显示表中列名称：

show column from table_name;

root@MYSQL:[DB(dkf)]>show columns from dkftab;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

也可以用另外一种方式：

show columns from database_name.table_name;

root@MYSQL:[DB(mysql)]>show columns from dkf.dkftab;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

或者这样：

show columns from table_name from database_name;

root@MYSQL:[DB(mysql)]>show columns from dkftab from dkf;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

显示表的索引：

show index from table_name;

root@MYSQL:[DB(mysql)]>show index from dkf.dkftab;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| dkftab |          1 | idx_name |            1 | name        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

不过，我们一般看表结构或索引信息，喜欢用这种方式，更简单直接：

show create table table_name;

root@MYSQL:[DB(mysql)]>show create table dkf.dkftab\G
*************************** 1. row ***************************
       Table: dkftab
Create Table: CREATE TABLE `dkftab` (
  `name` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

显示创建数据库的定义语句：

show create database database_name;

root@MYSQL:[DB(mysql)]>show create database dkf;
+----------+--------------------------------------------------------------------------------------------+
| Database | Create Database                                                                            |
+----------+--------------------------------------------------------------------------------------------+
| dkf      | CREATE DATABASE `dkf` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ |
+----------+--------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

显示系统变量的名称和值：

show variables;

root@MYSQL:[DB(mysql)]>show variables\G
*************************** 1. row ***************************
Variable_name: auto_increment_increment
        Value: 1
*************************** 2. row ***************************
Variable_name: auto_increment_offset
        Value: 1
*************************** 3. row ***************************
Variable_name: autocommit
        Value: ON
......

一般我们都是和like关键字一起用：

root@MYSQL:[DB(mysql)]>show variables like '%hostname%';
+---------------+---------+
| Variable_name | Value   |
+---------------+---------+
| hostname      | Tdongkf |
+---------------+---------+
1 row in set (0.01 sec)

显示系统的统计信息，例如，正在运行的线程数量：

show status;

root@MYSQL:[DB(mysql)]>show status;
+-----------------------------------------------+--------------------------------------------------+
| Variable_name                                 | Value                                            |
+-----------------------------------------------+--------------------------------------------------+
| Aborted_clients                               | 11                                               |
| Aborted_connects                              | 0                                                |
| Binlog_cache_disk_use                         | 0                                                |
| Binlog_cache_use                              | 0                                                |
| Binlog_stmt_cache_disk_use                    | 0                                                |
| Binlog_stmt_cache_use                         | 3                                                |
| Bytes_received                                | 1016                                             |
......

这个我们一般也是和like一起使用的：

root@MYSQL:[DB(mysql)]>show status like '%uptime%';
+---------------------------+--------+
| Variable_name             | Value  |
+---------------------------+--------+
| Uptime                    | 199501 |
| Uptime_since_flush_status | 199501 |
+---------------------------+--------+
2 rows in set (0.00 sec)

显示系统中正在运行的所有进程，也就是当前正在执行的查询：

大多数用户可以查看自己的进程，但是如果拥有process权限，就可以查看所有的进程。

show processlist;

root@MYSQL:[DB(mysql)]>show processlist; 
+----+------+-----------+-------+---------+------+----------+------------------+
| Id | User | Host      | db    | Command | Time | State    | Info             |
+----+------+-----------+-------+---------+------+----------+------------------+
| 15 | root | localhost | mysql | Query   |    0 | starting | show processlist |
+----+------+-----------+-------+---------+------+----------+------------------+
1 row in set (0.00 sec)

显示一个用户的权限：

显示结果类似于grant 命令。

show grants for user_name;

root@MYSQL:[DB(mysql)]>show grants for dbmgr@'%';
+--------------------------------------------+
| Grants for dbmgr@%                         |
+--------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'dbmgr'@'%' |
+--------------------------------------------+
1 row in set (0.00 sec)

显示服务器所支持的权限列表：

show privileges;

root@MYSQL:[DB(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                    |
+-------------------------+---------------------------------------+-------------------------------------------------------+
31 rows in set (0.00 sec)

显示innoDB存储引擎的状态：

show engine innodb status;

root@MYSQL:[DB(mysql)]>show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2021-03-31 17:09:21 0x7fcb33709700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 45 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 24 srv_active, 0 srv_shutdown, 199675 srv_idle
srv_master_thread log flush and writes: 199689
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 9
OS WAIT ARRAY INFO: signal count 9
RW-shared spins 0, rounds 18, OS waits 9
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 18.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
......

显示可用的存储引擎和默认引擎：

show engines;

root@MYSQL:[DB(mysql)]>show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

显示已经安装的插件：

show plugins;

root@MYSQL:[DB(mysql)]>show plugins;
+----------------------------+----------+--------------------+----------------------+---------+
| Name                       | Status   | Type               | Library              | License |
+----------------------------+----------+--------------------+----------------------+---------+
| binlog                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| mysql_native_password      | ACTIVE   | AUTHENTICATION     | NULL                 | GPL     |
| sha256_password            | ACTIVE   | AUTHENTICATION     | NULL                 | GPL     |
| InnoDB                     | ACTIVE   | STORAGE ENGINE     | NULL                 | GPL     |
| INNODB_TRX                 | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_LOCKS               | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_LOCK_WAITS          | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP                 | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
| INNODB_CMP_RESET           | ACTIVE   | INFORMATION SCHEMA | NULL                 | GPL     |
......

显示二进制的日志：

show binary logs;

root@MYSQL:[DB(mysql)]>show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.001301 |  84362846 |
| mysql-bin.001302 | 536963453 |
| mysql-bin.001303 |  41697301 |
| mysql-bin.000027 |       177 |
| mysql-bin.000028 |      2528 |
+------------------+-----------+
5 rows in set (0.00 sec)

显示最后一个执行的语句所产生的错误、警告和通知：

show warnings;

root@MYSQL:[DB(mysql)]>grant select on dkf.* to test@'%' identified by 'test1234';
Query OK, 0 rows affected, 1 warning (0.00 sec)


root@MYSQL:[DB(mysql)]>show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                            |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

显示最后一个执行语句所产生的错误：

show errors;

root@MYSQL:[DB(mysql)]>select aaa;
ERROR 1054 (42S22): Unknown column 'aaa' in 'field list'
root@MYSQL:[DB(mysql)]>show warnings;
+-------+------+--------------------------------------+
| Level | Code | Message                              |
+-------+------+--------------------------------------+
| Error | 1054 | Unknown column 'aaa' in 'field list' |
+-------+------+--------------------------------------+
1 row in set (0.00 sec)

其它的用法请自行测试。

总结

通过这些简单的示例，我们可以对show命令有了一个总体的印象，以后在使用过程中，我们可以通过show命令的额外参数，比如like，where，limit，row_count等，可以更方便的完成我们想要查询的信息。

完

=end=

mysql

文章转载自数据库知多少，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

讲讲MySQL数据库中的show命令

完

评论