内容简介：MySQL中有很多的基本命令，show命令也是其中之一，在很多使用者中对show命令的使用还容易产生混淆，本文汇集了show命令的众多用法。show命令可以提供关于数据库、表、列，或关于服务器的状态信息。#总结# 显示二进制文件以及文件大小（需要开启二进制日志记录功能）
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;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| 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 | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| 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 | |
| plugin | char(64) | NO | | mysql_native_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint(5) unsigned | YES | | NULL | |
| 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;
+--------------------+-----+----------------+------------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+--------------------+-----+----------------+------------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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;
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| 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 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| utf16le | UTF-16LE Unicode | utf16le_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 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
| 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;
+----+------+-----------+-------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+-------+---------+------+----------+------------------+
| 6 | root | localhost | mysql | Query | 0 | starting | SHOW PROCESSLIST |
+----+------+-----------+-------+---------+------+----------+------------------+
1 row in set (0.00 sec)
Linux公社的RSS地址 ： https://www.linuxidc.com/rssFeed.aspx
本文永久更新链接地址： https://www.linuxidc.com/Linux/2019-06/159143.htm
以上就是本文的全部内容，希望本文的内容对大家的学习或者工作能带来一定的帮助，也希望大家多多支持 码农网
猜你喜欢:
本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。
Think Python
Allen B. Downey / O'Reilly Media / 2012-8-23 / GBP 29.99
Think Python is an introduction to Python programming for students with no programming experience. It starts with the most basic concepts of programming, and is carefully designed to define all terms ......一起来看看 《Think Python》 这本书的介绍吧!
