MySQL表结构转JSON：避开常见陷阱，实现高效文档化方案
你是否需要将MySQL的表定义转换为一份清晰、可直接使用的JSON文档？这项工作听起来简单，但实际操作中，直接解析SHOW CREATE TABLE命令的输出会遇到格式不统一的问题，容易出错。有没有更稳定可靠的方法？答案是肯定的。
利用 INFORMATION_SCHEMA 查询并转换表结构为 JSON 是最佳实践
MySQL本身并未提供一键导出JSON格式表结构的命令，但我们可以依赖一个跨版本稳定、信息准确的“元数据宝库”——INFORMATION_SCHEMA数据库。与其费力解析格式自由的SHOW CREATE TABLE文本，不如直接从几个核心的系统视图中精准提取信息。具体而言，通过组合查询COLUMNS、KEY_COLUMN_USAGE和TABLES这三张视图，可以确保字段顺序、约束类型、默认值等关键细节无一遗漏。
具体操作流程如下：
- 基础字段信息查询
COLUMNS视图：首先从INFORMATION_SCHEMA.COLUMNS中获取字段名、数据类型、是否允许NULL、默认值（COLUMN_DEFAULT）以及字段注释（COLUMN_COMMENT）。这是构建表结构JSON的骨架。 - 主键与外键约束查询
KEY_COLUMN_USAGE视图：接着使用INFORMATION_SCHEMA.KEY_COLUMN_USAGE来补充约束信息。关键点在于：主键约束的名称（CONSTRAINT_NAME）固定为'PRIMARY'，可用于准确定位。 - 表级别元数据查询
TABLES视图：最后，INFORMATION_SCHEMA.TABLES视图提供了存储引擎、行格式、表注释等信息。避免硬编码如ENGINE=InnoDB，从这里获取最为准确。 - 避免使用
SHOW FULL COLUMNS命令：需要提醒的是，尽量避免依赖SHOW FULL COLUMNS FROM tbl。它不返回外键引用的目标表，且其Extra字段内容格式较为随意（例如将auto_increment和on update CURRENT_TIMESTAMP混合显示），不利于程序化处理。
生成 JSON 时必须手动处理 NULL 值与特殊字符
信息组装完成后，下一步是生成JSON字符串。虽然MySQL内置的JSON_OBJECT()和JSON_ARRAYAGG()函数使用便捷，但存在两个必须警惕的“暗坑”。
首先是NULL值问题。如果某个字段的默认值或注释为NULL，这些函数会直接跳过对应的键，导致生成的JSON结构不完整，字段看似“消失”。
第二个问题更为棘手，即特殊字符处理。字段注释中常包含换行符、双引号、反斜杠等，若未经处理直接拼接到JSON字符串中，会破坏JSON格式的合法性，导致解析失败。
因此，在生成环节必须进行手动干预：
- 处理NULL值：对所有可能为
NULL的字段（如注释、默认值），使用IFNULL(col, '')或COALESCE(col, '')函数将其转换为空字符串，确保键值对不会丢失。 - 转义特殊字符：对于注释这类用户输入内容，必须进行转义。可以使用嵌套的
REPLACE函数处理反斜杠、双引号和换行符，例如：REPLACE(REPLACE(REPLACE(col, '\', '\\'), '"', '\"'), '', '\n')。 - 统一数据类型命名规范：从
DATA_TYPE获取的类型名称可能存在大小写不一致（如tinyint和TINYINT）。建议统一映射为标准大写形式，避免给下游解析器带来困扰。 - 避免手动拼接JSON字符串：切勿使用
CONCAT('{', ... , '}')这种方式手动拼接JSON。一旦遗漏逗号或引号，整个JSON将无效，且MySQL可能不会报错，排查难度极大。
使用存储过程封装逻辑比临时 SQL 更易于复用和维护
如果仅需导出一两张表，编写复杂的SQL查询尚可应付。但当需要批量处理数十甚至上百张表时，手写多层JOIN结合GROUP_CONCAT和JSON_OBJECT的查询，不仅容易出错，还可能遗漏外键关联或索引信息。
此时，存储过程的优势便凸显出来。将其封装成一个过程，只需传入数据库名和表名参数，即可输出标准化的JSON。你还可以增加参数，灵活控制是否包含索引、分区信息、触发器等可选内容，显著增强代码的复用性。
编写存储过程时，有以下几点建议：
- 利用变量存储结果：在过程内部，使用
SELECT ... INTO @json将生成的JSON存入用户变量，最后通过SELECT @json返回。这种方式通常比使用游标遍历性能更优。 - 妥善处理外键信息：外键引用的目标表名（
REFERENCED_TABLE_NAME）仅在外键约束存在时才非空。查询时应使用LEFT JOIN并结合IFNULL函数处理，避免结果集缺失。 - 考虑文档化扩展需求：如果生成JSON的主要目的是为了创建数据字典或API文档，可以设计一个
is_document_mode参数。启用时，可从COLUMN_COMMENT中提取中文名、补充示例值，甚至标记敏感字段。 - 注意动态SQL的安全性：尽量避免在存储过程内使用
PREPARE/EXECUTE动态拼接数据库名，这涉及权限和SQL注入风险。更安全的做法是使用CONCAT('SELECT ... FROM ', db_name, '.COLUMNS')进行字符串拼接，但前提是传入的db_name参数来源可信。
导出后校验 JSON 合法性：不能仅依赖 JSON_VALID()
JSON生成后，切勿直接使用。使用MySQL自带的JSON_VALID()函数进行检查？这固然正确，但它仅进行最基础的语法校验。
这意味着它只检查字符串是否符合JSON格式规范（如括号配对、引号正确等）。至于JSON结构是否符合预期——例如字段数组是否为空、主键字段是否被正确标记为"primary_key": true、外键信息是否包含"ref_table"——这些它一概不负责。而这些结构缺失，恰恰会导致下游的文档渲染失败或代码生成工具报错。
因此，你需要一套更强大的校验逻辑：
- 验证核心数据结构：导出后，立即使用
JSON_EXTRACT(@json, '$.columns')确认“columns”数组是否存在且长度大于0。 - 检查必备字段路径：使用
JSON_CONTAINS_PATH函数，确保JSON中包含了诸如$.name（表名）、$.engine（存储引擎）等必需的路径。 - 关注数据类型一致性：如果下游是前端展示，需额外验证
JSON_EXTRACT(@json, '$.columns[0].type')返回的是字符串类型。有时数字类型的字段值可能被JSON解析器当作数字处理，从而丢失引号，引发前端解析异常。 - 利用本地开发工具辅助校验：一个快速的本地校验方法是，将生成的JSON字符串复制到VS Code等编辑器中，安装JSON格式化插件（如JSON Tools），然后执行格式化命令。如果格式化失败，通常意味着JSON中存在隐藏的非法字符，例如不可见的零宽空格。
值得一提的是，其中最棘手的可能是时间类型字段的默认值处理。CURRENT_TIMESTAMP在COLUMN_DEFAULT中以字符串形式存储，但在不同MySQL版本下，其表现形式可能不同——有的带括号，有的不带，有的还包含小数秒。在生成JSON前，最好将它们统一归一化为"CURRENT_TIMESTAMP"这样的字面量字符串，否则下游的JSON解析器可能将其视为普通字符串，从而失去其“当前时间”的特殊语义。
