JSON 与灵活 Schema 建模实践

CREATE TABLE product (
  id BIGINT PRIMARY KEY,
  name VARCHAR(128),
  color VARCHAR(32) NULL,      -- 只有服装类有
  weight_kg DECIMAL(8,2) NULL, -- 只有物流类有
  screen_size VARCHAR(32) NULL, -- 只有数码类有
  -- 每加一种商品类型就要加一列，列数失控
);

CREATE TABLE product_ext (
  id BIGINT PRIMARY KEY,
  attrs JSON NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 插入：自动校验 JSON 合法性，非法直接报错
INSERT INTO product_ext (id, attrs) VALUES
  (1, '{"brand":"Apple","color":"gray","storage":256}');

-- 读取：-> 返回 JSON，->> 返回去引号文本
SELECT attrs->>'$.brand' AS brand,
       attrs->'$.storage' AS storage
FROM product_ext WHERE id = 1;

-- PG 创建 JSONB 表
CREATE TABLE product_ext (
  id BIGSERIAL PRIMARY KEY,
  attrs JSONB NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

-- 查询与索引
CREATE INDEX idx_attrs_brand ON product_ext USING gin (attrs);
SELECT * FROM product_ext WHERE attrs @> '{"brand":"Apple"}';

划分三问：
  1. 会不会 WHERE/ORDER BY 用它？     → 会 → 独立列
  2. 会不会 JOIN/唯一约束？           → 会 → 独立列
  3. 变更频率高、旧数据不统一？       → 会 → JSON 列

{
  "brand": "string 必填",
  "color": "string 可选",
  "storage": "int 可选，单位 GB",
  "extras": "object 自由扩展，限 3 层"
}

-- 用 CHECK 约束兜底契约（PG 示例）
ALTER TABLE product_ext ADD CONSTRAINT attrs_shape CHECK (
  jsonb_typeof(attrs->'brand') = 'string'
);

推荐形态：小且平的 JSON
  {"a": 1, "b": "x", "c": [1,2,3]}                ✅ 好：3 层内、键少
  {"a": {"b": {"c": {"d": {"depth": 5}}}}}        ❌ 坏：深嵌套、难查

CREATE TABLE product_ext (
  id BIGINT PRIMARY KEY,
  attrs JSON NOT NULL,
  brand VARCHAR(64) AS (attrs->>'$.brand') STORED,   -- 虚拟列
  INDEX idx_brand (brand)                            -- 索引虚拟列
);

-- 查询时直接用虚拟列条件 → 命中索引
SELECT * FROM product_ext WHERE brand = 'Apple';

-- GIN 索引：支持 @>、?、?| 等操作符
CREATE INDEX idx_attrs ON product_ext USING gin (attrs jsonb_path_ops);

-- 常用查询
SELECT * FROM product_ext WHERE attrs @> '{"color":"gray"}';     -- 包含
SELECT * FROM product_ext WHERE attrs ? 'brand';                 -- 存在键
SELECT * FROM product_ext WHERE attrs ?| ARRAY['color','size'];  -- 任一键

-- MySQL 8.0+：函数索引
CREATE INDEX idx_attrs_color ON product_ext ((CAST(attrs->>'$.color' AS CHAR(32))));

-- PostgreSQL：表达式索引
CREATE INDEX idx_attrs_brand ON product_ext ((attrs ->> 'brand'));

-- 查询要写与索引表达式完全一致的形式才能命中
SELECT * FROM product_ext WHERE CAST(attrs->>'$.color' AS CHAR(32)) = 'gray';

阶段一：保持 JSON，新增虚拟列 + 索引（读路径无感）
阶段二：双写，写入时同时维护 JSON 与独立列（新老字段并存）
阶段三：数据回填 + 代码切换，老代码只读 JSON，新代码读独立列
阶段四：下线 JSON 字段（回收冗余）

-- 阶段一示例：为高频字段建虚拟列与索引
ALTER TABLE product_ext
  ADD COLUMN storage_gb INT AS (attrs->>'$.storage') STORED,
  ADD INDEX idx_storage (storage_gb);

-- 阶段二示例：应用层双写（伪代码）
-- product.set_storage(256); product.attrs['storage'] = 256; 两者同步写

-- 阶段三：把历史 JSON 数据回填到独立列
UPDATE product_ext
SET brand = attrs->>'$.brand'
WHERE brand IS NULL AND JSON_EXTRACT(attrs, '$.brand') IS NOT NULL;

-- 回填后对比抽查：JSON 与独立列是否一致
SELECT COUNT(*) FROM product_ext
WHERE brand <> attrs->>'$.brand';   -- 期望 0

-- 双写阶段的一致性巡检（每天跑一次）
SELECT COUNT(*) FROM product_ext
WHERE brand IS NOT NULL AND attrs->>'$.brand' IS NOT NULL
  AND brand <> attrs->>'$.brand';            -- 期望 0

-- 回填进度监控
SELECT COUNT(*) AS total,
       SUM(brand IS NOT NULL) AS filled
FROM product_ext;                             -- filled/total 趋近 100%