MySQL SHOW 指令大全：從基礎到進階的完整語法速查

核心 SHOW 命令分類

1. 字符集與排序規則

SHOW CHARACTER SET [LIKE 'pattern' | WHERE expr];
SHOW COLLATION [LIKE 'pattern' | WHERE expr];

2. 資料庫與表結構

SHOW DATABASES [LIKE 'pattern' | WHERE expr];
SHOW [FULL] TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr];
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE 'pattern' | WHERE expr];
SHOW CREATE DATABASE db_name;
SHOW CREATE TABLE tbl_name;
SHOW CREATE VIEW view_name;
SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern' | WHERE expr];

3. 索引與約束

SHOW INDEX FROM tbl_name [FROM db_name];
SHOW KEYS FROM tbl_name [FROM db_name];  -- 同義詞

4. 存儲引擎與插件

SHOW [STORAGE] ENGINES;
SHOW ENGINE engine_name {STATUS | MUTEX};
SHOW PLUGINS;

5. 進程、鎖與性能

SHOW [FULL] PROCESSLIST;
SHOW [GLOBAL | SESSION] STATUS [LIKE 'pattern' | WHERE expr];
SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern' | WHERE expr];
SHOW OPEN TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr];

6. 權限與用戶

SHOW GRANTS FOR user;
SHOW PRIVILEGES;
SHOW CONTRIBUTORS;

7. 存儲程序、觸發器、事件

SHOW CREATE PROCEDURE proc_name;
SHOW CREATE FUNCTION func_name;
SHOW CREATE TRIGGER trigger_name;
SHOW CREATE EVENT event_name;
SHOW PROCEDURE STATUS [LIKE 'pattern' | WHERE expr];
SHOW FUNCTION STATUS [LIKE 'pattern' | WHERE expr];
SHOW TRIGGERS [FROM db_name] [LIKE 'pattern' | WHERE expr];
SHOW EVENTS;

8. 日誌、備份、復制

SHOW BINARY LOGS;
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
SHOW MASTER STATUS;
SHOW SLAVE STATUS;
SHOW RELAYLOG EVENTS;

9. 診斷與調試

SHOW ERRORS [LIMIT [offset,] row_count];
SHOW WARNINGS [LIMIT [offset,] row_count];
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n];
SHOW PROFILES;

常用速查別名（兼容舊版）

mysqlshow 命令行工具

無需登入交互式客戶端，直接在 Shell 獲取元數據：

# 顯示所有庫
mysqlshow -uroot -pXXXX

# 顯示 mysql 庫下所有表
mysqlshow -uroot -pXXXX mysql

# 顯示 mysql.user 表結構
mysqlshow -uroot -pXXXX mysql user

# 顯示 mysql.user 表中 Host 列信息
mysqlshow -uroot -pXXXX mysql user Host

獲取幫助

HELP SHOW
# 或在客戶端輸入
help show

總結

SHOW 是 MySQL 內省能力的核心，掌握其完整語法與過濾選項（LIKE/WHERE），能讓你在元數據查詢、性能診斷、權限審計、復制監控等場景游刃有餘。建議收藏備查。