https://dev.mysql.com/doc/refman/8.0/en/show.html
MySQL 所有 show 语法
列出服务器上的二进制日志文件
-
SHOW {BINARY | MASTER} LOGS :
显示二进制日志中的事件
-
SHOW BINLOG EVENTS [IN ‘log_name’] [FROM pos] [LIMIT [offset,] row_count]
1 2 3 4 5 6 7
| show binlog events \G;
show binlog events in '日志名' \G;
show binlog events in '日志名' from 起点位置 \G;
show binlog events in '日志名' from 起点位置 limit 条数;
|
- 显示所有可用的字符集
-
SHOW CHARACTER SET [like_or_where]
1 2 3 4 5 6 7 8
| show character set;
show character set like 'utf8';
show character set like 'utf8%';
show character set where charset='utf8';
|
- 输出包括所有可用的排序规则
-
SHOW COLLATION [like_or_where]
1 2 3
| show collation where charset='utf8';
show collation like 'utf8%';
|
- 显示表字段结构
-
SHOW [EXTENDED] [FULL] {COLUMNS | FIELDS} {FROM | IN} tbl_name [{FROM | IN} db_name] [LIKE ‘pattern’ | WHERE expr]
1 2 3 4 5 6 7 8
| show full {COLUMNS | FIELDS} from 表名; // 除了显示字段结构,还显示对应权限
show {COLUMNS | FIELDS} from 表名; //显示表字段结构
show {COLUMNS | FIELDS} from 表名 from 库名;
show {COLUMNS | FIELDS} from 表名.库名;
|
- 显示创建数据库的语句
-
SHOW CREATE DATABASE db_name
1 2 3 4 5 6
| show create database 库名; + | Database | Create Database | + | larblog | CREATE DATABASE `larblog` | +
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14
| SHOW CREATE TRIGGER ins_sum\G *************************** 1. row *************************** Trigger: ins_sum sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES, NO_ZERO_IN_DATE,NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION SQL Original Statement: CREATE DEFINER=`me`@`localhost` TRIGGER `ins_sum` BEFORE INSERT ON `account` FOR EACH ROW SET @sum = @sum + NEW.amount character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci Created: 2018-08-08 10:10:12.61
|
1 2 3 4 5 6 7 8 9 10
| mysql> CREATE USER 'u1'@'localhost' IDENTIFIED BY 'secret'; mysql> SET print_identified_with_as_hex = ON; mysql> SHOW CREATE USER 'u1'@'localhost'\G *************************** 1. row *************************** CREATE USER for u1@localhost: CREATE USER 'u1'@'localhost' IDENTIFIED WITH 'caching_sha2_password' AS 0x244124303035240C7745603626313D613C4C10633E0A104B1E14135A544A7871567245614F4872344643546336546F624F6C7861326932752F45622F4F473273597557627139 REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
|
- 显示用于创建命名视图的CREATE VIEW语句
-
SHOW CREATE VIEW view_name
1 2 3 4 5 6 7 8 9
| mysql> SHOW CREATE VIEW v\G *************************** 1. row *************************** View: v Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`bob`@`localhost` SQL SECURITY DEFINER VIEW `v` AS select 1 AS `a`,2 AS `b` character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci
|
- 显示所有数据库
-
SHOW {DATABASES | SCHEMAS} [LIKE ‘pattern’ | WHERE expr]
- 显示有关存储引擎的操作信息
-
SHOW ENGINE engine_name {STATUS | MUTEX}
1 2 3
| SHOW ENGINE INNODB STATUS SHOW ENGINE INNODB MUTEX SHOW ENGINE PERFORMANCE_SCHEMA STATUS
|
- 显示错误、警告等信息
-
SHOW ERRORS [LIMIT [offset,] row_count]
-
SHOW COUNT(*) ERRORS;
-
SHOW EVENTS
-
SHOW FUNCTION CODE func_name
-
SHOW FUNCTION STATUS [like_or_where]
- 显示授权
1 2 3 4 5 6 7 8
| SHOW GRANTS [FOR user_or_role [USING role [, role] ...]]
user_or_role: { user (see Section 6.2.4, “Specifying Account Names”) | role (see Section 6.2.5, “Specifying Role Names”. }
|
1 2 3 4 5 6 7
| mysql> SHOW GRANTS FOR 'jeffrey'@'localhost'; + | Grants for jeffrey@localhost | + | GRANT USAGE ON *.* TO `jeffrey`@`localhost` | | GRANT SELECT, INSERT, UPDATE ON `db1`.* TO `jeffrey`@`localhost` | +
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
| mysql> SHOW GRANTS FOR 'u1'@'localhost' USING 'r1'; + | Grants for u1@localhost | + | GRANT USAGE ON *.* TO `u1`@`localhost` | | GRANT SELECT ON `db1`.* TO `u1`@`localhost` | | GRANT `r1`@`%`,`r2`@`%` TO `u1`@`localhost` | + mysql> SHOW GRANTS FOR 'u1'@'localhost' USING 'r2'; + | Grants for u1@localhost | + | GRANT USAGE ON *.* TO `u1`@`localhost` | | GRANT INSERT, UPDATE, DELETE ON `db1`.* TO `u1`@`localhost` | | GRANT `r1`@`%`,`r2`@`%` TO `u1`@`localhost` | + mysql> SHOW GRANTS FOR 'u1'@'localhost' USING 'r1', 'r2'; + | Grants for u1@localhost | + | GRANT USAGE ON *.* TO `u1`@`localhost` | | GRANT SELECT, INSERT, UPDATE, DELETE ON `db1`.* TO `u1`@`localhost` | | GRANT `r1`@`%`,`r2`@`%` TO `u1`@`localhost` | +
|
- 返回表索引信息 - ##### SHOW [EXTENDED] {INDEX | INDEXES | KEYS} {FROM | IN} tbl_name [{FROM | IN} db_name] [WHERE expr]
1 2
| SHOW INDEX FROM mytable FROM mydb; SHOW INDEX FROM mydb.mytable;
|
1 2 3 4 5 6 7 8
| mysql> SHOW MASTER STATUS\G *************************** 1. row *************************** File: master-bin.000002 Position: 1307 Binlog_Do_DB: test Binlog_Ignore_DB: manual, mysql Executed_Gtid_Set: 3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5 1 row in set (0.00 sec)
|
-
SHOW OPEN TABLES [FROM db_name] [like_or_where]
显示所有插件
-
SHOW PROCEDURE CODE proc_name
-
SHOW PROCEDURE STATUS [like_or_where]
显示MySQL服务器支持的系统特权列表
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
| mysql> SHOW PRIVILEGES\G *************************** 1. row *************************** Privilege: Alter Context: Tables Comment: To alter the table *************************** 2. row *************************** Privilege: Alter routine Context: Functions,Procedures Comment: To alter or drop stored functions/procedures *************************** 3. row *************************** Privilege: Create Context: Databases,Tables,Indexes Comment: To create new databases and tables *************************** 4. row *************************** Privilege: Create routine Context: Databases Comment: To use CREATE FUNCTION/PROCEDURE *************************** 5. row *************************** Privilege: Create temporary tables Context: Databases Comment: To use CREATE TEMPORARY TABLE ...
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48
| mysql> SHOW FULL PROCESSLIST\G *************************** 1. row *************************** Id: 1 User: system user Host: db: NULL Command: Connect Time: 1030455 State: Waiting for master to send event Info: NULL *************************** 2. row *************************** Id: 2 User: system user Host: db: NULL Command: Connect Time: 1004 State: Has read all relay log; waiting for the slave I/O thread to update it Info: NULL *************************** 3. row *************************** Id: 3112 User: replikator Host: artemis:2204 db: NULL Command: Binlog Dump Time: 2144 State: Has sent all binlog to slave; waiting for binlog to be updated Info: NULL *************************** 4. row *************************** Id: 3113 User: replikator Host: iconnect2:45781 db: NULL Command: Binlog Dump Time: 2086 State: Has sent all binlog to slave; waiting for binlog to be updated Info: NULL *************************** 5. row *************************** Id: 3123 User: stefan Host: localhost db: apollon Command: Query Time: 0 State: NULL Info: SHOW FULL PROCESSLIST 5 rows in set (0.00 sec)
|
- 当前会话过程中执行的语句的资源使用情况
-
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
-
SHOW RELAYLOG EVENTS [IN ‘log_name’] [FROM pos] [LIMIT [offset,] row_count]
显示当前向主服务器注册的复制从服务器列表
1 2 3 4 5 6 7
| mysql> SHOW SLAVE HOSTS; + | Server_id | Host | Port | Master_id | Slave_UUID | + | 192168010 | iconnect2 | 3306 | 192168011 | 14cb6624-7f93-11e0-b2c0-c80aa9429562 | | 1921680101 | athena | 3306 | 192168011 | 07af4990-f41f-11df-a566-7ac56fdaf645 | +
|
显示从服务器状态
-
SHOW SLAVE STATUS [FOR CHANNEL channel]
显示服务器状态信息
-
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27
| mysql> SHOW STATUS; + | Variable_name | Value | + | Aborted_clients | 0 | | Aborted_connects | 0 | | Bytes_received | 155372598 | | Bytes_sent | 1176560426 | | Connections | 30023 | | Created_tmp_disk_tables | 0 | | Created_tmp_tables | 8340 | | Created_tmp_files | 60 | ... | Open_tables | 1 | | Open_files | 2 | | Open_streams | 0 | | Opened_tables | 44600 | | Questions | 2026873 | ... | Table_locks_immediate | 1920382 | | Table_locks_waited | 0 | | Threads_cached | 0 | | Threads_created | 30022 | | Threads_connected | 1 | | Threads_running | 1 | | Uptime | 80380 | +
|
- 显示表当前状态
-
SHOW TABLE STATUS [FROM db_name] [like_or_where]
1
| show table status from 库名 where name='对应表名';
|
- 列出所有表
-
SHOW [FULL] TABLES [FROM db_name] [like_or_where]
- 列出了当前为数据库中的表定义的触发器
-
SHOW TRIGGERS [FROM db_name] [like_or_where]
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
| mysql> SHOW TRIGGERS LIKE 'acc%'\G *************************** 1. row *************************** Trigger: ins_sum Event: INSERT Table: account Statement: SET @sum = @sum + NEW.amount Timing: BEFORE Created: 2018-08-08 10:10:12.61 sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES, NO_ZERO_IN_DATE,NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_ENGINE_SUBSTITUTION Definer: me@localhost character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8mb4_0900_ai_ci
|
- 显示MySQL系统变量的值
-
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78
| mysql> SHOW VARIABLES; + | Variable_name | Value | + | activate_all_roles_on_login | OFF | | auto_generate_certs | ON | | auto_increment_increment | 1 | | auto_increment_offset | 1 | | autocommit | ON | | automatic_sp_privileges | ON | | avoid_temporal_upgrade | OFF | | back_log | 151 | | basedir | /usr/ | | big_tables | OFF | | bind_address | * | | binlog_cache_size | 32768 | | binlog_checksum | CRC32 | | binlog_direct_non_transactional_updates | OFF | | binlog_error_action | ABORT_SERVER | | binlog_expire_logs_seconds | 2592000 | | binlog_format | ROW | | binlog_group_commit_sync_delay | 0 | | binlog_group_commit_sync_no_delay_count | 0 | | binlog_gtid_simple_recovery | ON | | binlog_max_flush_queue_time | 0 | | binlog_order_commits | ON | | binlog_row_image | FULL | | binlog_row_metadata | MINIMAL | | binlog_row_value_options | | | binlog_rows_query_log_events | OFF | | binlog_stmt_cache_size | 32768 | | binlog_transaction_dependency_history_size | 25000 | | binlog_transaction_dependency_tracking | COMMIT_ORDER | | block_encryption_mode | aes-128-ecb | | bulk_insert_buffer_size | 8388608 |
...
| max_allowed_packet | 67108864 | | max_binlog_cache_size | 18446744073709547520 | | max_binlog_size | 1073741824 | | max_binlog_stmt_cache_size | 18446744073709547520 | | max_connect_errors | 100 | | max_connections | 151 | | max_delayed_threads | 20 | | max_digest_length | 1024 | | max_error_count | 1024 | | max_execution_time | 0 | | max_heap_table_size | 16777216 | | max_insert_delayed_threads | 20 | | max_join_size | 18446744073709551615 |
...
| thread_handling | one-thread-per-connection | | thread_stack | 286720 | | time_zone | SYSTEM | | timestamp | 1530906638.765316 | | tls_version | TLSv1,TLSv1.1,TLSv1.2 | | tmp_table_size | 16777216 | | tmpdir | /tmp | | transaction_alloc_block_size | 8192 | | transaction_allow_batching | OFF | | transaction_isolation | REPEATABLE-READ | | transaction_prealloc_size | 4096 | | transaction_read_only | OFF | | transaction_write_set_extraction | XXHASH64 | | unique_checks | ON | | updatable_views_with_limit | YES | | version | 8.0.12 | | version_comment | MySQL Community Server - GPL | | version_compile_machine | x86_64 | | version_compile_os | Linux | | version_compile_zlib | 1.2.11 | | wait_timeout | 28800 | | warning_count | 0 | | windowing_use_high_precision | ON | +
|
-
SHOW WARNINGS [LIMIT [offset,] row_count]
-
SHOW CREATE EVENT event_name