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