元数据：让数据库自己解释自己

上一章我们把一段复杂查询保存成了视图。视图建好以后，数据库必须记住它叫什么、有哪些列、定义来自哪条 SELECT、能不能更新，以及调用者用什么权限执行它。再往前看，表的列、主键、外键、索引也都不是靠人脑记住的。数据库每次解析 SQL，都要先去自己的目录里核对这些信息。

这些“描述数据库里有什么、长什么样、彼此如何关联”的数据，就是元数据。

元数据最直接的价值，不是让你背一串系统表名，而是让数据库回答原本要靠人工翻文档的问题：小满商店现在有几张表？哪些表没有主键？外键两端分别是哪一列？某次上线到底加了什么字段？一个视图还依赖哪些结构？如果一个查询卡了很久，现在是谁在执行它？

这一章以 MySQL 8.4 为主。我们会把 information_schema 当成一组可以查询的只读视图，用普通 SELECT 完成结构盘点、约束检查、索引体检、文档生成和变更对比。遇到 PostgreSQL 的实现差异时，我会直接指出边界。学完这一章，你手里会有一份能反复执行的小满商店数据库体检清单，也会知道哪些结论不能只凭元数据下判断。

元数据到底记录了什么

先看一个很普通的问题：下面这条 SQL 为什么能在执行前就发现列名写错了？

SELECT product_name, price
FROM products;

小满商店的 products 表没有 price，正确列名是 unit_price。数据库不需要把整张表读一遍才发现错误，它在解析阶段就能从数据字典中知道 products 有哪些列。换句话说，数据库先读元数据，再决定如何读业务数据。

我们可以把两者分开：

严格来说，PROCESSLIST 展示的是运行状态，不是静态表结构。但它和结构目录一样，都通过系统视图暴露，也常被放进数据库巡检脚本，所以本章会在最后单独讲它的适用边界。

information_schema 不是一套可以修改的业务表

在 MySQL 中，information_schema 看起来像一个数据库，里面的对象也可以用 SELECT 查询；但这些对象是数据库提供的只读视图。你不能向其中 INSERT、UPDATE 或 DELETE，也不应该试图用修改系统目录的方式改变表结构。要新增列，就执行 ALTER TABLE；要创建索引，就执行 CREATE INDEX。DDL 成功后，数据库会自动更新它维护的元数据。

SELECT table_schema, table_name, table_type
FROM information_schema.tables
WHERE table_schema = 'xiaoman_shop'
ORDER BY table_name;

元数据还有一个容易被忽略的特点：你看到的是当前账号有权看到的那部分世界。一个权限受限的账号查询 information_schema.tables，结果可能比管理员少；某些定义字段也可能因为权限不足而显示为 NULL。因此，“查询没有返回某对象”只能说明当前连接没有看见它，不能直接证明对象一定不存在。

schema、database 与 catalog 不要混着猜

MySQL 把 SCHEMA 当作 DATABASE 的同义词。CREATE SCHEMA xiaoman_shop 与 CREATE DATABASE xiaoman_shop 表达的是同一层级，所以过滤业务范围时通常写：

WHERE table_schema = 'xiaoman_shop'

PostgreSQL 的层级不同：一个服务器实例可以有多个数据库，一个数据库内部又可以有多个 schema。连接到某个 PostgreSQL 数据库后，information_schema 主要描述当前数据库内可见的对象，table_catalog 是当前数据库名，table_schema 才是 public、sales 这样的命名空间。

所以，把 MySQL 查询迁移到 PostgreSQL 时，不能看到 table_schema 就机械填数据库名。先问清楚目标到底是 database 还是 schema，这一步能避免很多“明明有表却查不到”的问题。

一张路线图先记住用途

本章会频繁使用下面这些视图。先不背字段，先记住它们分别回答什么问题：

从 SCHEMATA 开始确定检查边界

元数据查询最怕一上来就扫整个实例。正确顺序是先确认目标 schema，再把后面的每条查询都限制在这个范围内。

SELECT
    schema_name,
    default_character_set_name,
    default_collation_name
FROM information_schema.schemata
WHERE schema_name = 'xiaoman_shop';

结果可以整理成：

这里的字符集和排序规则是 schema 的默认值。它们会影响后续新建对象的默认选择，但不能证明 schema 里的每张旧表、每个字符列都使用同一规则。要检查表级排序规则，还得看 TABLES.table_collation；要检查列级规则，则要看 COLUMNS.character_set_name 与 collation_name。

如果你只想确认当前连接默认使用哪个 schema，可以执行：

SELECT DATABASE() AS current_schema;

在通用巡检脚本里，我更建议把目标 schema 写成明确参数，再在开头检查它是否存在。不要完全依赖 USE 后的会话状态，因为脚本被连接池、任务平台或其他人拆分执行时，默认 schema 可能已经变了。

SET @target_schema = 'xiaoman_shop';
 
SELECT CASE
           WHEN EXISTS (
               SELECT 1
               FROM information_schema.schemata
               WHERE schema_name = @target_schema
           ) THEN '可以开始检查'
           ELSE '目标 schema 不存在或当前账号不可见'
       END AS precheck;

