MySQL Show命令集

MySQL SHOW 命令

1、mysql shell 查看帮助show

(root@localhost) [(none)]> \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 {REPLICAS | SLAVE HOSTS}
SHOW {REPLICA | 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
https://dev.mysql.com/doc/refman/8.0/en/extended-show.html.In SHOW statement results, user names and host names are quoted using
backticks (`).URL: https://dev.mysql.com/doc/refman/8.0/en/show.html

2、查看存储引擎的状态

(root@localhost) [(none)]> SHOW ENGINE INNODB STATUS \G;
*************************** 1. row ***************************Type: InnoDBName:Status:
=====================================2024-06-08 11:54:25 139900802344704 INNODB MONITOR OUTPUT
=====================================Per second averages calculated from the last 14 seconds
-----------------BACKGROUND THREAD
-----------------srv_master_thread loops: 6 srv_active, 0 srv_shutdown, 435284 srv_idlesrv_master_thread log flush and writes: 0
----------SEMAPHORES
----------OS WAIT ARRAY INFO: reservation count 89
OS WAIT ARRAY INFO: signal count 87
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------TRANSACTIONS
------------Trx id counter 48991
Purge done for trx's n:o < 48986 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421376845869056, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421376845869864, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421376845868248, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421376845867440, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421376845866632, not started0 lock struct(s), heap size 1128, 0 row lock(s)
--------FILE I/O
--------I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
1168 OS file reads, 1380 OS file writes, 701 OS fsyncs0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:insert 0, delete mark 0, delete 0
discarded operations:insert 0, delete mark 0, delete 0
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 1 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 2 buffer(s)
Hash table size 34679, node heap has 3 buffer(s)
Hash table size 34679, node heap has 8 buffer(s)0.00 hash searches/s, 0.00 non-hash searches/s
---LOG
---Log sequence number          1352792225
Log buffer assigned up to    1352792225
Log buffer completed up to   1352792225
Log written up to            1352792225
Log flushed up to            1352792225
Added dirty pages up to      1352792225
Pages flushed up to          1352792225
Last checkpoint at           1352792225327 log i/o's done, 0.00 log i/o's/second
----------------------BUFFER POOL AND MEMORY
----------------------Total large memory allocated 0
Dictionary memory allocated 550711
Buffer pool size   8192
Free buffers       6892
Database pages     1284
Old database pages 493
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1139, created 145, written 840
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1284, unzip_LRU len: 0I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------ROW OPERATIONS
--------------0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=10299, Main thread ID=139901376005888 , state=sleeping
Number of rows inserted 0, updated 0, deleted 0, read 274
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
Number of system rows inserted 2, updated 479, deleted 0, read 129060.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------END OF INNODB MONITOR OUTPUT
============================1 row in set (0.01 sec)ERROR:
No query specified

3、显示数据库表中部分表

SHOW [FULL] TABLES [FROM db_name] [like_or_where]

(root@localhost) [(none)]> show tables from superdb like 'emp%';
+--------------------------+
| Tables_in_superdb (emp%) |
+--------------------------+
| emp                      |
| employees                |
+--------------------------+
2 rows in set (0.01 sec)(root@localhost) [(none)]> show full tables from superdb like 'emp%';
+--------------------------+------------+
| Tables_in_superdb (emp%) | Table_type |
+--------------------------+------------+
| emp                      | BASE TABLE |
| employees                | BASE TABLE |
+--------------------------+------------+
2 rows in set (0.04 sec)

4、显示表的字段信息

(root@localhost) [(none)]> desc superdb.emp;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| empno    | int unsigned | NO   | PRI | NULL    | auto_increment |
| ename    | varchar(15)  | YES  |     | NULL    |                |
| job      | varchar(10)  | YES  |     | NULL    |                |
| mgr      | int unsigned | YES  |     | NULL    |                |
| hiredate | date         | YES  |     | NULL    |                |
| sal      | decimal(7,2) | YES  |     | NULL    |                |
| comm     | decimal(7,2) | YES  |     | NULL    |                |
| deptno   | int unsigned | YES  | MUL | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)-- OR (root@localhost) [(none)]> select * from information_schema.COLUMNS where table_schema='superdb' and table_name='emp';
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+--------------+------------+----------------+---------------------------------+--------------------------------+-----------------------+--------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME     | COLUMN_TYPE  | COLUMN_KEY | EXTRA          | PRIVILEGES                      | COLUMN_COMMENT                 | GENERATION_EXPRESSION | SRS_ID |
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+--------------+------------+----------------+---------------------------------+--------------------------------+-----------------------+--------+
| def           | superdb      | emp        | empno       |                1 | NULL           | NO          | int       |                     NULL |                   NULL |                10 |             0 |               NULL | NULL               | NULL               | int unsigned | PRI        | auto_increment | select,insert,update,references | 雇员编号                       |                       |   NULL |
| def           | superdb      | emp        | ename       |                2 | NULL           | YES         | varchar   |                       15 |                     60 |              NULL |          NULL |               NULL | utf8mb4            | utf8mb4_0900_ai_ci | varchar(15)  |            |                | select,insert,update,references | 雇员姓名                       |                       |   NULL |
| def           | superdb      | emp        | job         |                3 | NULL           | YES         | varchar   |                       10 |                     40 |              NULL |          NULL |               NULL | utf8mb4            | utf8mb4_0900_ai_ci | varchar(10)  |            |                | select,insert,update,references | 雇员职位                       |                       |   NULL |
| def           | superdb      | emp        | mgr         |                4 | NULL           | YES         | int       |                     NULL |                   NULL |                10 |             0 |               NULL | NULL               | NULL               | int unsigned |            |                | select,insert,update,references | 雇员对应的领导的编号           |                       |   NULL |
| def           | superdb      | emp        | hiredate    |                5 | NULL           | YES         | date      |                     NULL |                   NULL |              NULL |          NULL |               NULL | NULL               | NULL               | date         |            |                | select,insert,update,references | 雇员的雇佣日期                 |                       |   NULL |
| def           | superdb      | emp        | sal         |                6 | NULL           | YES         | decimal   |                     NULL |                   NULL |                 7 |             2 |               NULL | NULL               | NULL               | decimal(7,2) |            |                | select,insert,update,references | 雇员的基本工资                 |                       |   NULL |
| def           | superdb      | emp        | comm        |                7 | NULL           | YES         | decimal   |                     NULL |                   NULL |                 7 |             2 |               NULL | NULL               | NULL               | decimal(7,2) |            |                | select,insert,update,references | 奖金                           |                       |   NULL |
| def           | superdb      | emp        | deptno      |                8 | NULL           | YES         | int       |                     NULL |                   NULL |                10 |             0 |               NULL | NULL               | NULL               | int unsigned | MUL        |                | select,insert,update,references | 所在部门                       |                       |   NULL |
+---------------+--------------+------------+-------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+--------------------+--------------+------------+----------------+---------------------------------+--------------------------------+-----------------------+--------+
8 rows in set (0.01 sec)

5、显示表的索引信息

(root@localhost) [(none)]> show index from superdb.emp;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| emp   |          0 | PRIMARY  |            1 | empno       | A         |          14 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| emp   |          1 | deptno   |            1 | deptno      | A         |           3 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.01 sec)-- extended index 索引扩展信息(root@localhost) [(none)]> show extended index from superdb.emp;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| emp   |          0 | PRIMARY  |            1 | empno       | A         |          14 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| emp   |          0 | PRIMARY  |            2 | DB_TRX_ID   | A         |        NULL |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| emp   |          0 | PRIMARY  |            3 | DB_ROLL_PTR | A         |        NULL |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| emp   |          0 | PRIMARY  |            4 | ename       | A         |        NULL |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| emp   |          0 | PRIMARY  |            5 | job         | A         |        NULL |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| emp   |          0 | PRIMARY  |            6 | mgr         | A         |        NULL |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| emp   |          0 | PRIMARY  |            7 | hiredate    | A         |        NULL |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| emp   |          0 | PRIMARY  |            8 | sal         | A         |        NULL |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| emp   |          0 | PRIMARY  |            9 | comm        | A         |        NULL |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| emp   |          0 | PRIMARY  |           10 | deptno      | A         |        NULL |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| emp   |          1 | deptno   |            1 | deptno      | A         |           3 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| emp   |          1 | deptno   |            2 | empno       | A         |        NULL |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
12 rows in set (0.01 sec)

6、显示表状态

SHOW TABLE STATUS

-- show table STATUS from superdb like 'emp%';
(root@localhost) [(none)]> show table STATUS from superdb like 'emp%' \G;
*************************** 1. row ***************************Name: empEngine: InnoDBVersion: 10Row_format: DynamicRows: 14Avg_row_length: 1170Data_length: 16384
Max_data_length: 0Index_length: 16384Data_free: 0Auto_increment: 7934Create_time: 2023-10-12 20:17:22Update_time: NULLCheck_time: NULLCollation: utf8mb4_0900_ai_ciChecksum: NULLCreate_options:Comment: 雇员信息表
*************************** 2. row ***************************Name: employeesEngine: InnoDBVersion: 10Row_format: DynamicRows: 107Avg_row_length: 153Data_length: 16384
Max_data_length: 0Index_length: 65536Data_free: 0Auto_increment: NULLCreate_time: 2023-10-12 20:17:22Update_time: NULLCheck_time: NULLCollation: utf8mb4_0900_ai_ciChecksum: NULLCreate_options:Comment: employees table. Contains 107 rows. References with departments,jobs, job_history tables. Contains a self reference.
2 rows in set (0.00 sec)ERROR:
No query specified

7、显示创建表的信息

show create table emp;

show create table emp;CREATE TABLE `emp` (`empno` int unsigned NOT NULL AUTO_INCREMENT COMMENT '雇员编号',`ename` varchar(15) DEFAULT NULL COMMENT '雇员姓名',`job` varchar(10) DEFAULT NULL COMMENT '雇员职位',`mgr` int unsigned DEFAULT NULL COMMENT '雇员对应的领导的编号',`hiredate` date DEFAULT NULL COMMENT '雇员的雇佣日期',`sal` decimal(7,2) DEFAULT NULL COMMENT '雇员的基本工资',`comm` decimal(7,2) DEFAULT NULL COMMENT '奖金',`deptno` int unsigned DEFAULT NULL COMMENT '所在部门',PRIMARY KEY (`empno`),KEY `deptno` (`deptno`),CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`deptno`) REFERENCES `dept` (`deptno`)
) ENGINE=InnoDB AUTO_INCREMENT=7935 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='雇员信息表'

8、显示binlog信息

-- SHOW {BINARY | MASTER} LOGSshow binary LOGS; -- mysql8.0/8.4show master LOGS; -- mysql8.0show master status;     -- mysql8.0show binary log status; -- mysql8.4-- SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
(root@localhost) [(none)]> SHOW BINLOG EVENTS IN 'binlog.000030';
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                              |
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
| binlog.000030 |   4 | Format_desc    |         1 |         126 | Server ver: 8.0.28, Binlog ver: 4 |
| binlog.000030 | 126 | Previous_gtids |         1 |         157 |                                   |
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
2 rows in set (0.01 sec)(root@localhost)[(none)]> SHOW BINLOG EVENTS IN 'binlog.000034';
+---------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                                                                 |
+---------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------------------------+
| binlog.000034 |   4 | Format_desc    |    853306 |         127 | Server ver: 8.4.0, Binlog ver: 4                                                     |
| binlog.000034 | 127 | Previous_gtids |    853306 |         158 |                                                                                      |
| binlog.000034 | 158 | Anonymous_Gtid |    853306 |         235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                 |
| binlog.000034 | 235 | Query          |    853306 |         331 | use `superdb`; ANALYZE TABLE employees                                               |
| binlog.000034 | 331 | Anonymous_Gtid |    853306 |         408 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                 |
| binlog.000034 | 408 | Query          |    853306 |         550 | use `superdb`; ANALYZE TABLE employees update histogram on EMPLOYEE_ID,DEPARTMENT_ID |
| binlog.000034 | 550 | Anonymous_Gtid |    853306 |         627 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'                                                 |
| binlog.000034 | 627 | Query          |    853306 |         767 | use `superdb`; ANALYZE TABLE employees drop histogram on EMPLOYEE_ID,DEPARTMENT_ID   |
+---------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------------------------+
8 rows in set (0.02 sec)(root@localhost)[(none)]> SHOW BINLOG EVENTS IN 'binlog.000034' from 127 limit 3;
+---------------+-----+----------------+-----------+-------------+----------------------------------------+
| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                   |
+---------------+-----+----------------+-----------+-------------+----------------------------------------+
| binlog.000034 | 127 | Previous_gtids |    853306 |         158 |                                        |
| binlog.000034 | 158 | Anonymous_Gtid |    853306 |         235 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |
| binlog.000034 | 235 | Query          |    853306 |         331 | use `superdb`; ANALYZE TABLE employees |
+---------------+-----+----------------+-----------+-------------+----------------------------------------+
3 rows in set (0.00 sec)