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]
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]
show character set;

show character set like 'utf8';

show character set like 'utf8%';

show character set where charset='utf8';
  • 输出包括所有可用的排序规则
    • SHOW COLLATION [like_or_where]
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]
show full {COLUMNS | FIELDS} from 表名; // 除了显示字段结构，还显示对应权限

show {COLUMNS | FIELDS} from 表名; //显示表字段结构

show {COLUMNS | FIELDS} from 表名 from 库名;

show {COLUMNS | FIELDS} from 表名.库名;
  • 显示创建数据库的语句
    • SHOW CREATE DATABASE db_name
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
show create function 存储函数名
  • 显示创建命名表的CREATE TABLE语句
    • SHOW CREATE TABLE tbl_name
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
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
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 [email protected]: 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
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 databases;
  • 显示有关存储引擎的操作信息
    • SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW ENGINE INNODB STATUS
SHOW ENGINE INNODB MUTEX
SHOW ENGINE PERFORMANCE_SCHEMA STATUS
  • 显示有关服务器存储引擎的状态信息
    • SHOW [STORAGE] ENGINES
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
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”.
}

mysql> SHOW GRANTS FOR 'jeffrey'@'localhost';
+------------------------------------------------------------------+
| Grants for [email protected]                                     |
+------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `jeffrey`@`localhost`                      |
| GRANT SELECT, INSERT, UPDATE ON `db1`.* TO `jeffrey`@`localhost` |
+------------------------------------------------------------------+

mysql> SHOW GRANTS FOR 'u1'@'localhost' USING 'r1';
+---------------------------------------------+
| Grants for [email protected]                     |
+---------------------------------------------+
| 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 [email protected]                                     |
+-------------------------------------------------------------+
| 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 [email protected]                                             |
+---------------------------------------------------------------------+
| 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]
SHOW INDEX FROM mytable FROM mydb;
SHOW INDEX FROM mydb.mytable;
  • 显示有关主服务器的二进制日志文件的状态信息
    • SHOW MASTER STATUS
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
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
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
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]
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]
show table status from 库名 where name='对应表名';
  • 列出所有表
    • SHOW [FULL] TABLES [FROM db_name] [like_or_where]
show tables;
  • 列出了当前为数据库中的表定义的触发器
    • SHOW TRIGGERS [FROM db_name] [like_or_where]
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: [email protected]
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_0900_ai_ci
  • 显示MySQL系统变量的值
    • SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
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

本文为铭原创文章,转载无需和我联系,但请注明来自铭博客https://8ary.com

黄铭博客
请先登录后发表评论
  • latest comments
  • 总共0条评论