mysql JSON字段利用

1.背景

Mysql5.7.8版本以后新增的功能，Mysql提供了一个原生的Json范例，Json值将不再以字符串的形式存储，而是采用一种允许快速读取文本元素（document elements）的内部二进制（internal binary）格式，并提供了不少内置函数,通过盘算列，甚至还可以直接索引json中的数据。
2.数据库基本操作

1.表结构

新建表

1. CREATE TABLE `table_name` (  
2.   `id` INT UNSIGNED NOT NULL,
3.   `business_attr` JSON NOT NULL,
4.   PRIMARY KEY (`id`)
5. );

复制代码

新增json范例字段

1. alter table table_name add column `business_attr` JSON NULL;

复制代码

2.插入操作

在json数据列提供json格式的字符串即可

1. insert into table_name values(1,'{"attr_key1":"attr_value1","attr_key2":"attr_value2"}');

复制代码

如果是字符串，可以通过cast函数转成json

1. insert into table_name values(1,CAST('{"attr_key1":"attr_value1","attr_key2":"attr_value2"}', as JSON));

复制代码

3.更新操作

1.json_set更新json字段

1. update table_name set business_attr=json_set('{"num":1,"name":"abc"}','$.num',2,'$.age',16,'$.class.id',1) where id=1;

复制代码

• JSON_SET(json_doc, path, val[, path, val] ...)
  
• path中$就代表整个doc,然后可以用javascript的方式指定对象属性或者数组下标等.
  
• 值存在就修改，值不存在就设置，路径不存在将直接被忽略。
  

2.json_merge_patch更新json字段

1. update table_name set business_attr=JSON_MERGE_PATCH(IFNULL(business_attr,json_object()),JSON_OBJECT('attr_key1',CAST('attr_value1' as JSON))) where id=1;

复制代码

MySQL JSON_MERGE_PATCH() 函数返回一个由参数指定的多个 JSON 文档归并后的 JSON 文档。JSON_MERGE_PATCH() 执行的是更换归并，即在相同键值时，只保存背面的值。
3.json_insert插入json字段

• JSON_INSERT(json_doc, path, val[, path, val] ...)
  
• 如果不存在对应属性则插入,否则不做任何变动
  

4.删除操作

json_remove方法删除某个属性

1. update table_name set business_attr=JSON_REMOVE(business_attr,'$.attr_key1') where id=1;

复制代码

• JSON_REMOVE(json_doc, path[, path] ...)
  
• 如果存在则删除对应属性,否则不做任何变动
  

5.查询操作

1.利用json_extract函数查询，获得doc中某个或多个节点的值。
JSON_EXTRACT(json_doc, path[, path] ...)

1. SELECT JSON_EXTRACT(business_attr, '$.attr_key1') where id=1;

复制代码

2.利用 字段->'$.json属性'进行查询条件
mysql5.7.9开始增加了一种简写方式：column->path

1. SELECT business_attr->'$.attr_key1' where id=1;

复制代码

   JSON相关函数参考
  https://www.sjkjc.com/mysql-ref/json-functions/
  
3.结合Mybatis利用

TypeHandler 是 Mybatis 中用来处理 Java 范例与数据库范例之间的映射的一个接口。当你需要自定义数据范例的映射规则时，就需要实现这个接口。
例如，需要将新增的JSON字段business_attr映射成Map范例，就需要实现一个MapTypeHandler，用来处理Java中Map和数据JSON字段范例的转换。
[留意]  在自定义范例转换逻辑的情况下，需要显式的设置启用autoResultMap，从而自动映射sql查询结果到相应的实体对象。
1.Java实体类设置

如前所述，需要自定义一个MapTypeHandler，并在JSON范例字段上设置，同时启用autoResultMap。

1. @TableName(value = "table_name", autoResultMap = true)
2. public class TableName {
3.    @TableId(type = IdType.ASSIGN_ID)
4.    private Long id;
5.    
6.    @TableField(typeHandler = MapTypeHandler.class)
7.    private Map<String, Object> businessAttr;
8. }

复制代码

2.增编削操作

为了操作方便，这里同一都利用JSON_MERGE_PATCH作为字段插入和更新的方法，JSON_REMOVE作为字段删除的方法，可根据实际需要调整。

1. //新增和修改
2. public Boolean updateBusinessAttr(AttrDTO dto){
3.    String setSql = " business_attr=JSON_MERGE_PATCH(IFNULL(business_attr,json_object()),JSON_OBJECT('%s',CAST('%s' as JSON)))";
4.    LambdaUpdateWrapper<TableName> updateWrapper = new LambdaUpdateWrapper<>();
5.    updateWrapper.eq(TableName::getId, dto.getId());
6.    updateWrapper.setSql(String.format(setSql, dto.getAttrKey(), UniMybatisJacksonUtil.toJson(dto.getValue())));
7.    this.update(updateWrapper);
8.    return true;
9. }
10. ​
11. //删除
12. public Boolean deleteBusinessAttr(AttrDTO dto) {
13.    LambdaUpdateWrapper<TableName> updateWrapper = new LambdaUpdateWrapper<>();
14.    updateWrapper.eq(TableName::getId, dto.getId());
15.    String setSql = " business_attr=JSON_REMOVE(business_attr,'$.%s')";
16.    updateWrapper.setSql(String.format(setSql, dto.getAttrKey()));
17.    this.update(updateWrapper);
18.    return true;
19. }

复制代码

3.查询操作

直接在mapper.xml文件里编写对应字段查询条件。

1. <!-- 查询attrKey=attrValue的记录 -->
2. <if test="pageQuery.attrs != null and pageQuery.attrs.size() > 0">
3.    and
4.    <foreach collection="pageQuery.attrs" index="attrKey" item="attrValue" open="("
5.             separator="and" close=")">
6.        table_name.business_attr->'$.${attrKey}' = #{attrValue}
7.    </foreach>
8. </if>

复制代码

对查询结果进行操作，直接将数据库查询结果强转成入库时的格式即可。

1. //入库时kvMap值格式是Map<String, String>,查询结果可以直接强转
2. Map<String, String> kvMap = (Map<String, Object>) tableName.getBusinessAttr().get("kvMap");

复制代码

4.json特殊字符处理

如果json字符串中含有转义符或者mysql不支持的字符，会导致入库失败，因此需要自定义JSON字符串处理类UniMybatisJacksonUtil对特殊字符处理一下。

• typeHandler在入库前，重写setNonNullParameter方法：
  

1. public void setNonNullParameter(PreparedStatement ps, int i, Map<K, V> parameter, JdbcType jdbcType) throws SQLException {
2.    ps.setString(i, this.toJson(parameter));
3. }
4. ​
5. private String toJson(Map<K, V> params) {
6.    try {
7.        return UniMybatisJacksonUtil.toJson(params);
8.    } catch (Exception var3) {
9.        var3.printStackTrace();
10.        return "{}";
11.    }
12. }

复制代码

• 单独更新字段时，处理待更新对象
  

1. updateWrapper.setSql(String.format(setSql, dto.getAttrKey(), UniMybatisJacksonUtil.toJson(dto.getValue())));

复制代码

[留意]  Java实体类中map的value是一个对象，留意在入库前不要把value序列化成String，否则会导致入库的值带有转义符；如果有逻辑要求必须序列化，在入库之前再额外执行一次反序列化成对象。

1. if (JSONUtil.isTypeJSON(updateAttrs.get(key))) {
2.    //如果value已经是JSON格式，反序列化成对象
3.    attrDTO.setValue(JSON.parse(updateAttrs.get(key)));
4. }

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。