mysql show 语法

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` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+--------------------------------------------------------------------+
  • 显示创建存储函数

    • SHOW CREATE FUNCTION func_name 、 SHOW CREATE PROCEDURE proc_name
      1
      show create function 存储函数名
  • 显示创建命名表的CREATE TABLE语句

    • SHOW CREATE TABLE tbl_name
      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      show create table 表名 \G;
      *************************** 1. row ***************************
      Table: bjy_users
      Create Table: CREATE TABLE `bjy_users` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      `email_verified_at` timestamp NULL DEFAULT NULL,
      `password` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
      `remember_token` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
      `created_at` timestamp NULL DEFAULT NULL,
      `updated_at` timestamp NULL DEFAULT NULL,
      `deleted_at` timestamp NULL DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `bjy_users_email_unique` (`email`)
      ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
      1 row in set (0.00 sec)
  • 显示创建命名触发器的CREATE TRIGGER语句

    • SHOW CREATE TRIGGER trigger_name
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
  • 显示创建命名用户的CREATE USER语句
    • SHOW CREATE USER user
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]
1
show databases;
  • 显示有关存储引擎的操作信息
    • SHOW ENGINE engine_name {STATUS | MUTEX}
1
2
3
SHOW ENGINE INNODB STATUS
SHOW ENGINE INNODB MUTEX
SHOW ENGINE PERFORMANCE_SCHEMA STATUS
  • 显示有关服务器存储引擎的状态信息
    • SHOW [STORAGE] ENGINES
1
show engines;
  • 显示错误、警告等信息
    • SHOW ERRORS [LIMIT [offset,] row_count]
    • SHOW COUNT(*) ERRORS;
  • SHOW EVENTS
  • SHOW FUNCTION CODE func_name
  • SHOW FUNCTION STATUS [like_or_where]
  • 显示授权
    • SHOW GRANTS
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;
  • 显示有关主服务器的二进制日志文件的状态信息
    • SHOW MASTER STATUS
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 PLUGINS
  • SHOW PROCEDURE CODE proc_name
  • SHOW PROCEDURE STATUS [like_or_where]
  • 显示MySQL服务器支持的系统特权列表

    • SHOW PRIVILEGES
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
...
  • 显示正在运行的线程
    • SHOW [FULL] PROCESSLIST
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 PROFILES
  • SHOW RELAYLOG EVENTS [IN ‘log_name’] [FROM pos] [LIMIT [offset,] row_count]
  • 显示当前向主服务器注册的复制从服务器列表

    • SHOW SLAVE HOSTS
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]
1
show tables;
  • 列出了当前为数据库中的表定义的触发器
    • 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