mysql json格式数据操作

• mysql5.7以上提供了一种新的字段格式-json，大概是mysql想把非关系型和关系型数据库一口通吃，所以推出了这种非常好用的格式，这样，我们的很多基于mongoDb或者clickHouse的业务都可以用mysql去实现了。5.7的版本只是最基础的版本，对于海量数据的效率是远远不够的，不过这些都在mysql8.0解决了。

1、创建json格式字段

CREATE TABLE `dept` (
  `id` int(11) NOT NULL,
  `dept` varchar(255) DEFAULT NULL,
  `json_value` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、存储数据

insert into dept VALUES(1,'部门1','{"deptName": "部门1", "deptId": "1", "deptLeaderId": "3"}');
insert into dept VALUES(2,'部门2','{"deptName": "部门2", "deptId": "2", "deptLeaderId": "4"}');
insert into dept VALUES(3,'部门3','{"deptName": "部门3", "deptId": "3", "deptLeaderId": "5"}');
insert into dept VALUES(4,'部门4','{"deptName": "部门4", "deptId": "4", "deptLeaderId": "5"}');
insert into dept VALUES(5,'部门5','{"deptName": "部门5", "deptId": "5", "deptLeaderId": "5"}');

3、基础查询操作

3.1 使用 json字段名->’$.json属性’ 进行查询条件

SELECT * from dept WHERE json_value->'$.deptLeaderId'='5';

3.2 json中多个字段关系查询

SELECT * from dept WHERE json_value->'$.deptLeaderId'='5' and json_value->'$.deptId'='5';

4、函数查询操作

4.1 函数 json_extract()

查询json中的某个字段值
json_extract(字段名,$.json字段名)

select id,json_extract(json_value,'$.deptName') as deptName from dept;

select id,replace(json_extract(json_value,'$.deptName'),'"','') as deptName from dept;

字符串替换空

4.2 函数JSON_CONTAINS()：JSON格式数据是否在字段中包含特定对象

JSON_CONTAINS(target, candidate[, path])

select * from dept WHERE JSON_CONTAINS(json_value, JSON_OBJECT("deptName","部门5"))

4.3 函数JSON_ARRAY():创建JSON数组

insert into dept VALUES(7,'部门9','{"deptName": ["1","2","3"], "deptId": "5", "deptLeaderId": "5"}');
insert into dept VALUES(7,'部门9','{"deptName": ["5","6","7"], "deptId": "5", "deptLeaderId": "5"}');

JSON_ARRAY([val[, val] …])

SELECT * from dept WHERE JSON_CONTAINS(json_value->'$.deptName',JSON_ARRAY("1"))

4.4 函数JSON_TYPE(）：查询某个json字段属性类型

JSON_TYPE(json_val)

SELECT json_value->'$.deptName' ,JSON_TYPE(json_value->'$.deptName') as type from dept 

4.5 函数JSON_EXTRACT() ：从JSON文档返回数据

SELECT * FROM dept WHERE JSON_EXTRACT(json_value,'$.deptName') like '%部门%';

4.6 函数JSON_KEYS() ：JSON文档中的键数组

SELECT JSON_KEYS(json_value) FROM dept

4.7 函数JSON_CONTAINS(JSON_ARRAY(‘1’,‘2’,‘3’),json_value->‘$.name’)

json数据某个字段值in过滤

4.8 函数JSON_SET() ：将数据插入JSON格式中，有key则替换，无key则新增

JSON_SET(json_doc, path, val[, path, val] …)

update dept set json_value=JSON_SET('{"deptName": "部门2", "deptId": "2", "deptLeaderId": "4"}','$.deptName','新增的部门1','$.newData','新增的数据') WHERE id=2;

select * from dept WHERE id =2

4.9 函数JSON_INSERT():插入值（往json中插入新值，但不替换已经存在的旧值）

JSON_INSERT(json_doc, path, val[, path, val] …)

UPDATE dept set json_value=JSON_INSERT('{"a": "1", "b": "2"}', '$.deptName', '新增的部门2','$.newData2','新增的数据2') 
WHERE id=2![在这里插入图片描述](https://img-blog.csdnimg.cn/079fb79747574acdadb97c53af0d5af7.png)

4.10 函数JSON_REPLACE（）：

JSON_REPLACE(json_doc, path, val[, path, val] …)

UPDATE dept set json_value=JSON_REPLACE('{"a": "1", "b": "2", "deptName": "新增的部门2", "newData2": "新增的数据2"}', '$.newData2', '更新的数据2') WHERE id =2;

select * from dept WHERE id =2

4.11 函数JSON_REMOVE() ：从JSON文档中删除数据

UPDATE dept set json_value=JSON_REMOVE('{"a": "1", "b": "2", "deptName": "新增的部门2", "newData2": "更新的数据2"}','$.a') WHERE id =2;

5、Mybatis对JSON数据查询、新增

5.1、自定义TypeHandler

package com.xxx.xxx.handler;
  
import java.io.IOException;  
import java.sql.CallableStatement;  
import java.sql.PreparedStatement;  
import java.sql.ResultSet;  
import java.sql.SQLException;  
  
import com.fasterxml.jackson.core.JsonProcessingException;  
import com.fasterxml.jackson.databind.DeserializationFeature;  
import com.fasterxml.jackson.databind.ObjectMapper;  
import lombok.extern.slf4j.Slf4j;  
import org.apache.ibatis.exceptions.PersistenceException;  
import org.apache.ibatis.type.BaseTypeHandler;  
import org.apache.ibatis.type.JdbcType;  
import org.apache.ibatis.type.MappedJdbcTypes;  

/**
 1. @ModifyTime 2021/11/25
 2. JSON 字段类型处理器
 **/
@Slf4j
@MappedJdbcTypes(JdbcType.VARCHAR)
public class JacksonTypeHandler<T extends Object> extends BaseTypeHandler<T> {
    private static ObjectMapper objectMapper;
    private Class<T> type;

    static {
        objectMapper = new ObjectMapper();
        objectMapper.configure(DeserializationFeature.FAIL_ON_UNKNOWN_PROPERTIES, false);
    }

    public JacksonTypeHandler(Class<T> type) {
        if (log.isTraceEnabled()) {
            log.trace("JacksonTypeHandler(" + type + ")");
        }
        if (null == type) {
            throw new PersistenceException("Type argument cannot be null");
        }
        this.type = type;
    }

    private T parse(String json) {
        try {
            if (json == null || json.length() == 0) {
                return null;
            }
            return objectMapper.readValue(json, type);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    private String toJsonString(T obj) {
        try {
            return objectMapper.writeValueAsString(obj);
        } catch (JsonProcessingException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public T getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return parse(rs.getString(columnName));
    }

    @Override
    public T getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return parse(rs.getString(columnIndex));
    }

    @Override
    public T getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return parse(cs.getString(columnIndex));
    }

    @Override
    public void setNonNullParameter(PreparedStatement ps, int columnIndex, T parameter, JdbcType jdbcType)
            throws SQLException {
        ps.setString(columnIndex, toJsonString(parameter));
    }
}

5.2、mapper文件中字段映射规则

-- 指定返回信息使用该Typehandler
<result column="jsonParam" property="jsonParam" jdbcType="VARCHAR"
            typeHandler="com.xx.xx.handler.JacksonTypeHandler"/>       

-- 新增或修改时指定该字段对应的对象类型、以及Typehandler
`parameter` = #{parameter,javaType=com.xx.xx.entity.ParameterEntity, typeHandler=com.xx.xx.handler.JacksonTypeHandler},

// 实体类
// 可以在xml中指定column与property对应关系
// 也可以在实体类直接指定@Column(name='param')
// json这个字段在实体类指定没生效
@Data
public class ParameterEntity{
	private JSONObject jsonParam;
}