MySQL表结构查询全攻略：从DESC到information_schema的进阶实践

1. 从一条 SQL 聊起：为什么“看表结构”这件事值得写一篇长文

先问个问题：你在 MySQL 里想知道一张表长什么样，第一反应是敲什么命令？我猜八成是DESC或者SHOW CREATE TABLE。这两个确实最常用，但如果你只停留在“会用这两个命令”的层面，那当表字段越来越多、索引越来越复杂、团队协作出现争议的时候，你会明显感觉信息不够用。

我最近帮一个朋友排查线上问题，他给我发来一张截图，表里有 60 多个字段，他问“这个表到底哪些字段允许为空”，我说你用SHOW CREATE TABLE看啊，他说“看了，但太长了，眼睛看花”。这就是典型的“会看”但“没看透”。后来我给他推荐了information_schema的查询方式，他看完直呼“原来还有这种操作”。

这篇文章我不打算念文档。MySQL 官方手册里关于表结构的内容其实很分散，你要么查SHOW系列，要么查information_schema，要么查DESCRIBE，很少有人系统地把它们串起来讲清楚。这篇文章就是把“查表结构”这件事从入门到进阶、从单条命令到整套思路全部拆开，顺便把我这些年踩过的坑、积累的习惯都写进去。

无论你是刚接触 MySQL 的新手，还是已经写了好几年 SQL 的开发者，这篇文章都值得花十分钟看完。因为“看表结构”看起来是个极小的操作，但它背后涉及的是你对整个数据库 schema 的理解能力，是排查问题、优化查询、设计表结构的基本功。

2. 基本功：DESC、SHOW CREATE TABLE 和 SHOW COLUMNS 的区别与选择

2.1 DESC：短平快，但信息有限

DESC是DESCRIBE的简写，也是大多数人入门的第一个命令。

DESC user_info;

执行结果大致长这样：

这个结果看似一目了然，但注意几个细节：

• Null这一列显示的是NO，表示该字段不允许为 NULL，并不是说这个字段不能有 NULL 值。这个表达有歧义，很多人初学时会搞混。
• Key列看到PRI说明是主键，UNI说明有唯一索引，但如果一张表有多个索引，DESC 只会展示每个字段命中的“最显眼”的索引标识，并不会把复合索引的完整信息列出来。
• Extra列会显示auto_increment、DEFAULT_GENERATED、on update CURRENT_TIMESTAMP等信息，但不完整。

DESC 适合什么场景？适合你手头只有终端、不想看太多输出、只想知道字段名和类型的时候。但如果你要判断“这个字段在哪个索引里，索引顺序是什么”，DESC 就帮不上忙了。

2.2 SHOW CREATE TABLE：最完整的建表还原

SHOW CREATE TABLE输出的是 MySQL 内部存储的完整建表语句，它包含了所有细节：字段、索引、约束、字符集、排序规则、存储引擎、自增值、分区信息等。

SHOW CREATE TABLE user_info\G

注意\G这个结尾，在终端里它会把结果从表格形式改成键值对形式，看起来舒服很多，尤其是字段多、语句长的时候。不加\G的话，一行字符串可能长到屏幕都装不下。

输出里你会看到类似这样的内容：

