| 命令 | 用途 | 關鍵輸出 |
|---|---|---|
SHOW DATABASES; | 列出所有資料庫 | Database |
SHOW TABLES [FROM db]; | 列出表 | Tables_in_db |
SHOW CREATE DATABASE db; | 庫建立語句 | Create Database |
SHOW CREATE TABLE tbl; | 表建立語句 (含索引、引擎、字元集) | Create Table |
SHOW COLUMNS FROM tbl [FROM db]; | 欄位定義 | Field, Type, Null, Key, Default, Extra |
DESC tbl; | SHOW COLUMNS 簡寫 | 同上 |
SHOW INDEX FROM tbl; | 索引詳情 | Key_name, Seq_in_index, Column_name, Cardinality, Index_type |
SHOW TABLE STATUS [FROM db]; | 表統計 (行數、大小、引擎、自增值、更新時間) | Name, Engine, Rows, Data_length, Index_length, Auto_increment |
SHOW GRANTS [FOR 'user'@'host']; | 權限授予語句 | Grants for user@host |
SHOW PRIVILEGES; | 支援的權限列表 | Privilege, Context, Comment |
SHOW VARIABLES [LIKE 'pattern']; | 系統變數 (配置參數) | Variable_name, Value |
SHOW STATUS [LIKE 'pattern']; | 運行狀態計數器 | Variable_name, Value |
SHOW PROCESSLIST [FULL]; | 當前執行緒 (需 PROCESS 權限看全) | Id, User, Host, db, Command, Time, State, Info |
SHOW ENGINES; | 可用儲存引擎與預設 | Engine, Support, Comment, Transactions, XA, Savepoints |
SHOW ENGINE INNODB STATUS; | InnoDB 內部狀態 (鎖、事務、Buffer Pool、死鎖) | Status (長文本) |
SHOW WARNINGS; | 上一條語句的警告 | Level, Code, Message |
SHOW ERRORS; | 上一條語句的錯誤 | Level, Code, Message |
SHOW PROCEDURE STATUS [LIKE 'pattern']; | 儲存過程列表 | Db, Name, Type, Definer, Created, Modified |
SHOW CREATE PROCEDURE sp_name; | 儲存過程定義 | Procedure, sql_mode, Create Procedure, character_set_client, collation_connection |
SHOW TRIGGERS; | 觸發器列表 | Trigger, Event, Table, Statement, Timing |
SHOW EVENTS; | 排程事件列表 | Db, Name, Definer, Time_zone, Type, Execute_at |
SHOW PLUGINS; | 已安裝插件 | Name, Status, Type, Library, License |
SHOW MASTER STATUS; | 主庫 Binlog 位置 | File, Position, Binlog_Do_DB, Binlog_Ignore_DB, Executed_Gtid_Set |
SHOW SLAVE STATUS; | 從庫複製狀態 | Slave_IO_State, Master_Host, Seconds_Behind_Master, ... |
SHOW BINLOG EVENTS [IN 'file'] [FROM pos]; | Binlog 事件內容 | Log_name, Pos, Event_type, Server_id, End_log_pos, Info |
-- 慢查詢現場
SHOW PROCESSLIST;
SHOW ENGINE INNODB STATUS\G
-- 複製延遲排查
SHOW SLAVE STATUS\G
SHOW MASTER STATUS;
-- 空間暴增定位
SHOW TABLE STATUS FROM db WHERE Data_length > 1000000000;
-- 權限審計
SHOW GRANTS FOR 'app'@'%';
SHOW PRIVILEGES;SHOW 系列是 MySQL 的「CT 掃描儀」。熟練掌握上述 25+ 命令，配合 \G 垂直顯示、LIKE 過濾、information_schema 程式化查詢，讓資料庫內部狀態無所遁形。
