MySQL基础：show命令总结
程序员文章站
2022-04-29 18:15:43
show命令 show 命令可以提供关于数据库、表、列，或关于服务器的状态信息。 总结 实例 显示建表建库语句 sql mysql SHOW DATABASES; + + | Database | + + | information_schema | | mysql | | performance_ ......
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)
上一篇： ASP.NET过滤器的应用方法介绍
下一篇： MySQL优化之查询_MySQL