CREATE TABLE `user_info` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `username` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `email` varchar(128) DEFAULT NULL, `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `uk_username` (`username`), KEY `idx_email` (`email`) ) ENGINE=InnoDB AUTO_INCREMENT=10001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

这个结果的价值在于：

• 你能看到索引的完整定义，包括复合索引的字段顺序，比如KEY idx_a_b (a, b)和KEY idx_b_a (b, a)的效果完全不同，只有在这里才能看清。
• 你能看到AUTO_INCREMENT的当前值，它直接影响后续插入数据时主键从几开始。
• 你能看到字符集和排序规则，跨库对比时，两个库同名表的字符集是否一致，光靠 DESC 根本看不出来。

所以如果你要“完整了解一张表的结构”，SHOW CREATE TABLE是首选。

2.3 SHOW COLUMNS：DESC 的全写，没什么区别但可加 LIKE

SHOW COLUMNS FROM user_info;和DESC user_info;的执行结果几乎完全一样。那为什么还要单列出来？因为它支持LIKE通配符。

SHOW COLUMNS FROM user_info LIKE '%name%';

这个命令会返回所有字段名中含name的字段信息。当一张表有上百个字段时，你想快速确认某个字段是否存在的场景下，这个比DESC之后人眼搜索强得多。

不过老实说，SHOW COLUMNS LIKE用得不算多。更多时候我会用它来确认“某张表是否有某列”，比起SELECT * FROM information_schema.columns WHERE ...来说更快捷，但灵活性略逊一筹。

2.4 三者对比：什么时候该用谁

我的习惯是：日常开发用 DESC 瞄一眼，涉及索引设计或表结构变更时用 SHOW CREATE TABLE，涉及字段筛选时用 SHOW COLUMNS LIKE。三个命令各有分工，没有哪个是“全能王”。

3. 进阶：information_schema —— 一张“数据库的数据库”

3.1 为什么你需要 information_schema

如果你只是管理三五张表，DESC和SHOW CREATE TABLE完全够用。但是当数据库实例里有几百张表、表分散在几十个库中，你想问出“哪个库有字段名为 deleted 的表”这种跨库问题，命令行就力不从心了。

information_schema是 MySQL 自带的一个系统数据库，里面存储的是关于数据库本身的元数据（metadata）。也就是说，它是“关于数据库的数据库”。在这个库里，你可以像查普通表一样用 SQL 查询所有库、所有表、所有字段的元信息。

3.2 COLUMNS 表：字段级信息大全

information_schema.COLUMNS是平时用得最多的一张元数据表。每一行代表一张表中的一个字段。

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, EXTRA, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'your_database' AND TABLE_NAME = 'user_info' ORDER BY ORDINAL_POSITION;

这个查询返回的内容，和 DESC 相比多出了COLLATION_NAME、CHARACTER_SET_NAME、COLUMN_COMMENT等元信息。很多公司的数据字典工具（如某些开源数据库文档生成器）就是通过查询这张表自动生成字段注释文档的。

另一个典型用法：找出所有具有某个字段特征的表。

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM information_schema.COLUMNS WHERE COLUMN_NAME = 'created_at';

这条 SQL 能把整个实例里所有含有created_at字段的表全部列出来，这在做跨表统计、数据治理、批量表结构调整时价值极大。

3.3 TABLES 表：表和存储引擎的元信息

information_schema.TABLES提供了表级别的信息：有多少行（TABLE_ROWS）、平均行长度（AVG_ROW_LENGTH）、数据大小（DATA_LENGTH）、索引大小（INDEX_LENGTH）、创建时间、更新时间、表类型、字符集等。

SELECT TABLE_NAME, ENGINE, TABLE_ROWS, DATA_LENGTH / 1024 / 1024 AS data_mb, INDEX_LENGTH / 1024 / 1024 AS index_mb FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_database' ORDER BY DATA_LENGTH DESC;

这条 SQL 可以一眼看出哪些表占据了大量磁盘空间。做容量规划、性能调优时，这比在终端一条条执行SHOW TABLE STATUS高效得多。

需要注意，TABLE_ROWS对 InnoDB 来说只是一个估算值，并不精确。如果你需要精确行数，还是得SELECT COUNT(*)。但对于快速判断表的大小量级，这个值已经够用了。

3.4 STATISTICS 表：索引元数据的完整视图

information_schema.STATISTICS是查看索引细节的关键表。它记录了每个索引的名称、字段顺序、是否唯一、基数（CARDINALITY）等信息。

SELECT INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, NON_UNIQUE, CARDINALITY, COLLATION FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = 'your_database' AND TABLE_NAME = 'user_info' ORDER BY INDEX_NAME, SEQ_IN_INDEX;

结果里你会看到类似这样的内容：

这个查询比SHOW INDEX FROM user_info;好在哪？它可以叠加 WHERE 条件和 SQL 聚合。比如你想找出所有表中“冗余索引”（多个索引以同一列开头），光靠眼睛盯着一个个表看是看不完的，但用 SQL 就能自动化定位。

SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(INDEX_NAME ORDER BY SEQ_IN_INDEX SEPARATOR ', ') AS index_groups FROM information_schema.STATISTICS WHERE NON_UNIQUE = 1 GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME HAVING COUNT(*) > 1;

不过这里要提醒：CARDINALITY这个值是采样估计的，不精确，别拿它当精确统计用。它更大的作用是“大致判断”索引的区分度。

3.5 KEY_COLUMN_USAGE 表：约束与外键的追踪利器

information_schema.KEY_COLUMN_USAGE记录的是索引列、主键列、唯一约束列、外键约束列的使用情况。如果你要检查一张表有哪些外键，关联到哪张表的哪个字段，这张表就是最直接的来源。

SELECT CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = 'your_database' AND REFERENCED_TABLE_NAME IS NOT NULL;

输出结果会展示所有带外键约束的关联关系。注意，如果某个索引在多个约束中被引用，CONSTRAINT_NAME会重复出现，这并不代表数据有问题，只是说明该列参与了多个约束。

很多人在设计表时不建议用物理外键，而倾向于在应用层维护逻辑外键。如果你接手一个老项目，想知道里面到底有没有物理外键、有多少，这个查询能直接告诉你答案。

3.6 information_schema 的注意事项

information_schema是系统库，默认只有只读权限，你不能改里面的数据。另外，在某些 MySQL 版本或云数据库实例中，查询information_schema可能会有性能问题——比如TABLE_ROWS对超大表进行统计时，全库扫描会消耗资源。生产环境谨慎执行不带过滤条件的大范围查询，尤其不要在业务高峰期跑类似SELECT * FROM information_schema.COLUMNS这种全量查询。

4. 实战：组合使用元数据查询解决真实业务问题

4.1 场景一：找出一张表里所有“没有注释的字段”

很多团队要求字段必须有注释，但历史表往往不达标。用information_schema.COLUMNS可以直接把缺注释的字段捞出来：

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLUMN_TYPE FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'your_database' AND (COLUMN_COMMENT IS NULL OR COLUMN_COMMENT = '');

拿到结果后，你可以拼出 ALTER 语句来批量补注释。我曾经靠这条 SQL 在一张 80 个字段的旧表里找出了 23 个无注释字段，然后用脚本自动生成 ALTER 语句逐条补上。整个过程省了一个下午的人工排查。

4.2 场景二：对比两个环境（开发库 vs 生产库）的表结构差异

结构漂移是数据库管理里的经典问题：开发环境改了表结构，生产环境忘了同步。手工对比太累，用 information_schema 对比就轻松多了。

思路：把两边的表结构信息分别导出，再通过哈希比对。

-- 开发库 SELECT TABLE_NAME, GROUP_CONCAT( CONCAT(COLUMN_NAME, ':', COLUMN_TYPE) ORDER BY ORDINAL_POSITION SEPARATOR ',' ) AS structure_hash FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'dev_db' GROUP BY TABLE_NAME;

生产库执行同样查询，把两个结果导成文件，用diff就能快速看到差异。这个方法我在多个项目中实测有效，比用图形化工具逐个表对比快得多。

4.3 场景三：定位“全表字段全为空的表”

有时你会遇到一批导入异常的表，需要确认哪些表是空表。information_schema.TABLES的TABLE_ROWS可以帮你快速过滤：

SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'your_database' AND TABLE_ROWS = 0;

但前面说了，TABLE_ROWS是估算值。对 InnoDB 而言，如果表从未执行过 ANALYZE，这个值可能不准。所以这个查询适合“初步筛查”，最后的确认还是得SELECT COUNT(*)。

4.4 场景四：找出一张表中“包含某个字段名但类型不一致”的情况

比如你想找user_no这个字段在不同表中分别是什么类型：

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_TYPE FROM information_schema.COLUMNS WHERE COLUMN_NAME = 'user_no';

如果结果里出现varchar(20)和varchar(32)混在一起，那后续 JOIN 操作就可能出现隐式转换导致索引失效的问题。这种问题在人工 review 时很难发现，但在 information_schema 上一查便知。

4.5 场景五：自动化生成数据字典文档

很多团队需要维护一份数据字典，手写太痛苦。用 information_schema 可以轻松生成 Markdown 格式的表格。

SELECT TABLE_NAME AS '表名', COLUMN_NAME AS '字段名', COLUMN_TYPE AS '类型', IS_NULLABLE AS '是否为空', COLUMN_DEFAULT AS '默认值', COLUMN_COMMENT AS '注释' FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'your_database' ORDER BY TABLE_NAME, ORDINAL_POSITION;

然后写个脚本把结果输出为 Markdown 即可。我见过有人把这条 SQL 跑成定时任务，每天自动生成一个数据字典文件发到团队群，大家再也不用追着开发问“这个字段啥意思”。

5. 命令行也有神器：SHOW TABLE STATUS 与 SHOW INDEX 的隐藏价值

5.1 SHOW TABLE STATUS 能告诉你什么

SHOW TABLE STATUS返回的是表级别的状态信息，类似information_schema.TABLES的内容，但它是命令行的原生方式。

SHOW TABLE STATUS FROM your_database LIKE 'user_info'\G

输出里包含Engine、Row_format、Rows、Avg_row_length、Data_length、Max_data_length、Index_length、Auto_increment、Create_time、Update_time、Collation等。

几个容易被忽略的字段：

• Auto_increment：当前自增计数器已经到哪了。如果你发现这个值异常大，比如一张表只有几百条数据但Auto_increment已经到几百万，那可能是之前有大事务回滚过，或者频繁的删除插入操作导致自增值快速跳跃。
• Update_time：在 InnoDB 下，这个值如果不为 NULL，表示最近一次数据更新的大致时间。但它只有在表被更新过且 innodb_stats 刷新时才会变，不要依赖它做精确判断。
• Row_format：InnoDB 表的行格式（Dynamic / Compact 等）。检查是否因为某些长字段导致行格式变化，有时会影响存储效率和性能。

5.2 SHOW INDEX：查看索引全貌的另一种方式

SHOW INDEX FROM user_info;会返回一张包含所有索引列的表格，信息很丰富：

重点看这几列：

• Seq_in_index：列在索引中的序号。复合索引中这个值从 1 递增。如果两个索引的Column_name相同但Seq_in_index不同，说明它们是不同顺序的复合索引，查询优化时的效率可能完全不同。
• Sub_part：前缀索引长度。如果这个值不是 NULL，说明该索引只取了字段的前 N 个字符，比如(10)表示前 10 个字符作为索引。前缀索引可以节省空间，但对排序和精确匹配会有影响。
• Index_type：BTREE 是 InnoDB 里最常见的。如果出现 FULLTEXT 或 HASH，说明是全文索引或哈希索引（MyISAM 或 MEMORY 表会有）。
• Cardinality：索引基数，表示索引列有多少个不同的值。这个值相对表行数越大，说明索引区分度越高，优化器更倾向于使用这个索引。但前面已经提过，这是采样估算，不精确。

SHOW INDEX和information_schema.STATISTICS返回的信息基本一致，只是展示形式不同。终端里快速看一眼用前者，写脚本逻辑处理数据用后者。

5.3 我的排查习惯：先 SHOW TABLE STATUS，再 SHOW INDEX，最后 DESC

我自己的操作顺序通常是这样的：

1. 先SHOW TABLE STATUS FROM db LIKE 'table'\G，快速了解表的存储引擎、行数估算、自增值、数据大小。
2. 再SHOW INDEX FROM table;，看索引设计是否合理，有没有重复索引、前缀索引、基数过低的索引。
3. 然后DESC table;或者SHOW CREATE TABLE table\G，看具体字段定义和约束。
4. 如果需要跨表或跨库操作，直接上 information_schema 写 SQL。

这个顺序的核心逻辑是从宏观到微观：先看表级别的“体检报告”，再看索引这个“关键器官”，最后才落到字段这个“细胞层”。如果你一开始就 DESC，很可能陷入字段细节里，忘了整体看索引设计、存储参数这类同样重要的信息。

6. 容易被忽略的坑与经验总结

6.1 字符集和排序规则不一致的坑

两个库的表 JOIN 时，如果字段字符集不同，MySQL 可能会报错 “Illegal mix of collations”，或者虽然不报错但隐式转换导致索引失效。DESC看不到字符集信息，SHOW CREATE TABLE能看到字段级别的CHARACTER SET设置。因此，一旦你的 SQL 出现 JOIN 后性能异常，先查两边表的字段字符集。

检查字段字符集的快捷方式：

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA IN ('dev_db', 'prod_db') AND TABLE_NAME IN ('user_info');

6.2 临时表在 SHOW CREATE TABLE 时看不到

如果你在会话里建了临时表（CREATE TEMPORARY TABLE），然后用连接工具去SHOW CREATE TABLE，可能会查不到。因为临时表只在当前会话可见，元数据也不写入持久化的 schema。这时候你需要确认自己连接的是不是正确会话，或者直接检查information_schema.INNODB_TEMP_TABLE_INFO（仅 InnoDB 临时表）。这个细节经常在排查“我明明建了表，怎么查不到”时用到。

6.3 视图和表的 SHOW CREATE 输出不同

SHOW CREATE VIEW不能和SHOW CREATE TABLE混用。MySQL 中视图的元数据存储在information_schema.VIEWS表中。如果你用SHOW CREATE TABLE查看一张视图，结果会看到类似“Table 'xxx' doesn't exist”的错误（具体报错取决于版本）。遇到这种报错先别慌，用SHOW CREATE VIEW view_name;即可。

6.4 用 \G 还是用分号

终端中查看SHOW CREATE TABLE或SHOW TABLE STATUS，输出宽度经常超出一屏，推荐用\G代替分号，以键值对方式展示结果。这个技巧虽然基础，但确实能显著提升阅读体验。注意\G不能和;同时用，二者选其一。

6.5 权限问题和线上环境的自我保护

不是所有数据库账号都对information_schema有查询权限。某些云数据库实例中，连information_schema.COLUMNS都会被限制。生产环境执行元数据查询前，先确认当前账号的权限范围。另外，不要在业务高峰期执行全库范围的information_schema查询，尤其不要不加 WHERE 条件。元数据表本身也是读磁盘、占 CPU 的。

6.6 常见问题速查表

7. 一套自用的“快速查询表结构”脚本模板

平时我很喜欢在终端里保存一组 SQL 模板，需要时直接调用。这里分享一套常用的，属于“复制即用”的类型。

查看表的所有字段和注释：

SELECT ORDINAL_POSITION AS '序号', COLUMN_NAME AS '字段名', COLUMN_TYPE AS '类型', IS_NULLABLE AS '可空', COLUMN_DEFAULT AS '默认值', EXTRA AS '额外信息', COLUMN_COMMENT AS '注释' FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '你的库名' AND TABLE_NAME = '你的表名' ORDER BY ORDINAL_POSITION;

查看表的索引全貌：

SELECT INDEX_NAME AS '索引名', SEQ_IN_INDEX AS '序号', COLUMN_NAME AS '字段', NON_UNIQUE AS '是否唯一', SUB_PART AS '前缀长度', CARDINALITY AS '基数' FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = '你的库名' AND TABLE_NAME = '你的表名' ORDER BY INDEX_NAME, SEQ_IN_INDEX;

一键生成 Markdown 格式数据字典（适合直接贴到文档）：

SELECT CONCAT('| ', COLUMN_NAME, ' | ', COLUMN_TYPE, ' | ', IF(IS_NULLABLE = 'NO', 'NOT NULL', 'NULL'), ' | ', IFNULL(COLUMN_DEFAULT, ''), ' | ', IFNULL(COLUMN_COMMENT, ''), ' |') AS markdown_row FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '你的库名' AND TABLE_NAME = '你的表名' ORDER BY ORDINAL_POSITION;

输出结果可以直接拼成 Markdown 表格行，省去手写表格的功夫。我每次给团队更新文档时都会用这条 SQL，效率翻倍。

最后再说一个小技巧：如果你在本地用 Docker 跑了一个 MySQL，把上面这些 SQL 存到一个.sql文件里，每次进容器执行mysql -e "source /path/to/your_file.sql"就能批量查看，不用一条条手动敲。省下来的时间，足够你多看一遍执行计划了。