mysql 常用 json 函数

本文总结了 mysql 常用的 json 内置函数。以前，我们存储 json 数据一般会采用 nosql 数据库, 因为旧版本的 mysql 不支持 json 类型的字段。现在，如果您有存储 json 数据的需求，且对 json 字段可能只是做一些简单的管理操作，如对 json 对象某属性的增删改查等, 那么使用高版本的 mysql 即可满足需求。

mysql5.7 以上版本支持 json 类型的字段，用于存储 json 对象.
mysql 提供了一系列 json 字段的操作函数。

解析 json 字段的某个属性

select json_extract(ad_config, '$.listMiddle.interval') from distributor;

修改 json 字段的某个属性

update distributor set ad_config = json_replace(ad_config, '$.listMiddle.interval', 2);

判断某个json字段中的数组是否包含某个值

create database test_json;

use test_json;
create table test (
    id int auto_increment primary key ,
    f1 json
);

insert into test(f1) value ('[{"name": "zhangsan","sex":1},{"name": "lisi", "sex": 2}]');
select id, f1, json_extract(f1, '$[*].name'), json_contains(json_extract(f1, '$[*].name'), '["zhangsan"]') from test;

按json列的某子属性为条件查询

参考文档：https://developer.aliyun.com/article/768446 示例如下

CREATE TABLE `t1` (
  `id` bigint(20) unsigned primary key ,
  `title` varchar(255),
  `properties` json comment '使用json存储各自平台的特有属性'
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
create table t2(
    id bigint unsigned primary key
);
INSERT INTO test_json.t1 (id, title, properties) VALUES (1, 'title1', '{"creation": {"sort": 1, "category_id": 1}}');
INSERT INTO test_json.t1 (id, title, properties) VALUES (2, 'title2', '{"creation": {"sort": 2, "category_id": 2}}');
INSERT INTO test_json.t2 (id) VALUES (1);

# 查询json列的数据：利用mysql8新增的特性JSON_TABLE
select id, title, properties, properties_json.*  from t1, JSON_TABLE(properties, '$.creation' columns (
    category_id bigint unsigned path '$.category_id',
    sort int path '$.sort'
    )) properties_json;


# 以json列的某个属性为条件：利用mysql8新增的特性JSON_TABLE
select id, title, properties, properties_json.*  from t1, JSON_TABLE(properties, '$.creation' columns (
    category_id bigint unsigned path '$.category_id',
    sort int path '$.sort'
    )) properties_json
where properties_json.category_id = 1;

# 以json列的某个属性为条件：mysql5.7支持
select id, title, properties, json_extract(properties, '$.creation.category_id') as category_id, json_extract(properties, '$.creation.sort') as sort from t1
where properties -> '$.creation.category_id' = 1;

# 将json列的某个属性关联到另一个表，以json列的某个属性为条件：利用mysql8新增的特性JSON_TABLE
select t1.id, t2.id, t1.title, t1.properties, properties_json.*  from t1, JSON_TABLE(properties, '$.creation' columns (
    category_id bigint unsigned path '$.category_id',
    sort int path '$.sort'
    )) properties_json inner join t2 on properties_json.category_id = t2.id
where properties_json.category_id = 1;