- 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;
}