MySQL SHOW CREATE TABLE 和 INFORMATION_SCHEMA 为什么看到的属性不同

遇到“表结构明明一样，两个查询看到的属性却不同”，先不要把它当成数据库元数据冲突。SHOW CREATE TABLE 面向的是“怎样重新创建这张表”的完整定义；INFORMATION_SCHEMA 面向的是“怎样按字段、表名和属性筛选元数据”。同一张表用两种入口读取，展示口径自然不同。

官方地址：https://dev.mysql.com/doc/refman/8.4/en/show-create-table.html

简单记忆：要复制或审阅建表语句，先看 SHOW CREATE TABLE；要批量找列、默认值、字符集或注释，优先查询 INFORMATION_SCHEMA。下面按这个选择顺序拆开说明。

先判断：你要的是建表定义，还是可筛选元数据

这两个入口不是同一份结果换了格式。SHOW CREATE TABLE 返回一条重建表所需的 CREATE TABLE 定义，适合迁移前审阅、生成变更脚本和确认表级选项；INFORMATION_SCHEMA 是一组可以使用 WHERE、ORDER BY 和聚合的系统视图，适合做批量盘点。

用 SHOW CREATE TABLE 读取可复现的表定义

需要完整上下文时，先读取目标表的建表语句。执行账号需要对目标表拥有某种权限；结果中表名和列名的引号形式还会受到 sql_quote_show_create 会话选项影响。

-- 读取一张表的完整定义，适合复制前审阅
SHOW CREATE TABLE app.orders;

-- 用竖排输出，便于在命令行中查看很长的 CREATE TABLE
SHOW CREATE TABLE app.orders\G;

这里的“完整”是建表语句视角，不代表每个属性都会以你熟悉的原始写法出现。例如，MySQL 8.4 会把列定义中的 CHECK 约束按表约束形式显示；这通常是规范化展示，不等于约束消失。

还要留意两个容易误判的边界：生成的不可见主键默认也可能出现在建表定义中；表经历存储引擎变更后，某些不适用于新引擎的旧选项仍可能保留在定义里，便于回退。因此，看到 ROW_FORMAT 或隐藏主键时，不要直接拿它和运行时状态字段逐字比较。

用 INFORMATION_SCHEMA.COLUMNS 做可筛选的列级核对

INFORMATION_SCHEMA.COLUMNS 提供列名、序号、默认值、是否可为空、数据类型、字符集、排序规则和注释等结构化字段。它适合一次检查多个表，但查询时要明确限定 TABLE_SCHEMA，避免只靠表名撞到同名表。

-- 按定义顺序读取指定表的关键列属性
SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    ORDINAL_POSITION,
    COLUMN_NAME,
    COLUMN_DEFAULT,
    IS_NULLABLE,
    DATA_TYPE,
    CHARACTER_SET_NAME,
    COLLATION_NAME,
    COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'app'
  AND TABLE_NAME = 'orders'
ORDER BY ORDINAL_POSITION;

这里有一个关键差异：从 COLUMNS 查询时不会自动按列定义顺序返回，必须显式 ORDER BY ORDINAL_POSITION。而 SHOW COLUMNS 的展示还可能把多个 COLUMNS 字段组合成一个 Type 文本，所以不能简单地按列数或字符串是否相同判断两份结果一致。

如果只想盘点所有业务表的字符集，可以换成批量条件；这也是 INFORMATION_SCHEMA 比建表语句更适合审计清单的地方：

-- 批量找出 app 库中使用指定字符集的字符列
SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'app'
  AND CHARACTER_SET_NAME = 'utf8mb4'
ORDER BY TABLE_NAME, ORDINAL_POSITION;

为什么两边看到的属性会不一样

第一类差异来自信息粒度。SHOW CREATE TABLE 把列、约束、索引、引擎、字符集等组合成一段可执行定义；COLUMNS 只关心列级字段，表级选项要去看 INFORMATION_SCHEMA.TABLES，约束则要看对应的约束表。

第二类差异来自规范化。列上的 CHECK 约束在 SHOW CREATE TABLE 中可能按表约束呈现；默认值、字符集和排序规则则可能分别散落在 COLUMN_DEFAULT、CHARACTER_SET_NAME 和 COLLATION_NAME。一边是可执行文本，一边是拆开的字段，不同并不等于矛盾。

第三类差异来自隐藏对象和开关。生成的不可见主键默认可在建表语句和信息架构中显示，也可通过 show_gipk_in_create_table_and_information_schema 关闭这类信息。对同一环境做核对时，应把相关会话设置一起记录。

按任务收尾：复现、批量审计和迁移核对分别怎么做

如果目标是复现表，保存 SHOW CREATE TABLE 的结果，同时记录 MySQL 版本、相关会话选项和目标库名。不要把 INFORMATION_SCHEMA.COLUMNS 的几行结果拼回一条建表语句，因为它不覆盖所有表级信息。

如果目标是批量审计，查询 COLUMNS、TABLES 和约束相关信息，并统一使用明确的库名、表名和排序字段。这样得到的结果更适合导出、比较和后续脚本处理。

如果目标是迁移前后核对，先用 SHOW CREATE TABLE 确认定义，再用 INFORMATION_SCHEMA 按列输出差异。看到默认值、排序规则或隐藏列不同，先核对展示开关和版本，再决定是否需要进一步检查实际数据。

• 需要一条可以重放的定义：选择 SHOW CREATE TABLE。
• 需要跨多个表筛选属性：选择 INFORMATION_SCHEMA，并显式排序。
• 需要解释差异：按列级、表级、约束级和会话开关分层核对。

相关问题

为什么 INFORMATION_SCHEMA.COLUMNS 的列顺序和 SHOW COLUMNS 不一样？

SELECT 查询系统表时不会自动承诺按定义顺序返回，所以要使用 ORDER BY ORDINAL_POSITION。排序后再比较，才不会把返回顺序差异误判为结构差异。

SHOW CREATE TABLE 为什么出现我没有写过的 CHECK 约束形式？

MySQL 可以把列约束按表约束形式展示。判断约束是否存在时，应关注约束表达式和名称，不要只比较它在建表语句中的排版位置。

只看 INFORMATION_SCHEMA 能不能生成完整建表脚本？

不建议。它适合按字段查询元数据，但完整脚本还涉及索引、约束、引擎和表级选项；需要复现时直接保存 SHOW CREATE TABLE 的结果更稳妥。