前阵子接了一个老项目接手第一件事我没去看业务代码而是先登录 MySQL把全库所有的表结构和列定义整张整张捞了出来。理由很现实业务表几百张建表脚本散落得到处都是纸质文档和实际字段经常对不上多出来的、改过名的、加了注释的列全靠人肉翻根本翻不过来。所谓“全表查询”在这个场景里不是指SELECT *而是指把表名称注释、列注释、字段类型、列是否可为空、缺省值这些元数据从系统的数据字典里一次性查出来形成一张随时可以导出、可以筛选、可以对比的结构总表。这篇就把我惯用的查询方式、踩过的坑和最终沉淀出来的 SQL 写出来给同样需要给数据库做结构盘点的人做个参考。1. 一次捞干净的手段全表结构查询到底在查什么1.1 从一次交接踩坑说起有次我接手一套订单系统文档里写的用户表是 28 个字段实际库里是 32 个多出来的几个还正好是关键状态位。刚接手那几天我靠的是SHOW CREATE TABLE一张表一张表看看到第 40 张的时候就已经眼花缭乱看到第 100 张就彻底放弃了而且这种看法的最大问题是你只能看到一张表没法同时对比多张表的差异。后来想明白一件事MySQL 的所有结构信息其实都躺在information_schema系统库里。其中TABLES表存表级信息表名、引擎、表注释、行数估算什么的都在COLUMNS表存列级信息列名、列注释、字段类型、是否可空、缺省值都在。只要把这两张表连接起来一次就能输出每一个表的表名、表注释、列名、列注释、字段类型、是否可空、缺省值。这个操作就是标题里说的“全表查询”——查的不是某一条业务数据而是整库的结构字典。1.2 和 SHOW CREATE TABLE 的区别很多人习惯用SHOW CREATE TABLE看结构因为它输出的是CREATE TABLE语句看起来最接近建表脚本。但它有个明显短板一次只能看一张表而且输出结果是一整段 DDL想拿去做对比、筛选、排序都很麻烦。对比项SHOW CREATE TABLEinformation_schema 查询输出形式还原后的建表 SQL 文本结构化的表格行一次处理表数一张全库任意筛选适合场景复制建表语句、核对单个表批量结构对比、生成数据字典程序处理需要解析 SQL 文本直接读取字段获取列注释看得见但要在文本里翻按列直接返回所以我的习惯是需要单独复制一张表的结构时用SHOW CREATE TABLE要做全库盘点、批量核对、生成文档的时候一律走information_schema。1.3 我习惯先跑两条探路 SQL刚开始我会先跑两条 SQL 确认库名和表规模避免上来就一顿全查。第一条是看有哪些库SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA;第二条是看某个库里有多少表、哪些是视图SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE, TABLE_COMMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA 你的库名 ORDER BY TABLE_TYPE, TABLE_NAME;这两条跑完心里就有底了。后面拼全量结构查询时也会用类似条件去过滤。2. information_schema.COLUMNS抓列注释的主战场2.1 核心列别记串information_schema.COLUMNS表的字段非常多但做结构查询时真正需要关注的核心列十个手指头能数过来TABLE_SCHEMA所在数据库名过滤时最常用。TABLE_NAME所属表名。ORDINAL_POSITION列在表里的顺序号从 1 开始排序靠它。COLUMN_NAME列名。COLUMN_TYPE完整字段类型比如bigint unsigned、varchar(64)、decimal(10,2)。DATA_TYPE基础数据类型比如bigint、varchar、decimal。CHARACTER_MAXIMUM_LENGTH字符型最大长度数字型字段这里是NULL。IS_NULLABLE是否可空显示为YES或空字符串。COLUMN_DEFAULT缺省值没有默认值时是NULL。COLUMN_COMMENT列注释。一个最常见的单表列信息查询长这样SELECT TABLE_NAME, ORDINAL_POSITION, COLUMN_NAME, COLUMN_COMMENT, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM information_schema.COLUMNS WHERE TABLE_SCHEMA 你的库名 AND TABLE_NAME 你的表名 ORDER BY ORDINAL_POSITION;返回结果大概是这样TABLE_NAMEORDINAL_POSITIONCOLUMN_NAMECOLUMN_COMMENTCOLUMN_TYPEIS_NULLABLECOLUMN_DEFAULTuser_profile1id主键bigint unsignedNULLuser_profile2mobile手机号varchar(20)YESNULLuser_profile3status状态0禁用 1启用tinyintYES1注意IS_NULLABLE这一列返回的是字符串YES或者空字符串不是true/false做条件判断时要小心。2.2 表注释从哪里取列注释在COLUMNS.COLUMN_COMMENT表注释则不在COLUMNS里而是在TABLES.TABLE_COMMENT。两表连接的逻辑很简单TABLE_SCHEMA和TABLE_NAME对上就行。如果你只查COLUMNS不连接TABLES输出结果里就看不到表注释。但业务方要的结构字典通常希望每条列记录上带着所属表的注释所以连接是必须的。我第一次漏了这个连接导出的 Excel 被业务方退回来说只有列注释没有表注释完全没法看。2.3 第一版完整 SQL把两张表连接起来就是最基础的全表结构查询SELECT t.TABLE_NAME AS 表名, t.TABLE_COMMENT AS 表注释, c.ORDINAL_POSITION AS 字段序号, c.COLUMN_NAME AS 字段名, c.COLUMN_COMMENT AS 字段注释, c.COLUMN_TYPE AS 字段类型, c.IS_NULLABLE AS 是否为空, c.COLUMN_DEFAULT AS 缺省值 FROM information_schema.TABLES t JOIN information_schema.COLUMNS c ON t.TABLE_SCHEMA c.TABLE_SCHEMA AND t.TABLE_NAME c.TABLE_NAME WHERE t.TABLE_SCHEMA 你的库名 ORDER BY t.TABLE_NAME, c.ORDINAL_POSITION;这个查询跑出来的结果就是一张既带表注释又带列注释的字典表。后面所有高级玩法基本都是在它上面追加字段和条件。3. 字段类型、可空性、缺省值的准确含义和易错点3.1 COLUMN_TYPE 和 DATA_TYPE 不要混用DATA_TYPE返回的是剥离了长度和后缀的基础类型比如int、varcharCOLUMN_TYPE返回的是建表语句里写的完整类型比如int unsigned、varchar(64)。两者的差别在导出文档时非常明显。业务方问“这个字段是啥类型”你给他varchar他还要追问一句“多长”。所以我做数据字典时展示一律用COLUMN_TYPE既包含类型又包含长度和符号属性做程序判断时比如要根据类型决定怎么格式化展示才用DATA_TYPE。另外要注意DATETIME、TIMESTAMP、DATE这类日期类型在CHARACTER_MAXIMUM_LENGTH上都是NULL别指望从这里拿到格式长度。3.2 IS_NULLABLE 的“YES”和空字符串IS_NULLABLE字段只有两种取值YES空字符串。前者表示该列允许 NULL后者表示该列是NOT NULL。有个容易误解的地方一个列没有显式写NULL也没写NOT NULLMySQL 默认是允许 NULL 的所以IS_NULLABLE会返回YES。也就是说你看到YES不一定是建表时写了NULL而是默认行为。另外只要是主键字段IS_NULLABLE一定是空字符串也就是NOT NULL。这一点在建表时可以不写但查出来的结果就是这样不需要觉得奇怪。3.3 COLUMN_DEFAULT 的三种情况COLUMN_DEFAULT是结构查询里最容易看走眼的字段。它有三种情况第一列没有定义默认值此时COLUMN_DEFAULT为NULL。 第二列显式定义了默认值为NULL此时COLUMN_DEFAULT也是NULL。 第三列显式定义了实际默认值比如0、abc、CURRENT_TIMESTAMP此时返回对应的值。这里就有一个经典坑不能因为看到NULL就认为“没有默认值”。对于允许 NULL 的列NULL本身就是一种合法默认值。你要判断的是“建表语句里到底写了 DEFAULT NULL还是压根没写 DEFAULT”只看COLUMN_DEFAULT是区分不出来的必须结合当时的建表 DDL。在 MySQL 8.0 里还有一类特殊默认值叫表达式默认值典型例子是DEFAULT CURRENT_TIMESTAMP以及BLOB/TEXT类型的DEFAULT (某种表达式)。这类默认值出现在COLUMN_DEFAULT时可能是一段表达式文本而不是一个静态值。我遇到过业务方说“这个字段明明有默认值怎么查出来是 NULL”多半就是没看表达式的显示规则。碰到这种我建议直接把该表的SHOW CREATE TABLE拉出来对拍一遍确认默认值到底是静态值还是表达式。3.4 TIMESTAMP 和 DATETIME 的默认值差异很久以前只有TIMESTAMP支持DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMPDATETIME不支持。所以老库里的DATETIME字段很少直接依赖数据库默认时间都是应用层写。新版本 MySQL 已经放宽了限制但不同小版本的行为仍然有差异。做结构查询时要明确一件事COLUMN_DEFAULT给的是数据库里存的元数据文本不代表业务代码里实际写入时用的值。有些字段的默认值逻辑在应用层不在数据库层这属于正常现象别在数据库结构查询里纠结。4. 把注释、类型、可空、默认值拼成一张业务字典表4.1 再加一个“是否主键”的标识前面那个基础版本已经能应付大多数情况但业务方做结构审阅时几乎一定会问“哪些字段是主键”。所以我的字典表里习惯加一列“主键标识”它的来源是information_schema.KEY_COLUMN_USAGE或者STATISTICS。我比较喜欢用STATISTICS因为它不光能告诉你哪个列是主键还能告诉你主键在第几个位置复合主键的顺序也能体现出来。SELECT t.TABLE_NAME AS 表名, t.TABLE_COMMENT AS 表注释, c.ORDINAL_POSITION AS 字段序号, c.COLUMN_NAME AS 字段名, c.COLUMN_COMMENT AS 字段注释, c.COLUMN_TYPE AS 字段类型, IF(c.IS_NULLABLE YES, 是, 否) AS 是否可空, c.COLUMN_DEFAULT AS 缺省值, IF(st.SEQ_IN_INDEX IS NULL, , CONCAT(主键-, st.SEQ_IN_INDEX)) AS 主键标识 FROM information_schema.TABLES t JOIN information_schema.COLUMNS c ON t.TABLE_SCHEMA c.TABLE_SCHEMA AND t.TABLE_NAME c.TABLE_NAME LEFT JOIN information_schema.STATISTICS st ON st.TABLE_SCHEMA c.TABLE_SCHEMA AND st.TABLE_NAME c.TABLE_NAME AND st.COLUMN_NAME c.COLUMN_NAME AND st.INDEX_NAME PRIMARY WHERE t.TABLE_SCHEMA 你的库名 ORDER BY t.TABLE_NAME, c.ORDINAL_POSITION;st.SEQ_IN_INDEX在复合主键里分别返回 1、2、3这样导出的字典能清楚看出联合主键的字段顺序。4.2 把长度和字符集也带上字段类型的“可读性”和“完整性”是两回事。单纯给业务看varchar(255)比varchar有用但如果你负责数据库规范检查最好把字符集和排序规则也带上SELECT c.COLUMN_NAME AS 字段名, c.COLUMN_COMMENT AS 字段注释, c.COLUMN_TYPE AS 字段类型, c.CHARACTER_SET_NAME AS 字符集, c.COLLATION_NAME AS 排序规则, IF(c.IS_NULLABLE YES, 是, 否) AS 是否可空, c.COLUMN_DEFAULT AS 缺省值 FROM information_schema.COLUMNS c WHERE c.TABLE_SCHEMA 你的库名 ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION;数字类型的CHARACTER_SET_NAME是NULL字符类型的NUMERIC_PRECISION是NULL这些都是正常现象。批量生成文档时不要做非空校验否则会误伤。4.3 GROUP_CONCAT 的另类用法有时候不需要逐列清单只需要快速看某个表到底有哪些字段、注释写没写。我会用GROUP_CONCAT把列名和注释各拼成一行SELECT TABLE_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY ORDINAL_POSITION) AS 列名列表, GROUP_CONCAT(COLUMN_COMMENT ORDER BY ORDINAL_POSITION) AS 列注释列表 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA 你的库名 GROUP BY TABLE_NAME;这个结果很适合快速扫一眼。不过要注意GROUP_CONCAT默认最大长度是 1024字段特别多的表可能被截断。遇到报错或者结果明显少了一截先执行下面这行把长度调大再查SET SESSION group_concat_max_len 1048576;5. 跨库过滤、视图混入和排序的隐藏坑5.1 业务库要过滤掉系统库如果直接全量查询information_schema.COLUMNS结果里会混入mysql、information_schema、performance_schema、sys这些系统库。导出的字典表会瞬间多出一堆无用的表业务方根本看不懂。我的做法是两步先用TABLE_SCHEMA明确指定目标库如果确实要查多个库再用IN列表把业务库列出来。不要依赖TABLE_SCHEMA NOT IN (mysql, performance_schema)这种反向排除因为万一以后又冒出别的系统库结果就不干净了。明确列出业务库名是最稳妥的方式。5.2 视图和基表要区分开information_schema.TABLES.TABLE_TYPE字段能区分表类型BASE TABLE是基础表VIEW是视图。视图在COLUMNS里也有列记录能看到列名和类型但有些视图的列注释是空的因为视图本身不强调注释。生成数据字典时我一般默认只保留BASE TABLE。如果业务方明确要连视图一起管理再加TABLE_TYPE条件。不要不加条件直接全量导出否则视图混在表里表注释全是空的容易让看文档的人误以为表设计有缺陷。5.3 ORDER BY 排序可能影响展示顺序ORDINAL_POSITION是列在表内部的物理顺序我排序时一定会带上它。但需要注意TABLE_NAME的排序受库的排序规则影响中文表名和英文表名混在一起时排序结果可能不是你想的那种“字典序”。如果只是生成给人看的字典这个排序问题不大。如果是程序要按表名一致性对比我建议对TABLE_NAME做二进制排序ORDER BY c.TABLE_NAME COLLATE utf8mb4_bin, c.ORDINAL_POSITION;这样排序严格按字节来大小写敏感不容易在对比时出幺蛾子。5.4 列顺序别被COLUMN_KEY误导COLUMNS表里还有一个COLUMN_KEY字段用来表示该列是主键、唯一索引、还是普通索引。但它只能说明“这个列上有什么索引”不能说明索引的完整结构。比如联合索引中某列是第二列COLUMN_KEY可能还是显示MUL但你光靠它看不出来第二列的顺序必须回STATISTICS里看SEQ_IN_INDEX。所以我在做主键标识时才会选择LEFT JOIN STATISTICS而不是直接看COLUMN_KEY。这个细节在导出复杂表结构时特别容易踩雷。6. 把查询结果导出成 Excel 数据字典的两种方式6.1 命令行直接导出 CSV最后一步也是最常被问的一步批量结构查询出来了怎么导出成 Excel最简单的做法是直接在操作系统层面跑 MySQL 客户端命令。mysql -h主机名 -P端口 -u用户名 -p密码 --default-character-setutf8mb4 \ -e SELECT t.TABLE_NAME AS 表名, t.TABLE_COMMENT AS 表注释, c.ORDINAL_POSITION AS 字段序号, c.COLUMN_NAME AS 字段名, c.COLUMN_COMMENT AS 字段注释, c.COLUMN_TYPE AS 字段类型, IF(c.IS_NULLABLEYES,是,否) AS 是否可空, c.COLUMN_DEFAULT AS 缺省值 FROM information_schema.TABLES t JOIN information_schema.COLUMNS c ON t.TABLE_SCHEMAc.TABLE_SCHEMA AND t.TABLE_NAMEc.TABLE_NAME WHERE t.TABLE_SCHEMA你的库名 ORDER BY t.TABLE_NAME, c.ORDINAL_POSITION \ 表结构字典.csv注意一定要加--default-character-setutf8mb4否则中文表注释和列注释导出到 CSV 里很容易变成乱码。另外直接用-e跑复杂 SQL 时SQL 里的双引号要留意操作系统的转义规则所以我更推荐的方式是把 SQL 写进一个.sql文件再用重定向执行mysql -h主机名 -P端口 -u用户名 -p密码 --default-character-setutf8mb4 --database你的库名 查询结构.sql 表结构字典.tsv这样输出的是制表符分隔的文件Excel 打开时选择按制表符拆分即可。6.2 用 Python 直接生成 ExcelCSV 的缺点是如果某个字段里带了逗号、换行符再导入 Excel 时容易错位。更省心的做法是用 Python 把查询结果直接写成 Excel哪怕里面内容再复杂也不会乱。import pymysql import pandas as pd conn pymysql.connect( host主机名, port3306, user用户名, password密码, database你的库名, charsetutf8mb4, ) sql SELECT t.TABLE_NAME AS 表名, t.TABLE_COMMENT AS 表注释, c.ORDINAL_POSITION AS 字段序号, c.COLUMN_NAME AS 字段名, c.COLUMN_COMMENT AS 字段注释, c.COLUMN_TYPE AS 字段类型, IF(c.IS_NULLABLEYES,是,否) AS 是否可空, c.COLUMN_DEFAULT AS 缺省值, IF(st.SEQ_IN_INDEX IS NULL, , CONCAT(主键-, st.SEQ_IN_INDEX)) AS 主键标识 FROM information_schema.TABLES t JOIN information_schema.COLUMNS c ON t.TABLE_SCHEMA c.TABLE_SCHEMA AND t.TABLE_NAME c.TABLE_NAME LEFT JOIN information_schema.STATISTICS st ON st.TABLE_SCHEMA c.TABLE_SCHEMA AND st.TABLE_NAME c.TABLE_NAME AND st.COLUMN_NAME c.COLUMN_NAME AND st.INDEX_NAME PRIMARY WHERE t.TABLE_SCHEMA 你的库名 ORDER BY t.TABLE_NAME, c.ORDINAL_POSITION df pd.read_sql(sql, conn) df.to_excel(表结构字典.xlsx, indexFalse) conn.close()pymysql和pandas需要提前安装。如果公司环境不让装 pandas那就退回 CSV 方案一样够用。6.3 版本差异和元数据快照我在几个版本上踩过不同的细节MySQL 5.7 和 8.0 里information_schema.COLUMNS的字段名基本一致查询逻辑通用但表达式默认值的展示、排序规则的默认值不同版本存在差异。真遇到拿不准的地方最快的排查方式就是把目标表SHOW CREATE TABLE的结果和information_schema里的值放一起对拍。另外结构查询导出的只是一份“瞬间快照”。如果数据库结构经常变建议把它纳入不定期的巡检动作每次变更前后各导出一份用文本对比工具看看差异。我遇到过一个事故就是上线脚本里把某个字段从varchar(50)偷偷改成了varchar(200)索引直接超长报警最后就是靠这种结构字典对比发现的时间点。结构查询这个东西平时不起眼关键时刻是真的能救命的。