mysql中 多选项的json存储及查询

这个表单是网站的一些调查问卷中的多选项，如果查询的话，怎么做最简易？

首先是将大类与小类拆开：

CREATE TABLE `t_base_social_ability` (
   `id` char(19) NOT NULL COMMENT '主键id',
   `ability_type` varchar(2) NOT NULL DEFAULT '' COMMENT '能力类型字典项',
   `ability_detail` json COMMENT '能力详情',
   PRIMARY KEY (`id`) USING BTREE
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='特长表'

 插入测试数据，查询

 查询数据，同时包含 3，13的数据：

SELECT * FROM t_base_social_ability 
WHERE json_contains(ability_detail,json_array('3','13'));

查询数据，3 或13的数据
 

SELECT * FROM t_base_social_ability 
WHERE json_contains(json_array('3','13'),ability_detail) AND JSON_LENGTH(ability_detail)>0;

mysql中json字段的用法：

@TableName(value = "t_base_social_ability",autoResultMap = true)
@Data
public class SocialAbility{
@TableField(value="ability_detail", typeHandler= JacksonTypeHandler.class)
private List<Integer> abilityDetail;
}

FastJsonArrayTypeHandler

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.serializer.SerializerFeature;
import com.baomidou.mybatisplus.extension.handlers.AbstractJsonTypeHandler;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;

import java.util.List;

@Slf4j
@MappedJdbcTypes(value = JdbcType.JAVA_OBJECT)
public class FastJsonArrayTypeHandler<T> extends AbstractJsonTypeHandler<Object> {
    private Class<T> type;
    public FastJsonArrayTypeHandler(Class<T> type) {
        this.type = type;
    }

    @Override
    protected List<T> parse(String json) {
        return JSON.parseArray(json, type);
    }

    @Override
    protected String toJson(Object obj) {
        return JSON.toJSONString(obj, SerializerFeature.WriteMapNullValue, SerializerFeature.WriteNullListAsEmpty, SerializerFeature.WriteNullStringAsEmpty);
    }
}

如果json存储得是实体集合List

<result property="taskLabel" column="task_label" 
    typeHandler="com.swan.task.center.dao.common.config.FastJsonArrayTypeHandler"
    javaType="com.swan.task.center.api.task.param.TaskLabelParam"/>

-- 对json中的字段进行统计

SELECT t1.label AS labelName,COUNT(1) AS sumTask FROM t_task_info, 
JSON_TABLE(task_label, "$[*]" COLUMNS ( label VARCHAR(30) PATH '$.label') ) t1
WHERE t1.label LIKE CONCAT ('%', '任务', '%')
GROUP BY t1.label   

 

json类型查询， 基本类型，对象，数组。

1.模糊查询json类型字段
存储的数据格式（字段名 people_json）：

{“name”: “zhangsan”, “age”: “13”, “gender”: “男”}

代码如下（示例）：

select * from table_name  where people_json->'$.name' like '%zhang%'
1
2.精确查询json类型字段
存储的数据格式（字段名 people_json）：

{“name”: “zhangsan”, “age”: “13”, “gender”: “男”}

代码如下（示例）：

select * from table_name  where people_json-> '$.age' = 13
1
3.模糊查询JsonArray类型字段
存储的数据格式（字段名 people_json）：

[{“name”: “zhangsan”, “age”: “13”, “gender”: “男”}]

代码如下（示例）：

select * from table_name  where people_json->'$[*].name' like '%zhang%'
1
4.精确查询JsonArray类型字段
存储的数据格式（字段名 people_json）：

[{“name”: “zhangsan”, “age”: “13”, “gender”: “男”}]

代码如下（示例）：

select * from table_name  where JSON_CONTAINS(people_json,JSON_OBJECT('age', "13"))