用 TABLES 和 COLUMNS 完成结构盘点

确定范围后，先看对象清单。TABLES 一行代表一张表或一个视图，TABLE_TYPE 用来区分它们。

SELECT
    table_name,
    table_type,
    engine,
    table_collation
FROM information_schema.tables
WHERE table_schema = 'xiaoman_shop'
ORDER BY table_type, table_name;

小满商店的九张核心业务表都是 BASE TABLE：

视图也会出现在 TABLES 中，但它的 ENGINE 通常为 NULL。如果只想盘点物理业务表，必须加上 table_type = 'BASE TABLE'，否则视图可能被误算进表数量，后面的主键检查也会把视图当成“没有主键的表”。

SELECT
    table_type,
    COUNT(*) AS object_count
FROM information_schema.tables
WHERE table_schema = 'xiaoman_shop'
GROUP BY table_type
ORDER BY table_type;

TABLES 里的行数和大小为什么不能当账本

TABLE_ROWS、DATA_LENGTH、INDEX_LENGTH 很适合做容量趋势和异常筛查：

SELECT
    table_name,
    table_rows,
    ROUND(data_length / 1024 / 1024, 2) AS data_mb,
    ROUND(index_length / 1024 / 1024, 2) AS index_mb
FROM information_schema.tables
WHERE table_schema = 'xiaoman_shop'
  AND table_type = 'BASE TABLE'
ORDER BY data_length + index_length 

但对 InnoDB 来说，TABLE_ROWS 通常是优化器使用的估算值，不等于 SELECT COUNT(*) 得到的精确行数。DATA_LENGTH 和 INDEX_LENGTH 也受到页分配、统计刷新和存储引擎实现影响。它们适合回答“哪张表明显更大”“本周增长是否异常”，不适合回答“今天一共成交了多少笔订单”。业务口径仍然要查业务表。

COLUMNS 把一张表拆成有顺序的列

查看 products 的列结构时，至少带上 ORDINAL_POSITION。如果只按列名排序，生成的结构文档会丢掉建表时的顺序。

SELECT
    ordinal_position,
    column_name,
    column_type,
    is_nullable,
    column_default,
    extra
FROM information_schema.columns
WHERE table_schema = 'xiaoman_shop'
  AND table_name = 'products'
ORDER BY ordinal_position;

这里同时出现了 DATA_TYPE 和 COLUMN_TYPE 两种概念。DATA_TYPE 更适合做跨表分类，例如找出所有 varchar；COLUMN_TYPE 保留了长度、精度以及 MySQL 类型细节，更适合展示。文档生成时还要一起保留 CHARACTER_MAXIMUM_LENGTH、NUMERIC_PRECISION、NUMERIC_SCALE、DATETIME_PRECISION、字符集和排序规则，否则一个简单的 decimal 并不能告诉读者它到底是 DECIMAL(10,2) 还是 DECIMAL(18,4)。

一次列出整库字段字典

下面这条查询已经可以作为字段文档的底表：

SELECT
    c.table_name,
    c.ordinal_position,
    c.column_name,
    c.column_type,
    c.is_nullable,
    c.column_default,
    c.extra,
    c.column_comment
FROM information_schema.columns AS c
JOIN information_schema.

如果只想先看各表宽度，可以聚合列数：

SELECT
    table_name,
    COUNT(*) AS column_count
FROM information_schema.columns
WHERE table_schema = 'xiaoman_shop'
GROUP BY table_name
ORDER BY table_name;

九张核心表的列数是：

这张结果适合做结构基线。比如某次上线后 customers 从 7 列变成 8 列，你就知道需要继续核对新增列，而不是在完整 DDL 中靠肉眼寻找差异。

用条件查询发现可疑列

元数据能把设计规则翻译成 SQL。假设小满商店规定金额列必须是定点数、主业务时间列不能允许空值，可以先筛出候选问题：

SELECT
    table_name,
    column_name,
    column_type,
    is_nullable
