mysql的show命令总结_MySQL常用show命令使用总结

最新推荐文章于 2023-09-05 08:30:00 发布

Sabrina Lee

最新推荐文章于 2023-09-05 08:30:00 发布

阅读量715

点赞数

文章标签： mysql的show命令总结

本文链接：https://blog.csdn.net/weixin_28725949/article/details/113258756

版权

MySQL中有很多的基本命令，show命令也是其中之一，在很多使用者中对show命令的使用还容易产生混淆，本文汇集了show命令的众多用法。show命令可以提供关于数据库、表、列，或关于服务器的状态信息。

#总结

# 显示二进制文件以及文件大小(需要开启二进制日志记录功能)

SHOW {BINARY | MASTER} LOGS

# 显示二进制文件的执行过程

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

# 显示MySQL当前支持哪些字符集

SHOW CHARACTER SET [like_or_where]

# 显示MySQL支持字符集的排序规则

SHOW COLLATION [like_or_where]

# 显示表的列信息(等同于DESC，需要先创建表)

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

# 显示mysql中所有数据库的名称

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]

# 显示Master当前正在使用的二进制信息

SHOW MASTER STATUS

# 列举在表缓存中当前被打开的非TEMPORARY表

SHOW OPEN TABLES [FROM db_name] [like_or_where]

# 显示MySQL插件信息

SHOW PLUGINS

# 服务器内部调试，显示一个指定存储的内部实现的表示形式过程

SHOW PROCEDURE CODE proc_name

# 显示存储过程信息(需要先创建存储过程)

SHOW PROCEDURE STATUS [like_or_where]

# 显示MySQL所支持的所有权限，及权限可操作的对象

SHOW PRIVILEGES

# 显示系统中正在运行的所有进程，普通用户只能查看自己的进行信息

SHOW [FULL] PROCESSLIST

# 显示当前会话执行语句资源使用情况

SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]

# 显示当前会话执行语句资源使用情况

SHOW PROFILES

# 显示relaylog事件信息(需要先做主从复制)

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

# 显示Master主机上已注册的复制主机列表(需要先做主从复制)

SHOW SLAVE HOSTS

# 显示Slave主机状态信息(需要先做主从复制)

SHOW SLAVE STATUS [FOR CHANNEL channel]

# 显示MySQL状态信息

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]

# 显示MySQL变量信息

SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]

# 显示最后一个执行语句所产生的警告信息

SHOW WARNINGS [LIMIT [offset,] row_count]

#实例

显示建表建库语句

mysql> SHOW DATABASES;

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

| Database |

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

| information_schema |

| mysql |

| performance_schema |

| pubmmrpg |

| student |

| sys |

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

6 rows in set (0.01 sec)

mysql> SHOW CREATE DATABASE MYSQL;

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

| Database | Create Database |

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

| MYSQL | CREATE DATABASE `MYSQL` /*!40100 DEFAULT CHARACTER SET utf8 */ |

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

1 row in set (0.00 sec)

mysql> USE mysql;

Database changed

mysql> SHOW TABLES;

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

| Tables_in_mysql |

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

| columns_priv |

| db |

| engine_cost |

| event |

| func |

| general_log |

| gtid_executed |

| help_category |

| help_keyword |

| help_relation |

| help_topic |

| innodb_index_stats |

| innodb_table_stats |

| ndb_binlog_index |

| plugin |

| proc |

| procs_priv |

| proxies_priv |

| server_cost |

| servers |

| slave_master_info |

| slave_relay_log_info |

| slave_worker_info |

| slow_log |

| tables_priv |

| time_zone |

| time_zone_leap_second |

| time_zone_name |

| time_zone_transition |

| time_zone_transition_type |

| user |

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

31 rows in set (0.00 sec)

mysql> SHOW CREATE TABLE mysql.user\G;

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

Table: user

