mysql常用json语句大全

MySQL 5.7 开始原生支持 JSON 类型，用jn语之前只能把 JSON 存成 TEXT 然后靠应用层解析。用jn语现在数据库自己就能查、用jn语能改、用jn语能建索引。用jn语下面这些语句是用jn语我日常用得最多的，直接上干货。用jn语

建表和插入

JSON 列不能有默认值，用jn语这点要注意。用jn语建表时直接声明类型就行：

CREATE TABLE products ( id INT PRIMARY KEY AUTO_INCREMENT,用jn语 name VARCHAR(100), attrs JSON ); INSERT INTO products (name, attrs) VALUES ('手机', '{ "brand":"小米","price":2999,"tags":["5G","骁龙"]}'), ('耳机', '{ "brand":"索尼","price":1299,"tags":["降噪"]}');

插入时字符串必须是合法 JSON，否则直接报错。用jn语我见过有人把单引号当双引号用，用jn语MySQL 会毫不留情地拒绝。用jn语

提取字段

-> 和 ->> 的用jn语区别值得单独说。-> 返回带引号的用jn语 JSON 值，->> 返回去引号的纯文本。实际查询里后者更常用。

-- 返回 "小米"（带引号） SELECT attrs->'$.brand' FROM products; -- 返回 小米（不带引号） SELECT attrs->>'$.brand' FROM products; -- 提取数组元素 SELECT attrs->>'$.tags[0]' FROM products; -- 嵌套路径 SELECT attrs->>'$.specs.weight' FROM products;

路径表达式里 $ 代表整个文档，. 取对象属性，[n] 取数组下标。写惯了 JavaScript 的话这个语法很顺手。

条件查询

WHERE 子句里用 ->> 提取后比较，或者用 JSON_EXTRACT() 函数。前者更简洁。

-- 价格大于 2000 的商品 SELECT name FROM products WHERE attrs->>'$.price' > 2000; -- 包含特定标签 SELECT name FROM products WHERE JSON_CONTAINS(attrs->'$.tags', '"5G"'); -- 判断键是否存在 SELECT name FROM products WHERE JSON_CONTAINS_PATH(attrs, 'one', '$.brand'); -- 精确匹配整个 JSON 片段 SELECT name FROM products WHERE JSON_EXTRACT(attrs, '$.brand') = '索尼';

JSON_CONTAINS 第二个参数必须是合法 JSON 值，字符串要带双引号。我第一次用的时候传了 '5G'，结果一直返回空，排查了半天。

修改数据

更新 JSON 字段别用字符串拼接，用 JSON_SET、JSON_INSERT、JSON_REPLACE 这几个函数。

-- 设置或更新字段 UPDATE products SET attrs = JSON_SET(attrs, '$.price', 2599) WHERE id = 1; -- 只插入不存在的键 UPDATE products SET attrs = JSON_INSERT(attrs, '$.stock', 100) WHERE id = 1; -- 只替换已存在的键 UPDATE products SET attrs = JSON_REPLACE(attrs, '$.brand', '红米') WHERE id = 1; -- 删除键 UPDATE products SET attrs = JSON_REMOVE(attrs, '$.tags') WHERE id = 1; -- 数组追加元素 UPDATE products SET attrs = JSON_ARRAY_APPEND(attrs, '$.tags', '新品') WHERE id = 1;

这几个函数的区别很明确：SET 是存在就改、不存在就加；INSERT 只加不改；REPLACE 只改不加。用错了不会报错，但结果不是你想要的。

索引问题

JSON 列本身不能直接建索引。要加速查询，得建虚拟生成列，再在生成列上建索引。

ALTER TABLE products ADD COLUMN brand VARCHAR(50) GENERATED ALWAYS AS (attrs->>'$.brand') VIRTUAL; CREATE INDEX idx_brand ON products(brand);

VIRTUAL 列不占存储空间，查询时动态计算。如果查询频率极高，可以考虑 STORED，但会占用磁盘。

其他常用函数

-- JSON 对象的所有键 SELECT JSON_KEYS(attrs) FROM products; -- JSON 数组长度 SELECT JSON_LENGTH(attrs->'$.tags') FROM products; -- 格式化输出 SELECT JSON_PRETTY(attrs) FROM products; -- 合并两个 JSON SELECT JSON_MERGE_PATCH('{ "a":1}', '{ "b":2}'); -- 类型判断 SELECT JSON_TYPE(attrs->'$.price') FROM products;

JSON_MERGE_PATCH 和 JSON_MERGE_PRESERVE 行为不同。前者遇到重复键会覆盖，后者会保留两个值变成数组。合并配置对象时我一般用 PATCH，符合直觉。

实际项目里 JSON 字段适合存结构不固定的扩展属性，比如商品规格、用户偏好设置。但别把所有字段都塞进 JSON，关系型该有的约束和索引还是得靠正经列。JSON 是补充，不是替代。