{"msg":"操作成功","code":200,"data":{"createBy":"admin","createTime":"2020-04-23 18:23:19","updateBy":"admin","updateTime":"2020-04-23 18:23:19","remark":null,"id":27,"articleTitle":"MySQL（九）SHOW命令","articleUrl":"mysql_show","articleThumbnail":"https://www.asumimoe.com/imgfiles/20220908/9e192744fab244d39d3b15fca035c9f7.jpg","articleFlag":"0","draftStatus":"1","reprintStatement":"0","articleSummary":"SHOW是 MySQL 中最常用的元数据查询命令，用于获取数据库、表、列、索引等系统信息。它提供了管理员和开发者了解数据库结构、状态和性能的关键途径。","articleContent":"`SHOW` 是 MySQL 中最常用的元数据查询命令，用于**获取数据库、表、列、索引等系统信息**。它提供了管理员和开发者了解数据库结构、状态和性能的关键途径。\n\n## 常用 `SHOW` 命令分类\n\n### 数据库信息\n\n| 命令                            | 描述                      |\n| ------------------------------- | ------------------------- |\n| `SHOW DATABASES;`               | 列出所有数据库            |\n| `SHOW CREATE DATABASE db_name;` | 显示创建数据库的 SQL 语句 |\n\n### 表信息\n\n| 命令                          | 描述                              |\n| ----------------------------- | --------------------------------- |\n| `SHOW TABLES;`                | 列出当前数据库的所有表            |\n| `SHOW FULL TABLES;`           | 列出表及表类型（BASE TABLE/VIEW） |\n| `SHOW CREATE TABLE tbl_name;` | 显示创建表的 SQL 语句             |\n| `SHOW TABLE STATUS;`          | 显示表的详细信息（引擎、行数等）  |\n\n### 列信息\n\n| 命令                               | 描述               |\n| ---------------------------------- | ------------------ |\n| `SHOW COLUMNS FROM tbl_name;`      | 显示表的所有列信息 |\n| `DESCRIBE tbl_name;` (简写 `DESC`) | 同 `SHOW COLUMNS`  |\n\n### 索引信息\n\n| 命令                          | 描述             |\n| ----------------------------- | ---------------- |\n| `SHOW INDEX FROM tbl_name;`   | 显示表的所有索引 |\n| `SHOW INDEXES FROM tbl_name;` | 同上             |\n| `SHOW KEYS FROM tbl_name;`    | 同上             |\n\n### 权限与用户\n\n| 命令                    | 描述             |\n| ----------------------- | ---------------- |\n| `SHOW GRANTS;`          | 显示当前用户权限 |\n| `SHOW GRANTS FOR user;` | 显示指定用户权限 |\n| `SHOW PRIVILEGES;`      | 显示所有可用权限 |\n\n### 服务器状态\n\n| 命令                              | 描述                    |\n| --------------------------------- | ----------------------- |\n| `SHOW STATUS;`                    | 显示服务器状态变量      |\n| `SHOW VARIABLES;`                 | 显示服务器系统变量      |\n| `SHOW PROCESSLIST;`               | 显示当前运行的线程/进程 |\n| `SHOW ENGINE engine_name STATUS;` | 显示存储引擎状态        |\n\n### 其他\n\n| 命令                  | 描述                   |\n| --------------------- | ---------------------- |\n| `SHOW CHARACTER SET;` | 显示所有可用字符集     |\n| `SHOW COLLATION;`     | 显示所有可用校对规则   |\n| `SHOW WARNINGS;`      | 显示上条语句的警告     |\n| `SHOW ERRORS;`        | 显示上条语句的错误     |\n| `SHOW BINARY LOGS;`   | 显示二进制日志文件列表 |\n| `SHOW MASTER STATUS;` | 显示主服务器状态       |\n\n## 常用 `SHOW` 命令详解\n\n### 1. `SHOW DATABASES;`\n\n**功能**：列出所有数据库\n\n```sql\nSHOW DATABASES;\n```\n\n**输出示例**：\n\n```\n+--------------------+\n| Database           |\n+--------------------+\n| information_schema |\n| company_db         |\n| mysql              |\n| performance_schema |\n| sys                |\n| test_db            |\n+--------------------+\n6 rows in set (0.00 sec)\n```\n\n### 2. `SHOW TABLES;`\n\n**功能**：列出当前数据库的所有表\n\n```sql\nUSE company_db;\nSHOW TABLES;\n```\n\n**输出示例**：\n\n```\n+----------------------+\n| Tables_in_company_db |\n+----------------------+\n| departments          |\n| employees            |\n| projects             |\n| salaries             |\n+----------------------+\n4 rows in set (0.00 sec)\n```\n\n### 3. `SHOW CREATE TABLE`\n\n**功能**：显示创建表的 SQL 语句\n\n```sql\nSHOW CREATE TABLE employees;\n```\n\n**输出示例**：\n\n```sql\n+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\n| Table     | Create Table                                                                                                                                                                                                                         |\n+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\n| employees | CREATE TABLE `employees` (\n  `id` int(11) NOT NULL AUTO_INCREMENT,\n  `name` varchar(50) NOT NULL,\n  `email` varchar(100) DEFAULT NULL,\n  `salary` decimal(10,2) DEFAULT '0.00',\n  `hire_date` date NOT NULL,\n  PRIMARY KEY (`id`),\n  UNIQUE KEY `email` (`email`)\n) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8mb4 |\n+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\n1 row in set (0.00 sec)\n```\n\n### 4. `SHOW COLUMNS` / `DESCRIBE`\n\n**功能**：显示表结构信息\n\n```sql\nDESCRIBE employees;\n-- 或\nSHOW COLUMNS FROM employees;\n```\n\n**输出示例**：\n\n```\n+-----------+--------------+------+-----+---------+----------------+\n| Field     | Type         | Null | Key | Default | Extra          |\n+-----------+--------------+------+-----+---------+----------------+\n| id        | int(11)      | NO   | PRI | NULL    | auto_increment |\n| name      | varchar(50)  | NO   |     | NULL    |                |\n| email     | varchar(100) | YES  | UNI | NULL    |                |\n| salary    | decimal(10,2)| YES  |     | 0.00    |                |\n| hire_date | date         | NO   |     | NULL    |                |\n+-----------+--------------+------+-----+---------+----------------+\n5 rows in set (0.00 sec)\n```\n\n### 5. `SHOW INDEX`\n\n**功能**：显示表的所有索引信息\n\n```sql\nSHOW INDEX FROM employees;\n```\n\n**输出示例**：\n\n```\n+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+\n| Table     | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |\n+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+\n| employees |          0 | PRIMARY  |            1 | id          | A         |         100 |     NULL | NULL   |      | BTREE      |         |               |\n| employees |          0 | email    |            1 | email       | A         |         100 |     NULL | NULL   | YES  | BTREE      |         |               |\n+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+\n2 rows in set (0.00 sec)\n```\n\n### 6. `SHOW TABLE STATUS`\n\n**功能**：显示表的详细状态信息\n\n```sql\nSHOW TABLE STATUS LIKE 'employees';\n```\n\n**输出示例**：\n\n```\n+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+\n| Name      | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment |\n+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+\n| employees | InnoDB |      10 | Dynamic    |  100 |            163 |       16384 |               0 |        16384 |         0 |            101 | 2023-08-01 10:00:00 | 2023-08-01 15:30:00 | NULL       | utf8mb4_general_ci |     NULL |                |         |\n+-----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+\n1 row in set (0.00 sec)\n```\n\n### 7. `SHOW PROCESSLIST`\n\n**功能**：显示当前运行的线程/连接\n\n```sql\nSHOW PROCESSLIST;\n```\n\n**输出示例**：\n\n```\n+----+-----------------+-----------------+-----------+---------+------+------------------------+------------------+\n| Id | User            | Host            | db        | Command | Time | State                  | Info             |\n+----+-----------------+-----------------+-----------+---------+------+------------------------+------------------+\n|  5 | event_scheduler | localhost       | NULL      | Daemon  | 1234 | Waiting on empty queue | NULL             |\n|  8 | root            | localhost:12345 | company_db| Query   |    0 | starting               | SHOW PROCESSLIST |\n|  9 | app_user        | 192.168.1.100:54321 | company_db| Execute |    5 | Sending data           | SELECT * FROM ...|\n+----+-----------------+-----------------+-----------+---------+------+------------------------+------------------+\n3 rows in set (0.00 sec)\n```\n\n### 8. `SHOW VARIABLES`\n\n**功能**：显示系统变量\n\n```sql\n-- 查看所有变量\nSHOW VARIABLES;\n\n-- 查看特定变量\nSHOW VARIABLES LIKE 'innodb_buffer_pool_size';\n```\n\n**输出示例**：\n\n```\n+-------------------------+----------------+\n| Variable_name           | Value          |\n+-------------------------+----------------+\n| innodb_buffer_pool_size | 134217728      |\n| ...                     | ...            |\n+-------------------------+----------------+\n```\n\n### 9. `SHOW STATUS`\n\n**功能**：显示服务器状态变量\n\n```sql\n-- 查看所有状态\nSHOW STATUS;\n\n-- 查看连接相关状态\nSHOW STATUS LIKE 'Threads_%';\n```\n\n**输出示例**：\n\n```\n+-------------------------+-------+\n| Variable_name           | Value |\n+-------------------------+-------+\n| Threads_cached          | 5     |\n| Threads_connected       | 3     |\n| Threads_created         | 10    |\n| Threads_running         | 1     |\n+-------------------------+-------+\n```\n\n### 10. `SHOW GRANTS`\n\n**功能**：显示用户权限\n\n```sql\n-- 当前用户权限\nSHOW GRANTS;\n\n-- 特定用户权限\nSHOW GRANTS FOR 'app_user'@'localhost';\n```\n\n**输出示例**：\n\n```\n+---------------------------------------------------------------------------------+\n| Grants for app_user@localhost                                                   |\n+---------------------------------------------------------------------------------+\n| GRANT USAGE ON *.* TO `app_user`@`localhost`                                    |\n| GRANT SELECT, INSERT, UPDATE ON `company_db`.* TO `app_user`@`localhost`        |\n+---------------------------------------------------------------------------------+\n```\n\n## 高级用法\n\n### 1. 结合 `INFORMATION_SCHEMA`\n\n`SHOW` 命令的替代方案，提供更灵活的查询：\n\n```sql\n-- 等效于 SHOW DATABASES\nSELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA;\n\n-- 等效于 SHOW TABLES\nSELECT TABLE_NAME \nFROM INFORMATION_SCHEMA.TABLES \nWHERE TABLE_SCHEMA = 'company_db';\n\n-- 等效于 SHOW COLUMNS\nSELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT\nFROM INFORMATION_SCHEMA.COLUMNS \nWHERE TABLE_NAME = 'employees';\n```\n\n### 2. 过滤输出\n\n使用 `LIKE` 和 `WHERE` 过滤结果：\n\n```sql\n-- 显示名称包含 'emp' 的表\nSHOW TABLES LIKE '%emp%';\n\n-- 显示特定引擎的表状态\nSHOW TABLE STATUS WHERE Engine = 'InnoDB';\n```\n\n### 3. 查看复制状态\n\n```sql\n-- 主库状态\nSHOW MASTER STATUS;\n\n-- 从库状态\nSHOW SLAVE STATUS\\G  -- \\G 表示垂直显示结果\n\n-- 查看二进制日志\nSHOW BINARY LOGS;\n```\n\n### 4. 查看存储引擎\n\n```sql\n-- 查看支持的存储引擎\nSHOW ENGINES;\n\n-- 查看 InnoDB 引擎状态\nSHOW ENGINE INNODB STATUS;\n```\n\n## 使用技巧\n\n1. **垂直格式显示**：在命令后添加 `\\G` 替代 `;`，以垂直格式显示结果\n\n   ```sql\n   SHOW SLAVE STATUS\\G\n   ```\n\n2. **查看命令帮助**：在 MySQL 命令行中使用 `HELP SHOW` 获取完整帮助\n\n3. **结合 `LIMIT`**：限制 `SHOW` 命令输出行数\n\n   ```sql\n   SHOW VARIABLES LIMIT 10;\n   ```\n\n4. **保存到文件**：将结果导出到文件\n\n   ```sql\n   -- 在命令行中\n   mysql -e \"SHOW VARIABLES\" > variables.txt\n   \n   -- 在 MySQL 中\n   SHOW VARIABLES INTO OUTFILE '/tmp/variables.csv'\n   FIELDS TERMINATED BY ',' ENCLOSED BY '\"'\n   LINES TERMINATED BY '\\n';\n   ```\n\n5. **性能考虑**：某些 `SHOW` 命令（如 `SHOW TABLE STATUS`）可能对性能有影响，避免在高负载时使用\n\n## 注意事项\n\n1. **权限要求**：不同 `SHOW` 命令需要不同权限\n   - `SHOW DATABASES`：需要 `SHOW DATABASES` 权限\n   - `SHOW PROCESSLIST`：需要 `PROCESS` 权限\n   - `SHOW GRANTS`：需要 `SELECT` 权限在 `mysql` 数据库\n\n2. **结果准确性**：\n   - `SHOW TABLE STATUS` 的行数（Rows）是估计值\n   - 对于 InnoDB 表，使用 `SELECT COUNT(*)` 获取精确行数\n\n3. **版本差异**：不同 MySQL 版本 `SHOW` 命令输出可能略有不同\n\n`SHOW` 命令是 MySQL 数据库管理的核心工具，掌握这些命令能极大提升数据库管理效率。对于更复杂的元数据查询，建议结合 `INFORMATION_SCHEMA` 数据库使用 SQL 查询，以获得更灵活的结果处理和过滤能力。","categoryId":4,"viewCount":728,"categoryName":"MySQL","author":"球接子","authorAvatar":null,"tagIds":[2,17],"tagNames":["MySQL","数据库"]}}