Create Table: CREATE TABLE `user` (

`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',

`User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',

`Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',

`ssl_cipher` blob NOT NULL,

`x509_issuer` blob NOT NULL,

`x509_subject` blob NOT NULL,

`max_questions` int(11) unsigned NOT NULL DEFAULT '0',

`max_updates` int(11) unsigned NOT NULL DEFAULT '0',

`max_connections` int(11) unsigned NOT NULL DEFAULT '0',

`max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',

`plugin` char(64) COLLATE utf8_bin NOT NULL DEFAULT 'mysql_native_password',

`authentication_string` text COLLATE utf8_bin,

`password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

`password_last_changed` timestamp NULL DEFAULT NULL,

`password_lifetime` smallint(5) unsigned DEFAULT NULL,

`account_locked` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',

PRIMARY KEY (`Host`,`User`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges'

1 row in set (0.00 sec)

mysql> SHOW COLUMNS FROM user;

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

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

| Host | char(60) | NO | PRI | | |

| User | char(32) | NO | PRI | | |

| Select_priv | enum('N','Y') | NO | | N | |

| Insert_priv | enum('N','Y') | NO | | N | |

| Update_priv | enum('N','Y') | NO | | N | |

| Delete_priv | enum('N','Y') | NO | | N | |

| Create_priv | enum('N','Y') | NO | | N | |

| Drop_priv | enum('N','Y') | NO | | N | |

| Reload_priv | enum('N','Y') | NO | | N | |

| Shutdown_priv | enum('N','Y') | NO | | N | |

| Process_priv | enum('N','Y') | NO | | N | |

| File_priv | enum('N','Y') | NO | | N | |

| Grant_priv | enum('N','Y') | NO | | N | |

| References_priv | enum('N','Y') | NO | | N | |

| Index_priv | enum('N','Y') | NO | | N | |

| Alter_priv | enum('N','Y') | NO | | N | |

| Show_db_priv | enum('N','Y') | NO | | N | |

| Super_priv | enum('N','Y') | NO | | N | |

| Create_tmp_table_priv | enum('N','Y') | NO | | N | |

| Lock_tables_priv | enum('N','Y') | NO | | N | |

| Execute_priv | enum('N','Y') | NO | | N | |

| Repl_slave_priv | enum('N','Y') | NO | | N | |

| Repl_client_priv | enum('N','Y') | NO | | N | |

| Create_view_priv | enum('N','Y') | NO | | N | |

| Show_view_priv | enum('N','Y') | NO | | N | |

| Create_routine_priv | enum('N','Y') | NO | | N | |

| Alter_routine_priv | enum('N','Y') | NO | | N | |

| Create_user_priv | enum('N','Y') | NO | | N | |

| Event_priv | enum('N','Y') | NO | | N | |

| Trigger_priv | enum('N','Y') | NO | | N | |

| Create_tablespace_priv | enum('N','Y') | NO | | N | |

| max_questions | int(11) unsigned | NO | | 0 | |

| max_updates | int(11) unsigned | NO | | 0 | |

| max_connections | int(11) unsigned | NO | | 0 | |

| max_user_connections | int(11) unsigned | NO | | 0 | |

| password_expired | enum('N','Y') | NO | | N | |

| account_locked | enum('N','Y') | NO | | N | |

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

45 rows in set (0.01 sec)

显示二进制文件相关

mysql> SHOW MASTER LOGS;

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

| Log_name | File_size |

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

| mysql-bin.002022 | 817345 |

| mysql-bin.002023 | 817932 |

| mysql-bin.002024 | 816758 |

| mysql-bin.002025 | 664678 |

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

4 rows in set (0.01 sec)

mysql> SHOW BINARY LOGS;

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

| Log_name | File_size |

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

| mysql-bin.002022 | 817345 |

| mysql-bin.002023 | 817932 |

| mysql-bin.002024 | 816758 |

| mysql-bin.002025 | 665265 |

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

4 rows in set (0.01 sec)

显示主从相关

mysql> SHOW SLAVE HOSTS;

Empty set (0.00 sec)

mysql> SHOW SLAVE STATUS;

Empty set (0.00 sec)

mysql> SHOW RELAYLOG EVENTS limit 10;

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

| slave-relay.001351 | 4 | Format_desc | 1339158577 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 |

| slave-relay.001351 | 123 | Previous_gtids | 1339158577 | 194 | 4941480b-c2fa-11e8-850c-506b4bbe1cf4:1-3111622 |

| slave-relay.001351 | 194 | Rotate | 2241191473 | 0 | mysql-bin.001063;pos=4 |

| slave-relay.001351 | 241 | Format_desc | 2241191473 | 123 | Server ver: 5.7.20-log, Binlog ver: 4 |

| slave-relay.001351 | 360 | Rotate | 0 | 407 | mysql-bin.001063;pos=234 |

| slave-relay.001351 | 407 | Gtid | 2241191473 | 299 | SET @@SESSION.GTID_NEXT= '4941480b-c2fa-11e8-850c-506b4bbe1cf4:3111623' |

| slave-relay.001351 | 472 | Query | 2241191473 | 539 | /* rds internal mark */ CREATE TABLE IF NOT EXISTS mysql.ha_health_check (

id BIGINT DEFAULT 0,

type CHAR(1) DEFAULT '0',

PRIMARY KEY (type)

)

ENGINE = InnoDB |

| slave-relay.001351 | 712 | Gtid | 2241191473 | 604 | SET @@SESSION.GTID_NEXT= '4941480b-c2fa-11e8-850c-506b4bbe1cf4:3111624' |

| slave-relay.001351 | 777 | Query | 2241191473 | 672 | BEGIN |

| slave-relay.001351 | 845 | Table_map | 2241191473 | 734 | table_id: 81659 (mysql.ha_health_check) |

10 rows in set (0.01 sec)

mysql> SHOW SLAVE STATUS\G;

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

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.120.26

Master_User: replicator

Master_Port: 3069

Connect_Retry: 60

Master_Log_File: mysql-bin.001063

Read_Master_Log_Pos: 3564725

Relay_Log_File: slave-relay.001352

Relay_Log_Pos: 844419

Relay_Master_Log_File: mysql-bin.001063

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: 3564725

Relay_Log_Space: 3565260

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: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 2241191473

Master_UUID: 4941480b-c2fa-11e8-850c-506b4bbe1cf4

Master_Info_File: mysql.slave_master_info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Last_SQL_Error_Gtid:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set: 4941480b-c2fa-11e8-850c-506b4bbe1cf4:1-3119404

Executed_Gtid_Set: 3c09db04-c2fa-11e8-b5cc-506b4bff2084:1-2275307,

4941480b-c2fa-11e8-850c-506b4bbe1cf4:1-3119404

Auto_Position: 1

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.01 sec)

显示字符集变量相关

mysql> SHOW CHARACTER SET;

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

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

| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |

| dec8 | DEC West European | dec8_swedish_ci | 1 |

| cp850 | DOS West European | cp850_general_ci | 1 |

| hp8 | HP West European | hp8_english_ci | 1 |

| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |

| tis620 | TIS620 Thai | tis620_thai_ci | 1 |

| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |

| cp1250 | Windows Central European | cp1250_general_ci | 1 |

| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |

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

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

| cp866 | DOS Russian | cp866_general_ci | 1 |

| cp852 | DOS Central European | cp852_general_ci | 1 |

| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |

| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |

| cp1256 | Windows Arabic | cp1256_general_ci | 1 |

| cp1257 | Windows Baltic | cp1257_general_ci | 1 |

| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |

| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |

| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |

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

41 rows in set (0.02 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'character%';

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

| Variable_name | Value |

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

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | utf8 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | utf8 |

| character_set_system | utf8 |

| character_sets_dir | /usr/share/mysql/charsets/ |

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

8 rows in set (0.00 sec)

显示用户权限相关

mysql> SHOW GRANTS FOR test;

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

| Grants for test@% |

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

| GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' |

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

1 row in set (0.00 sec)

显示运行进程

mysql> SHOW PROCESSLIST;

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

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

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

1 row in set (0.00 sec)

Sabrina Lee

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