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;执行结果大致长这样FieldTypeNullKeyDefaultExtraidbigint unsignedNOPRINULLauto_incrementusernamevarchar(64)NOUNINULLemailvarchar(128)YESNULLcreated_attimestampYESCURRENT_TIMESTAMPDEFAULT_GENERATED这个结果看似一目了然但注意几个细节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) ) ENGINEInnoDB AUTO_INCREMENT10001 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci这个结果的价值在于你能看到索引的完整定义包括复合索引的字段顺序比如KEY idx_a_b (a, b)和KEY idx_b_a (b, a)的效果完全不同只有在这里才能看清。你能看到AUTO_INCREMENT的当前值它直接影响后续插入数据时主键从几开始。你能看到字符集和排序规则跨库对比时两个库同名表的字符集是否一致光靠 DESC 根本看不出来。所以如果你要“完整了解一张表的结构”SHOW CREATE TABLE是首选。2.3 SHOW COLUMNSDESC 的全写没什么区别但可加 LIKESHOW 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 / DESCRIBE低高表格形式快速查看字段名、类型、是否为空SHOW COLUMNS低高表格形式支持 LIKE快速筛选字段是否存在SHOW CREATE TABLE高完整建表语句中需 \G 辅助阅读查看索引、字符集、自增值、约束我的习惯是日常开发用 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;结果里你会看到类似这样的内容INDEX_NAMESEQ_IN_INDEXCOLUMN_NAMENON_UNIQUECARDINALITYCOLLATIONPRIMARY1id010000Auk_username1username010000Aidx_email_name1email19000Aidx_email_name2name19500A这个查询比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_formatInnoDB 表的行格式Dynamic / Compact 等。检查是否因为某些长字段导致行格式变化有时会影响存储效率和性能。5.2 SHOW INDEX查看索引全貌的另一种方式SHOW INDEX FROM user_info;会返回一张包含所有索引列的表格信息很丰富Key_nameSeq_in_indexColumn_nameCollationCardinalitySub_partPackedNullIndex_typeCommentIndex_commentPRIMARY1idA10000NULLNULLBTREEuk_username1usernameA10000NULLNULLBTREEidx_email_name1emailA9000NULLNULLYESBTREEidx_email_name2nameA9500NULLNULLYESBTREE重点看这几列Seq_in_index列在索引中的序号。复合索引中这个值从 1 递增。如果两个索引的Column_name相同但Seq_in_index不同说明它们是不同顺序的复合索引查询优化时的效率可能完全不同。Sub_part前缀索引长度。如果这个值不是 NULL说明该索引只取了字段的前 N 个字符比如(10)表示前 10 个字符作为索引。前缀索引可以节省空间但对排序和精确匹配会有影响。Index_typeBTREE 是 InnoDB 里最常见的。如果出现 FULLTEXT 或 HASH说明是全文索引或哈希索引MyISAM 或 MEMORY 表会有。Cardinality索引基数表示索引列有多少个不同的值。这个值相对表行数越大说明索引区分度越高优化器更倾向于使用这个索引。但前面已经提过这是采样估算不精确。SHOW INDEX和information_schema.STATISTICS返回的信息基本一致只是展示形式不同。终端里快速看一眼用前者写脚本逻辑处理数据用后者。5.3 我的排查习惯先 SHOW TABLE STATUS再 SHOW INDEX最后 DESC我自己的操作顺序通常是这样的先SHOW TABLE STATUS FROM db LIKE table\G快速了解表的存储引擎、行数估算、自增值、数据大小。再SHOW INDEX FROM table;看索引设计是否合理有没有重复索引、前缀索引、基数过低的索引。然后DESC table;或者SHOW CREATE TABLE table\G看具体字段定义和约束。如果需要跨表或跨库操作直接上 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 doesnt 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 常见问题速查表问题排查思路推荐命令无法确认表的索引顺序查看复合索引字段顺序SHOW INDEX / STATISTICS字段是否允许为空不清楚检查 Null 列DESC / COLUMNS表的自增值在哪儿看查看 Auto_increment 字段SHOW TABLE STATUS跨库找相同字段名的表过滤 COLUMNS 表information_schema.COLUMNSJOIN 时提示排序规则冲突对比两边字段 collationinformation_schema.COLUMNS确认是否使用前缀索引查看 Sub_part 字段SHOW INDEX确认表里有没有外键过滤 KEY_COLUMN_USAGEinformation_schema数据字典自动化生成查询 COLUMNS 并导出information_schema.COLUMNS找不到刚建的表检查是否临时表或视图SHOW CREATE VIEW7. 一套自用的“快速查询表结构”脚本模板平时我很喜欢在终端里保存一组 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就能批量查看不用一条条手动敲。省下来的时间足够你多看一遍执行计划了。