5 分钟搞懂 JSON

建表

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  meta JSON
);

INSERT INTO products VALUES
  (1, 'iPhone', '{"price_cents": 99900, "tags": ["phone","apple"], "stock": {"cn": 100, "us": 50}}'),
  (2, 'Pixel',  '{"price_cents": 79900, "tags": ["phone","google"], "stock": {"cn": 30, "us": 200}}');

MariaDB 的 JSON 类型本质上是 LONGTEXT + CHECK 约束——存的是 JSON 文本，每次查询都要 parse。与 PostgreSQL 的 JSONB 不同。性能上 MariaDB 11.x 已经显著优化，常规使用无感。

取值

-- JSON path
SELECT JSON_EXTRACT(meta, '$.price_cents') FROM products;
-- 或简写 ->
SELECT meta->'$.price_cents' FROM products;
-- 直接 unwrap 引号（推荐）
SELECT meta->>'$.tags[0]' FROM products;

-> 返回 JSON 值（带引号），->> 返回原始字符串。

嵌套路径

SELECT meta->>'$.stock.cn' AS cn_stock FROM products;
-- 100
-- 30

-- 数组下标
SELECT meta->>'$.tags[0]' FROM products;

过滤

SELECT * FROM products WHERE meta->>'$.price_cents' > 80000;
SELECT * FROM products WHERE JSON_CONTAINS(meta->'$.tags', '"apple"');
SELECT * FROM products WHERE JSON_EXISTS(meta, '$.stock.cn');

修改

-- 改值
UPDATE products SET meta = JSON_SET(meta, '$.stock.cn', 200) WHERE id = 1;

-- 加键
UPDATE products SET meta = JSON_SET(meta, '$.color', 'black') WHERE id = 1;

-- 删键
UPDATE products SET meta = JSON_REMOVE(meta, '$.color') WHERE id = 1;

-- 数组追加
UPDATE products SET meta = JSON_ARRAY_APPEND(meta, '$.tags', 'new') WHERE id = 1;

让 JSON 查询用索引

JSON 本身没有索引。但可以生成列 + 索引实现：

ALTER TABLE products
  ADD COLUMN price_cents INT GENERATED ALWAYS AS (CAST(meta->>'$.price_cents' AS UNSIGNED)) STORED,
  ADD INDEX idx_price (price_cents);

-- 现在用生成列查，超快：
SELECT * FROM products WHERE price_cents > 80000;

或者更直接的函数索引（MariaDB 10.5+）：

ALTER TABLE products
  ADD INDEX idx_price ((CAST(meta->>'$.price_cents' AS UNSIGNED)));

数组聚合

-- 把多行变成 JSON 数组
SELECT JSON_ARRAYAGG(name) FROM products;
-- ["iPhone","Pixel"]

SELECT JSON_OBJECTAGG(id, name) FROM products;
-- {"1":"iPhone","2":"Pixel"}

验证

-- 检查是否合法 JSON
SELECT JSON_VALID(meta) FROM products;

-- 在 CHECK 约束里强制
ALTER TABLE products ADD CONSTRAINT ck_meta CHECK (JSON_VALID(meta));

何时不用 JSON

能拆成关系列就拆。JSON 是"逃生通道"不是默认选择
强类型字段 → 用普通列 + 索引
频繁查询的字段 → 至少用生成列 + 索引

与 MySQL 8 的差异

主题	MySQL 8	MariaDB 11
存储	JSONB 类似（二进制）	LONGTEXT + 校验
`JSON_TABLE`	完整	部分
多值索引	8.0+ 支持 `CAST(... AS UNSIGNED ARRAY)`	用生成列/函数索引模拟
性能	略快	11.x 已显著改善

详见迁移：MySQL 8 → MariaDB 11。