MySQL 生成列提取 JSON 字段怎样选类型

我第一次把业务 JSON 字段做成索引时，卡在的不是语法，而是“这一列到底应该是什么类型”。同一个 payload，里面的 customer_id 适合整数，order_code 适合字符串，total 适合定点数；如果全部先取成 JSON 或长字符串，查询虽然能跑，比较规则、索引宽度和空值行为却会一起变得模糊。

这篇只解决一个边界：MySQL 生成列从 JSON 提取字段时怎样选类型，并让生成列表达式、类型转换和索引保持一致。我的经验是先按查询语义选 SQL 类型，再写提取表达式，最后才决定是否建索引。

需要按数值比较就落成整数或 DECIMAL，需要按文本匹配就落成合适长度的 VARCHAR，需要按时间范围查询就落成 DATETIME；不要把所有 JSON 字段都当成字符串。

触发场景：JSON 能读出来，不代表适合直接索引

假设订单表把扩展信息放在 payload 中：

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  payload JSON NOT NULL
);

-- 下面的 JSON 只是示例数据，分别代表编号、金额和下单时间
INSERT INTO orders (id, payload) VALUES
(1, '{"customer_id": 1008, "order_code": "A-20261114", "total": 38.50, "created_at": "2026-11-14 09:30:00"}');

JSON_EXTRACT(payload, '$.customer_id') 取到的是 JSON 值；即使它看起来像数字，也不等同于一个已经确定比较规则的整数列。我的判断顺序通常是：这个字段是否参与数值运算？是否做范围查询？是否需要前缀或等值匹配？有没有缺失键？这些答案直接决定生成列类型。

权限配置：先把字段语义映射成 SQL 类型

我会先做一张很小的映射表，而不是看到 JSON 字符串就统一使用 VARCHAR(255)：

这里的“权限”更像上线前的配置门槛：执行建表或变更需要相应的 DDL 权限，但权限本身不会帮你推断类型。字段如果有前导零、精度要求或时区约定，必须在生成列表达式里明确下来。

流水线阶段：提取、去壳、转换，再决定 VIRTUAL 或 STORED

对字符串字段，我更倾向于用 ->> 或 JSON_UNQUOTE(JSON_EXTRACT(...)) 明确去掉 JSON 字符串外壳；对数字字段，再加一层 CAST。下面的例子把四种查询语义分开写，便于后面和索引表达式对应：

ALTER TABLE orders
  -- 编码保留文本语义，避免 A-001 被当成数字
  ADD COLUMN order_code_v VARCHAR(32)
    GENERATED ALWAYS AS (payload->>'$.order_code') VIRTUAL,
  -- 编号明确转为无符号整数，供等值和范围比较
  ADD COLUMN customer_id_v BIGINT UNSIGNED
    GENERATED ALWAYS AS (CAST(payload->>'$.customer_id' AS UNSIGNED)) STORED,
  -- 金额使用定点数，p,s 要按业务允许范围设置
  ADD COLUMN total_v DECIMAL(12,2)
    GENERATED ALWAYS AS (CAST(payload->>'$.total' AS DECIMAL(12,2))) STORED,
  -- 时间按时间类型落地，不用字符串字典序替代时间比较
  ADD COLUMN created_at_v DATETIME
    GENERATED ALWAYS AS (CAST(payload->>'$.created_at' AS DATETIME)) STORED;

VIRTUAL 不把生成值单独存储，STORED 在写入或更新时计算并保存生成值。我的取舍是：表达式轻、读取不频繁时可以先用 VIRTUAL；字段要被频繁过滤、排序或建立索引时，使用 STORED 往往更容易观察存储与写入代价。最终仍要结合表写入比例和索引需求决定。

质量门禁：索引能否复用取决于表达式和类型是否对齐

生成列建好后，索引列的类型已经成为查询契约。最容易踩坑的是：生成列是 BIGINT，查询却把 JSON 值当字符串比较；或者生成列用了 JSON_UNQUOTE，查询条件又额外套了不同的转换。先把常用条件写得和生成列表达式一致：

CREATE INDEX idx_orders_customer_id_v ON orders (customer_id_v);
CREATE INDEX idx_orders_created_at_v ON orders (created_at_v);

-- 等值与范围条件沿用已定义的生成列类型
SELECT id, payload
FROM orders
WHERE customer_id_v = 1008
  AND created_at_v >= '2026-11-01 00:00:00';

MySQL 8.4 文档说明，优化器可以在条件表达式与生成列表达式匹配、且结果类型一致时利用生成列索引。因此我不会只看“索引已经创建”，还会把实际查询写成围绕生成列的条件，再用执行计划确认是否符合预期。文章不替代你的数据分布测试，尤其不能把一条命中示例当成所有查询都会命中。

失败处理也要提前写清：JSON 键缺失通常会落成 NULL；数字字段出现不符合约定的文本，转换结果可能产生警告或不符合业务预期；金额精度不足时，生成列的 DECIMAL(p,s) 需要重新评估。若类型选错，先停用依赖它的查询或索引，再通过 ALTER TABLE 调整生成列定义，不要悄悄把查询改成字符串比较来掩盖问题。

通知复盘：我现在用这四个问题做最后取舍

1. 这个 JSON 字段的业务语义是数字、文本、金额还是时间？
2. 缺失键、空字符串、前导零和非法值分别应该怎样处理？
3. 生成列定义中的提取、去壳和 CAST 是否与常用查询一致？
4. 查询频率、写入成本和索引宽度是否值得使用 STORED 或建立索引？

我的结论很简单：JSON 只是原始载体，生成列才是给查询使用的类型边界。先选对类型，再谈索引；先固定表达式，再谈优化器能不能复用。

常见问题

JSON_EXTRACT 取出的数字还需要 CAST 吗？

如果生成列要承担明确的数值比较、范围查询或金额计算，我会显式 CAST。这样列定义的意图和索引类型都更清楚，不把隐式转换交给每条查询。

订单编码可以用 BIGINT 吗？

只有在编码真正是数值且不需要保留前导零时才可以。像 00128、A-128 这类业务编码应保留为字符串。

VIRTUAL 和 STORED 应该怎么选？

先看是否需要把生成值持久化并频繁读取，再看写入成本和索引需求。不要只因为 STORED 看起来“更快”就默认选择，字段表达式和访问模式才是依据。