在 MySQL 8 中,查看表结构不仅是确认字段名称和类型,还包括检查默认值、索引、主外键、字符集、存储引擎以及完整建表语句。开发调试、数据库迁移和故障排查时,掌握这些命令可以快速还原一张表的真实定义。
一、准备示例数据表
下面创建一张员工表,后续示例都以它为基础:
CREATE TABLE employee (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '员工ID',
department_id BIGINT UNSIGNED DEFAULT NULL COMMENT '部门ID',
name VARCHAR(50) NOT NULL COMMENT '姓名',
email VARCHAR(120) NOT NULL COMMENT '邮箱',
salary DECIMAL(10, 2) NOT NULL DEFAULT 0.00 COMMENT '薪资',
status TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '状态',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uk_email (email),
KEY idx_department_status (department_id, status)
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_0900_ai_ci
COMMENT='员工表';
二、使用 DESCRIBE 查看基本结构
DESCRIBE 可以简写为 DESC,用于快速查看字段、类型、是否允许 NULL、索引标记、默认值和附加属性。
DESCRIBE employee;
-- 或者
DESC employee;
| 返回列 | 含义 |
|---|---|
Field | 字段名称 |
Type | 字段类型、长度及 unsigned 等属性 |
Null | 是否允许保存 NULL |
Key | PRI 表示主键,UNI 表示唯一索引,MUL 表示普通或联合索引 |
Default | 字段默认值 |
Extra | 自增、生成列、自动更新时间等附加信息 |
需要注意,Key=MUL 不代表存在重复索引,而是表示该字段位于一个允许重复值的索引中。对于联合索引,DESC 也无法完整展示字段顺序,因此还需要使用 SHOW INDEX。
三、只查看某一个字段
表字段较多时,可以在 DESCRIBE 后指定字段名,也可以使用通配模式:
DESC employee email;
DESC employee 'create%';
该方式适合快速确认字段类型,但如果需要获得排序规则、权限和注释等信息,应使用 SHOW FULL COLUMNS。
四、使用 SHOW COLUMNS 查看字段
SHOW COLUMNS FROM employee;
SHOW COLUMNS FROM employee FROM your_database;
SHOW COLUMNS FROM employee LIKE 'email';
SHOW COLUMNS 的基础结果与 DESCRIBE 接近。加上 FULL 后,可以额外查看字段排序规则、权限和注释:
SHOW FULL COLUMNS FROM employee;
排查中文排序、大小写比较或字段备注时,FULL 结果更实用。
五、使用 SHOW CREATE TABLE 查看完整定义
SHOW CREATE TABLE 会返回 MySQL 最终保存的完整建表语句,是确认表结构最可靠、最常用的方式之一。
SHOW CREATE TABLE employee;
在 MySQL 命令行中,可以使用 \G 将结果纵向显示,阅读长建表语句更方便:
SHOW CREATE TABLE employee\G
输出内容通常包括:
- 所有字段的准确类型、默认值和注释;
- 主键、唯一索引和普通索引;
- 外键约束与相关规则;
- 存储引擎、字符集、排序规则和表注释;
- MySQL 自动补充或规范化后的定义。
如果要迁移表结构或比较两个环境的差异,应优先保存 SHOW CREATE TABLE 的结果,而不是只复制 DESC 输出。
六、使用 SHOW INDEX 查看索引结构
SHOW INDEX FROM employee;
-- 也可以写成
SHOW KEYS FROM employee;
| 返回列 | 说明 |
|---|---|
Key_name | 索引名称,PRIMARY 表示主键 |
Non_unique | 0 表示唯一索引,1 表示允许重复 |
Seq_in_index | 字段在联合索引中的顺序 |
Column_name | 索引字段名称 |
Cardinality | 优化器估算的不同值数量 |
Index_type | 索引类型,例如 BTREE 或 FULLTEXT |
判断联合索引是否能匹配查询时,需要重点查看 Key_name 和 Seq_in_index,DESC 中的 PRI、UNI、MUL 无法代替完整索引信息。
七、查看表状态和存储信息
SHOW TABLE STATUS LIKE 'employee'\G
该命令可以查看存储引擎、估算行数、平均行长度、数据和索引空间、自动增长值、字符集以及更新时间等信息。InnoDB 的 Rows 通常是估算值,不能把它当作精确的 COUNT(*) 结果。
查看数据库中全部表的简要列表,可以使用:
SHOW TABLES;
SHOW FULL TABLES;
SHOW FULL TABLES 还会标明对象是普通表还是视图。
八、通过 information_schema 查询表结构
SHOW 命令适合人工查看;需要批量检查、编写脚本或生成文档时,查询 information_schema 更灵活。
1. 查询字段信息
SELECT
ordinal_position,
column_name,
column_type,
is_nullable,
column_default,
column_key,
extra,
column_comment
FROM information_schema.columns
WHERE table_schema = DATABASE()
AND table_name = 'employee'
ORDER BY ordinal_position;
2. 查询表信息
SELECT
table_name,
engine,
table_rows,
table_collation,
data_length,
index_length,
table_comment
FROM information_schema.tables
WHERE table_schema = DATABASE()
AND table_name = 'employee';
3. 查询索引信息
SELECT
index_name,
non_unique,
seq_in_index,
column_name,
index_type
FROM information_schema.statistics
WHERE table_schema = DATABASE()
AND table_name = 'employee'
ORDER BY index_name, seq_in_index;
九、查看主键、外键和约束
只看字段列表容易遗漏约束。可以从 information_schema 中查询表的约束类型:
SELECT
constraint_name,
constraint_type
FROM information_schema.table_constraints
WHERE table_schema = DATABASE()
AND table_name = 'employee';
查看外键字段、引用表以及更新和删除规则:
SELECT
kcu.constraint_name,
kcu.column_name,
kcu.referenced_table_name,
kcu.referenced_column_name,
rc.update_rule,
rc.delete_rule
FROM information_schema.key_column_usage AS kcu
JOIN information_schema.referential_constraints AS rc
ON rc.constraint_schema = kcu.constraint_schema
AND rc.constraint_name = kcu.constraint_name
WHERE kcu.table_schema = DATABASE()
AND kcu.table_name = 'employee'
AND kcu.referenced_table_name IS NOT NULL;
十、查看视图结构
视图可以使用 DESC 查看输出列,但要查看创建视图的原始定义,应执行:
SHOW CREATE VIEW employee_view\G
也可以从 information_schema.views 中批量读取视图定义:
SELECT table_name, view_definition
FROM information_schema.views
WHERE table_schema = DATABASE();
十一、常用场景应该选择哪个命令
| 使用场景 | 推荐命令 |
|---|---|
| 快速查看字段类型和默认值 | DESC table_name |
| 查看字段注释和排序规则 | SHOW FULL COLUMNS |
| 复制或核对完整表结构 | SHOW CREATE TABLE |
| 分析联合索引字段顺序 | SHOW INDEX |
| 查看引擎、空间和表状态 | SHOW TABLE STATUS |
| 批量生成结构文档 | 查询 information_schema |
十二、常见问题与注意事项
1. 为什么 DESC 结果与原始 SQL 不完全相同?
DESC 是字段结构摘要,不会展示完整索引、外键、表选项和注释。MySQL 还可能规范化用户提交的定义,因此应以 SHOW CREATE TABLE 的结果为准。
2. 为什么看不到某张表?
先确认当前数据库:
SELECT DATABASE();
USE your_database;
SHOW TABLES LIKE 'employee';
同时检查表名大小写、账号权限以及对象是否实际位于其他数据库。
3. information_schema 查询不到结果怎么办?
检查 table_schema 和 table_name 条件是否正确,并确认当前账号具有相应对象的访问权限。
十三、总结
查看 MySQL 8 表结构时,可以把 DESC 作为快速入口,把 SHOW CREATE TABLE 作为完整定义依据,再用 SHOW INDEX、SHOW TABLE STATUS 和 information_schema 补充索引、存储和约束信息。根据排查目标选择合适的命令,能够避免只看字段列表而遗漏关键配置。