FROM information_schema.columns
WHERE table_schema = 'xiaoman_shop'
  AND (
      (column_name IN ('unit_price', 'total_amount', 'amount')
       AND data_type <> 'decimal')
      OR
      (column_name IN ('order_date', 'paid_at', 'moved_at')

结果为空，只代表这些列通过了当前规则，不代表整个数据库设计已经正确。比如 amount 即使是 DECIMAL，精度也可能太小；paid_at 是否允许为空，还取决于未支付记录的建模方式。元数据能帮你筛选，业务规则负责给出标准。

用 STATISTICS 看懂索引，而不是数索引行

information_schema.statistics 是 MySQL 提供的索引元数据视图。这里一行表示“一个索引中的一个列位置”，所以复合索引会占多行。直接 COUNT(*) 得到的是索引列数，不是索引个数。

SELECT
    table_name,
    index_name,
    non_unique,
    seq_in_index,
    column_name,
    collation,
    cardinality,
    index_type
FROM information_schema.statistics
WHERE table_schema = 'xiaoman_shop'
ORDER BY table_name, index_name, seq_in_index;

其中：

• NON_UNIQUE = 0 表示索引值必须唯一，1 表示允许重复。
• SEQ_IN_INDEX 表示列在复合索引中的位置，从 1 开始。
• COLUMN_NAME 对普通列索引给出列名；函数索引的键部分是表达式时，不能只靠普通列名理解。
• CARDINALITY 是不同值数量的估计，不是实时精确统计。
• INDEX_TYPE 常见为 BTREE，也可能是 FULLTEXT、SPATIAL 等。

把每个索引聚合回一行，更适合人读：

SELECT
    table_name,
    index_name,
    CASE MIN(non_unique)
        WHEN 0 THEN '唯一'
        ELSE '非唯一'
    END AS uniqueness,
    GROUP_CONCAT(
        COALESCE(column_name, expression)
        ORDER BY seq_in_index
        SEPARATOR ', '
    ) AS index_columns
FROM information_schema.statistics
WHERE table_schema = 'xiaoman_shop'
GROUP BY table_name, index_name
ORDER BY

把小满商店明确创建的六个复合索引摘出来，会看到：

完整结果还会包含各表的 PRIMARY，以及存储引擎为外键需要而创建的支持索引。这张表必须按列顺序读。索引 (customer_id, order_date) 通常能支持只按 customer_id 过滤，也能支持同时按两列过滤；但它不能被简单视为 order_date 的单列索引。最左前缀和具体查询条件共同决定索引能不能被使用，最终还要看执行计划。

找出没有显式主键的业务表

在 MySQL 中，主键索引名是 PRIMARY。下面的反连接能找出没有可见主键的基础表：

SELECT t.table_name
FROM information_schema.tables AS t
LEFT JOIN information_schema.table_constraints AS tc
  ON tc.constraint_schema = t.table_schema
 AND tc.table_name = t.table_name
 AND tc.constraint_type = 'PRIMARY KEY'
WHERE t.table_schema

九张核心表都应有主键，因此理想结果是空集。

MySQL 8.4 还可能启用生成不可见主键功能。启用后，InnoDB 会为符合条件、原本没有主键的表生成不可见主键；相关信息默认可以出现在 COLUMNS 和 STATISTICS 中，但服务器变量能够影响展示。巡检报告应该区分“业务显式设计的主键”和“服务器补上的生成不可见主键”，不能因为看到了 PRIMARY 就认为数据模型已经经过业务设计。

外键列有没有可用的左前缀索引

InnoDB 创建外键时，需要引用端和被引用端存在可用索引；必要时可能自动创建支持索引。因此，一个已经成功存在的 InnoDB 外键通常不会真的处于“完全无索引”状态。不过巡检仍然应该核对索引列顺序，尤其是在结构迁移、索引清理或跨数据库检查时。

下面先把每个外键的列按约束顺序拼起来，再判断它是否是某个索引的左前缀：

WITH fk_columns AS (
    SELECT
        constraint_schema,
        table_name,
        constraint_name,
        GROUP_CONCAT(
            column_name
            ORDER BY ordinal_position
            SEPARATOR ','
        ) AS columns_in_fk
    FROM information_schema.key_column_usage
    WHERE constraint_schema = 'xiaoman_shop'
      AND referenced_table_name IS NOT NULL
    GROUP BY constraint_schema, table_name, constraint_name
), index_columns AS (
    SELECT

如果返回记录，先核对存储引擎、外键状态、表达式索引和版本能力，再决定是否补索引。不要让检查脚本直接执行 CREATE INDEX。

“缺少索引”不能只靠结构下结论

元数据可以可靠发现没有主键、重复前缀、外键支持索引异常等结构问题，却不能仅凭列名判断业务查询需要什么索引。比如 orders.status 没有单列索引，并不自动等于缺陷：如果状态只有少数几个值，单列索引可能选择性很低；如果所有查询都先按 customer_id 再按 status 过滤，复合索引也许更合适。

索引决策至少还要结合：

1. 高频 SQL 的筛选、连接、排序和分组方式；
2. EXPLAIN 或 EXPLAIN ANALYZE 展示的访问路径；
3. 数据分布、写入成本与已有索引重叠情况。

STATISTICS.cardinality 可以提供估算线索，但不能代替执行计划和工作负载数据。

PostgreSQL 的索引边界

STATISTICS 是 MySQL 的扩展，不是可跨数据库照搬的标准信息模式视图。PostgreSQL 的 information_schema 同样能查表、列、约束和视图，但完整索引信息通常要从 pg_catalog.pg_index、pg_indexes 等 PostgreSQL 系统目录或视图获取。

这也说明了一个实际原则：标准 information_schema 适合写可移植的“结构共同部分”，数据库特有的索引、执行和存储细节要明确分支。为了表面统一而硬凑一条跨方言 SQL，最后往往会丢掉最关键的信息。

把约束的三层信息拼起来

约束查询最容易让人困惑，因为信息被拆在多个视图里。可以把它们理解成三张不同粒度的表：

• TABLE_CONSTRAINTS：这是什么约束，属于哪张表。
• KEY_COLUMN_USAGE：约束使用了哪些列，列的先后顺序是什么。
• REFERENTIAL_CONSTRAINTS：如果它是外键，更新和删除时采取什么规则。

先看约束目录

SELECT
    table_name,
    constraint_name,
    constraint_type,
    enforced
FROM information_schema.table_constraints
WHERE constraint_schema = 'xiaoman_shop'
ORDER BY table_name, constraint_type, constraint_name;

CONSTRAINT_TYPE 常见值包括 PRIMARY KEY、UNIQUE、FOREIGN KEY 和 CHECK。这一层能告诉我们 orders 有一个主键和一个客户外键，但还不知道外键落在哪一列。

再把列映射接上

KEY_COLUMN_USAGE 中，ORDINAL_POSITION 表示列在约束内的位置，不是列在表中的位置。对复合外键，这个顺序决定了两端如何一一对应。

SELECT
    kcu.table_name,
    kcu.constraint_name,
    kcu.ordinal_position,
    kcu.column_name,
    kcu.referenced_table_name,
    kcu.referenced_column_name
FROM information_schema.key_column_usage AS kcu
WHERE kcu.constraint_schema = 'xiaoman_shop'
ORDER BY
    kcu.table_name

外键部分可以整理成：

customers.referrer_id 和 employees.manager_id 都是自引用外键。只看“父表名和子表名是否不同”会漏掉这类关系，画关系图时也要允许一张表指回自己。

最后接上更新与删除规则

SELECT
    kcu.table_name,
    kcu.constraint_name,
    GROUP_CONCAT(
        CONCAT(
            kcu.column_name,
            ' -> ',
            kcu.referenced_table_name,
            '.',
            kcu.referenced_column_name
        )
        ORDER BY kcu.ordinal_position
        SEPARATOR ', '
    ) AS column_mapping,

这次得到的是一份可以直接评审的外键清单：

删除规则不能只看名字猜。订单明细随订单删除可能符合业务预期，而客户被删除时连带删除历史订单通常很危险。元数据负责把现状摊开，业务负责人负责判断现状是否合理。

MySQL 与 PostgreSQL 在可见性上的差别

两者都实现了标准信息模式的一部分，但具体可见行受各自权限规则影响。PostgreSQL 的某些约束视图只展示当前角色拥有或具备特定权限的对象；MySQL 也通常只展示当前账号有适当权限的对象。跨环境比对时，应使用权限一致的只读巡检账号，并单独记录数据库版本。

另外，PostgreSQL 的外键列信息经常需要把 key_column_usage、referential_constraints 和 constraint_column_usage 组合起来；MySQL 的 KEY_COLUMN_USAGE 直接提供 REFERENCED_TABLE_NAME 与 REFERENCED_COLUMN_NAME，写法更短。不要假定同名视图的所有扩展列也完全相同。

从 VIEWS 重新检查上一章创建的接口

上一章我们用视图隐藏复杂查询。到了元数据这一章，视图不再只是“像表一样查询的对象”，它自己也成了可检查、可比较的数据库接口。

SELECT
    table_name AS view_name,
    check_option,
    is_updatable,
    security_type,
    definer,
    view_definition
FROM information_schema.views
WHERE table_schema = 'xiaoman_shop'
ORDER BY table_name;

几个字段分别回答不同问题：

• VIEW_DEFINITION：视图保存的查询定义。权限不足时可能看不到完整内容。
• CHECK_OPTION：通过可更新视图修改数据时，是否检查结果仍满足视图条件。
• IS_UPDATABLE：数据库根据视图结构判断它是否可能更新，不代表当前用户一定有更新权限。
• SECURITY_TYPE：执行时按定义者还是调用者的权限检查。
• DEFINER：定义者账号，是迁移视图时经常被忽略的环境依赖。

COLUMNS 同样能检查视图输出契约

应用真正依赖的往往不是视图定义文本，而是视图对外暴露的列名、顺序和类型。可以把 TABLES 与 COLUMNS 连接起来，只导出视图字段：

SELECT
    c.table_name AS view_name,
    c.ordinal_position,
    c.column_name,
    c.column_type,
    c.is_nullable
FROM information_schema.columns AS c
JOIN information_schema.tables AS t
  ON t.table_schema = c.table_schema

如果客服页面依赖 v_customer_directory.customer_name，那么删除或改名这一列就是接口变更。即使视图内部只是换了一种等价写法，只要输出列契约不变，调用方通常不需要改 SQL。这正好承接了上一章“用视图隔离复杂性”的思路。

精确还原视图时优先用 SHOW CREATE VIEW

INFORMATION_SCHEMA.VIEWS 适合批量筛选、排序和生成报告；如果要拿到 MySQL 可重新执行的完整视图 DDL，应使用：

SHOW CREATE VIEW xiaoman_shop.v_city_sales_summary;

它能保留算法、安全方式、定义者等 MySQL 语法细节。只把 VIEW_DEFINITION 拼到 CREATE VIEW 后面，可能漏掉这些属性。迁移到另一个环境前还要检查 DEFINER 是否存在，以及是否应该改用更合适的执行账号。

元数据能告诉你定义，却不能保证视图仍然正确

视图存在、字段类型正确，不代表业务口径正确。比如订单总额视图把 cancelled 订单也算进销售额，所有结构检查仍可能通过。结构体检之后，还要运行数据断言：抽样核对订单、检查金额守恒、验证边界状态。元数据测试与业务数据测试解决的是两类问题。

SHOW 与 information_schema 怎么选

在 MySQL 客户端里，SHOW TABLES、DESCRIBE products 很顺手。它们没有过时，也不需要为了“显得标准”全部替换掉。真正的区别在于使用场景。

下面两组语句回答的是相近问题：

SHOW TABLES FROM xiaoman_shop;

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'xiaoman_shop';

后者可以继续写 AND table_type = 'BASE TABLE'、按名称排序、与 COLUMNS 连接，还能直接被程序读取。前者更适合人临时敲一下。

SHOW CREATE TABLE 与自己拼 DDL 不是同一件事

SHOW CREATE TABLE xiaoman_shop.products;

如果目标是备份、迁移或精确复现，优先用数据库提供的完整 DDL、专用导出工具或经过验证的迁移工具。只查 COLUMNS 再拼字符串，很容易漏掉：

• 主键、唯一约束、外键和检查约束；
• 索引前缀长度、降序、表达式和可见性；
• 生成列定义、列注释与表注释；
• 存储引擎、字符集、排序规则和表选项；
• 触发器、分区以及版本特有属性。

自己生成 DDL 更适合受控的小任务，例如为审核生成一批 ALTER TABLE ... ADD INDEX 候选，而不是发明一个不完整的数据库导出器。

生成文档与 DDL 时，转义比拼接更重要

元数据天然适合生成文本：字段文档、关系图输入、检查报告，甚至待审核的 DDL。但从“查到名称”走到“把名称放回 SQL”时，必须区分两种完全不同的内容：标识符与字面量。

标识符和字符串字面量不是一种引号

表名、列名、索引名属于标识符。MySQL 通常用反引号引用标识符，名称内部的反引号要写成两个反引号。

字符串值属于字面量，通常用单引号。MySQL 的 QUOTE() 可以把字符串转成带引号、经过转义的 SQL 字面量，但它不能拿来引用表名。

SELECT
    CONCAT('`', REPLACE('order`archive', '`', '``'), '`') AS quoted_identifier,
    QUOTE('华东区 O''Reilly') AS quoted_literal;

quoted_identifier  | quoted_literal
`order``archive`   | '华东区 O\'Reilly'

应用层如果支持数据库驱动提供的标识符引用函数，应优先使用驱动或成熟 SQL 构造器。不要假定参数占位符能替代表名：

-- 这类写法通常不能把 ? 当作表名
SELECT * FROM ?;

预编译参数主要用来绑定值，不是绑定 SQL 语法结构。动态表名无法避免时，先从固定白名单或可信元数据中选择，再按目标数据库规则引用。

安全地生成“候选索引”语句

假设团队已经人工确认，要给下面两个组合补索引：

候选清单应该来自受控配置，而不是直接接收网页输入。生成语句时，对每个标识符分别转义：

WITH index_candidates AS (
    SELECT
        'customers' AS table_name,
        'idx_customers_city_registered' AS index_name,
        'city' AS first_column,
        'registered_at' AS second_column
    UNION ALL
    SELECT
        'products',
        'idx_products_status_stock',
        'status',
        'stock'
)
SELECT CONCAT(
    'CREATE INDEX ',
    '`', REPLACE(index_name, 

生成结果是两条普通的 MySQL DDL：

CREATE INDEX `idx_customers_city_registered`
ON `xiaoman_shop`.`customers` (`city`, `registered_at`);
 
CREATE INDEX `idx_products_status_stock`
ON `xiaoman_shop`.`products` (`status`, `stock`);

这段示例把两个列名分开验证和引用。如果索引列数不固定，可以把候选列保存成“一列一行并带顺序”的受控数据，再逐个引用后聚合；不要接收一整段外部传入的逗号列表并原样拼进去。

让数据库生成字段文档

文档生成比 DDL 生成安全，因为结果通常只是文本，不会立刻执行。下面把每一列整理成一行 Markdown：

SELECT CONCAT(
    '| ', REPLACE(table_name, '|', '\\|'),
    ' | ', ordinal_position,
    ' | ', REPLACE(column_name, '|', '\\|'),
    ' | ', REPLACE(column_type, '|', '\\|'),
    ' | ', is_nullable,
    ' | ', COALESCE(REPLACE(CAST(column_default AS CHAR), 

即使只是生成 Markdown，也要转义竖线和换行，否则一个列注释就可能破坏表格。生成 HTML 时更不能直接拼原文，必须经过 HTML 转义，避免把对象注释中的 <、&、引号或脚本片段当成标签执行。

GROUP_CONCAT 被截断会制造“看起来完整”的错误

用 GROUP_CONCAT 拼列清单和 DDL 很方便，但结果长度受 group_concat_max_len 限制。复杂表的生成文本可能被截断，而截断后的字符串仍然像一段普通结果，很容易漏过人工检查。

SELECT @@session.group_concat_max_len;

如果确实要生成长文本，可以在当前会话提高限制，然后同时校验列数和生成结果长度：

SET SESSION group_concat_max_len = 1024 * 1024;

别把“把限制调大”当作完整导出方案。精确结构仍优先使用 SHOW CREATE TABLE 或专用工具。

用快照比较变更前后，而不是靠记忆

元数据查询展示当前状态。要回答“和昨天相比发生了什么”，必须把两个时点的结果保存下来再比较。

最小可用的列快照可以放进一张临时表。它只服务于本次结构体检，连接关闭后会自动消失，不属于小满商店的九张核心业务表：

CREATE TEMPORARY TABLE schema_column_snapshots (
    snapshot_name  VARCHAR(64)  NOT NULL,
    captured_at    DATETIME     NOT NULL,
    table_schema   VARCHAR(64)  NOT NULL,
    table_name     VARCHAR(64)  NOT NULL,
    ordinal_position INT        NOT NULL,
    column_name    VARCHAR(64)  NOT NULL,
    column_type    

临时表只在当前连接中可见，因此发布前、发布后两次采集与差异查询必须使用同一个连接。若检查跨越多个连接或需要长期留档，再把相同结构建到独立管理 schema 中，并限制写权限；无论采用哪种方式，它都不是核心九表的一部分。

发布前保存一份：

INSERT INTO schema_column_snapshots (
    snapshot_name,
    captured_at,
    table_schema,
    table_name,
    ordinal_position,
    column_name,
    column_type,
    is_nullable,
    column_default,
    extra
)
SELECT
    'release_2026_08_before',
    NOW(),
    table_schema,
    table_name,
    ordinal_position,
    column_name,
    column_type,
    is_nullable,
    CAST(column_default AS CHAR),
    extra
FROM information_schema

发布后再保存：

INSERT INTO schema_column_snapshots (
    snapshot_name,
    captured_at,
    table_schema,
    table_name,
    ordinal_position,
    column_name,
    column_type,
    is_nullable,
    column_default,
    extra
)
SELECT
    'release_2026_08_after',
    NOW(),
    table_schema,
    table_name,
    ordinal_position,
    column_name,
    column_type,
    is_nullable,
    CAST(column_default AS CHAR),
    extra
FROM information_schema

找新增列、删除列和属性变化

MySQL 没有直接的 FULL OUTER JOIN。可以用三段查询分别找新增、删除和修改，再用 UNION ALL 合并：

WITH before_columns AS (
    SELECT *
    FROM schema_column_snapshots
    WHERE snapshot_name = 'release_2026_08_before'
      AND table_schema = 'xiaoman_shop'
),
after_columns AS (
    SELECT *
    FROM schema_column_snapshots
    WHERE snapshot_name = 'release_2026_08_after'
      AND table_schema = 'xiaoman_shop'
)
SELECT
    '新增列' AS change_type,

<=> 是 MySQL 的 NULL 安全相等运算符。普通 = 遇到 NULL 会得到 UNKNOWN，从而漏掉“默认值从 NULL 改为某个值”这类变化。课程前面学过的 NULL 三值逻辑，在元数据对比里又出现了。

可能的发布差异如下：

列快照不等于完整结构快照

只保存 COLUMNS，发现不了索引、外键、检查约束和视图定义变化。成熟的结构基线至少要分别保存：

• 基础表与视图清单；
• 列定义及顺序；
• 索引列、顺序、唯一性与可见性；
• 约束、外键列映射、更新与删除规则；
• 视图输出列和完整定义的规范化结果。

对完整 DDL 做文本 diff 时，还要先处理自动生成名称、环境相关 DEFINER、自增当前值等噪声。否则报告里满是无关差异，真正危险的变更反而被淹没。

用 PROCESSLIST 看当前正在发生什么

结构检查结束后，再看运行状态。information_schema.processlist 是 MySQL 特有视图，它展示当前连接与线程的快照：谁连进来、默认使用哪个库、正在执行什么命令、处于什么状态、已经持续多久。

SELECT
    id,
    user,
    host,
    db,
    command,
    time,
    state,
    LEFT(info, 160) AS sql_preview
FROM information_schema.processlist
WHERE id <> CONNECTION_ID()
ORDER BY time DESC, id;

TIME 是线程处于当前状态的秒数，不一定等于一条业务请求从开始到现在的完整耗时。INFO 在空闲连接上可能为 NULL，STATE 也可能为空。状态名称会随版本和执行阶段变化，程序不应把每个状态字符串写死成永久接口。

为什么有时只能看到自己的连接

没有相应进程查看权限的普通账号，通常只能看到自己的线程；具备 PROCESS 权限的账号才能看到其他用户的线程。巡检结果突然只剩一行时，先核对权限，不要立刻判断“其他连接都消失了”。

INFO 可能包含业务 SQL 甚至敏感字面量。把进程列表写入日志或告警平台前，应限制接收者、控制保留时间，并考虑脱敏。元数据查询本身同样需要最小权限。

PROCESSLIST 只是一张照片

它适合临时回答：

• 当前是否有持续很久的查询；
• 哪个账号或主机发起了连接；
• 连接目前使用哪个默认数据库；
• 某个线程正在等待还是执行。

它不适合回答：

• 过去一小时最慢的十条 SQL；
• 一条查询每次执行的平均耗时；
• 谁在昨天造成了锁等待；
• 某状态随时间如何变化。

这些问题需要 Performance Schema、sys 视图、慢查询日志或外部监控。MySQL 8.4 还提供基于 Performance Schema 的进程视图，常规持续监控通常更适合从 performance_schema.threads、performance_schema.processlist 或 sys.processlist 获取信息；传统 SHOW PROCESSLIST 和 information_schema.processlist 更适合临时排查。

不要仅凭“时间长”就终止连接

一个持续 300 秒的连接可能是异常查询，也可能是经过批准的数据迁移。Sleep 很久可能说明连接池配置不理想，也可能只是正常保留连接。终止线程前至少要核对账号、来源主机、命令、状态、SQL、事务情况和业务负责人。

-- 只生成复核清单，不在巡检脚本里自动 KILL
SELECT
    id,
    user,
    host,
    db,
    time,
    state,
    info
FROM information_schema.processlist
WHERE command <> 'Sleep'
  AND time >= 60
ORDER BY time DESC;

PostgreSQL 没有 information_schema.processlist。对应的活动会话通常从 pg_stat_activity 查询，字段、权限和状态含义都不同。这一段监控 SQL 必须按数据库方言分别维护。

小满商店数据库体检清单

现在把前面的查询收束成一次有顺序的检查。顺序很重要：先确认范围与权限，再盘结构；先生成问题清单，再决定变更；最后才观察运行状态。

检查目标与可见范围

SELECT
    VERSION() AS mysql_version,
    CURRENT_USER() AS privilege_account,
    USER() AS connected_account,
    DATABASE() AS current_schema;
 
SELECT
    schema_name,
    default_character_set_name,
    default_collation_name
FROM information_schema.schemata
WHERE schema_name = 'xiaoman_shop';

通过标准：目标 schema 可见；执行账号与巡检记录一致；版本没有偏离团队支持范围。

盘点表、视图与列

SELECT
    t.table_name,
    t.table_type,
    COUNT(c.column_name) AS column_count
FROM information_schema.tables AS t
LEFT JOIN information_schema.columns AS c
  ON c.table_schema = t.table_schema
 AND c.table_name =

通过标准：九张核心业务表都存在；列数符合基线；计划内视图存在；没有意外对象。

检查每张基础表的主键

SELECT
    t.table_name,
    CASE WHEN COUNT(tc.constraint_name) = 1
         THEN '通过'
         ELSE '检查'
    END AS pk_check
FROM information_schema.tables AS t
LEFT JOIN information_schema.table_constraints AS tc
  ON tc.constraint_schema = t.table_schema

通过标准：每张核心表都有且只有一个主键约束；生成不可见主键已被单独识别和登记。

检查外键映射与动作

SELECT
    kcu.table_name,
    kcu.column_name,
    kcu.referenced_table_name,
    kcu.referenced_column_name,
    rc.update_rule,
    rc.delete_rule
FROM information_schema.key_column_usage AS kcu
JOIN information_schema.referential_constraints AS rc
  ON rc.constraint_schema =

通过标准：十一条预期外键都存在；复合外键顺序正确；CASCADE、SET NULL、RESTRICT 等规则与业务生命周期一致。

检查索引覆盖与重叠候选

先运行本章前面的外键左前缀检查，再导出完整索引清单。对于结构完全相同、仅名称不同的索引，可以用聚合结果找候选重复项：

WITH index_signatures AS (
    SELECT
        table_schema,
        table_name,
        index_name,
        non_unique,
        index_type,
        GROUP_CONCAT(
            COALESCE(column_name, expression)
            ORDER BY seq_in_index
            SEPARATOR ','
        ) AS indexed_parts
    FROM information_schema.statistics
    WHERE table_schema = 'xiaoman_shop'
    GROUP BY
        table_schema,
        table_name,
        index_name,
        non_unique,

通过标准：没有未经说明的完全重复索引；外键列有可用左前缀索引；新增索引都有对应查询和执行计划证据。

完全相同只是最容易发现的一类重叠。(customer_id) 与 (customer_id, order_date) 是否都需要，不能靠这条查询自动决定：较长索引有时能替代较短索引，有时又因覆盖、索引大小或查询模式不同而需要并存。

检查视图接口与定义者

SELECT
    table_name,
    is_updatable,
    check_option,
    security_type,
    definer
FROM information_schema.views
WHERE table_schema = 'xiaoman_shop'
ORDER BY table_name;

通过标准：视图输出列符合接口基线；DEFINER 在目标环境存在；安全方式经过审核；需要只读的聚合视图没有被误认为可更新接口。

对比发布前后结构

保存 TABLES、COLUMNS、STATISTICS、约束和 VIEWS 快照，再按对象键比较。通过标准：所有差异都能在发布单中找到对应变更；没有漏执行、重复执行或环境漂移。

观察当前会话

SELECT
    user,
    db,
    command,
    COUNT(*) AS connection_count,
    MAX(time) AS longest_seconds
FROM information_schema.processlist
GROUP BY user, db, command
ORDER BY longest_seconds DESC;

通过标准不是“没有长连接”，而是异常连接有明确解释与处理人；监控账号权限足够但不过度；SQL 文本没有被无控制地扩散到日志。

把体检结果分成三种结论

执行完检查后，不要只输出一大段查询结果。每一项都归入下面一种状态：

这种分级能避免两个极端：要么看到任何差异都慌忙回滚，要么生成了几十页报告却没有任何行动。

练习：把元数据真正用起来

练习一：只找基础表

请写一条查询，列出 xiaoman_shop 中所有基础表的名称，并按名称排序。结果不能包含视图。

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'xiaoman_shop'
  AND table_type = 'BASE TABLE'
ORDER BY table_name;

练习二：找可空的外键列

请找出小满商店中所有允许 NULL 的外键列，显示表名、列名和引用目标。

SELECT
    kcu.table_name,
    kcu.column_name,
    kcu.referenced_table_name,
    kcu.referenced_column_name
FROM information_schema.key_column_usage AS kcu
JOIN information_schema.columns AS c
  ON c.table_schema = kcu.table_schema
 AND c.table_name =

练习三：辨认索引统计

下面哪个说法正确？

练习四：判断能否自动建索引

练习五：补全 NULL 安全比较

在 MySQL 快照对比中，如果要把两个可能为 NULL 的默认值视为相等，应使用哪个运算符？

练习六：写一份视图接口清单

请写 SQL，列出所有视图及其输出列，要求每个视图占一行，列名按视图内顺序以逗号连接。

SELECT
    c.table_name AS view_name,
    GROUP_CONCAT(
        c.column_name
        ORDER BY c.ordinal_position
        SEPARATOR ', '
    ) AS output_columns
FROM information_schema.columns AS c
JOIN information_schema.tables AS t
  ON t.table_schema = c.table_schema
 AND

从会写 SQL 到会管理变化

到这里，这套课程走完了一个完整闭环。

我们从表、行、列开始，学会用 SELECT 找数据；接着处理过滤、NULL、连接、聚合、子查询和集合；然后用增删改维护数据，用事务守住一组操作的边界，用索引和执行计划理解性能，用视图保存稳定接口。最后这一章又绕回数据库本身：我们用 SQL 查询 SQL 世界的结构，再用查询结果检查主键、外键、索引、视图和变化。

元数据让很多原本靠经验的动作变得可重复：

• 新环境初始化后，运行同一份结构体检；
• 发布前后，各保存一次元数据快照；
• 每周检查缺少主键和重复索引候选；
• 生成字段文档与外键关系清单；
• 视图改动时，对比输出列契约；
• 排障时，用进程视图确认当前会话，再进入更完整的监控系统。

但它也有清楚的边界。元数据能说明结构是什么，不能替你定义业务规则；能列出索引，不能仅凭列表判断哪个查询最快；能展示当前会话，不能恢复过去发生的一切；能生成 DDL 文本，不代表生成结果应该自动执行。

接下来最适合做的，不是再背更多系统视图，而是把小满商店当成一套长期维护的数据库项目：

1. 为九张核心表写一份版本化 DDL，并让它能从空库完整创建结构。
2. 保存一次 TABLES、COLUMNS、索引、约束和视图基线，把差异检查放进发布流程。
3. 为订单创建一组典型查询，用 EXPLAIN 验证索引，而不是凭列名猜。
4. 给关键业务规则写数据断言，例如订单总额与明细金额是否一致、支付成功金额是否越界。
5. 在 MySQL 和 PostgreSQL 各完成一次元数据盘点，亲手整理标准部分与方言部分。
6. 把体检结果输出为 Markdown 或 JSON，但保持“只读检查、人工批准、迁移脚本执行”的权限边界。

当这些动作可以重复运行时，你维护的就不再是一堆“目前看起来能用”的表，而是一套知道自己有什么、知道哪里发生变化、也知道如何验证变化的数据库系统。

customers.referrer_id、categories.parent_id、employees.manager_id 这类可选关系通常会出现在结果中。允许空值并不等于设计错误，它表示“当前没有推荐人、上级分类或直属经理”是允许存在的状态。

这份结果适合保存为接口基线。若列很多，记得检查 group_concat_max_len，避免输出被截断。