mysql json类型

参见 https://www.bilibili.com/video/BV1LD4y1m7Ej?from=search&seid=11398812751163482789

表

CREATE TABLE `t_json` (
`id` int NOT NULL AUTO_INCREMENT,
`json` json DEFAULT NULL,
`jarray` json DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

 

数据

 

 查询

select sum(json->'$.oilFee') +sum(json->'$.partFee') + IFNULL(sum(json->'$.paratFee'),0), json->'$.remark' from t_json where json->'$.remark' is null; // 查json字段 ->'$.字段名'

select * from t_json where 1 member of (jarray) // 对json数组进行查找

select * from t_json where JSON_CONTAINS_PATH(json, 'all', '$.remark') // 查询json数据中存在remark键的数据. 其中all是条件,可以是one或all

update t_json set json = '{\"test\": \"test\"}' where id = 1;

 

 

springboot操作:见:https://blog.csdn.net/yelangkingwuzuhu/article/details/109943552

 

如果使用苞米豆的查询只需在映射类中加上如下注解即可

映射类:

@Data
@TableName(autoResultMap = true, value = "t_json")
public class TestJson implements Serializable{

    private static final long serialVersionUID = 1L;
    @TableId(type = IdType.AUTO)
    private Integer id;
    
    @TableField(value = "jarray",typeHandler = FastjsonTypeHandler.class)
    private List<Integer> ids;
    
    @TableField(value = "json",typeHandler = FastjsonTypeHandler.class)
    private Map<String,Object> map ;
}

 

 

如果是自己写sql通过xml的形式,需要使用

<resultMap id="BaseResultMap"  type="com.test3.model.TestJson" >
        <id column="id" property="id"/>
        <result column="jarray" property="ids"
                javaType="java.util.List"
                typeHandler="com.baomidou.mybatisplus.extension.handlers.FastjsonTypeHandler"/>
        <result column="json" property="map"
                javaType="java.util.Map"
                typeHandler="com.baomidou.mybatisplus.extension.handlers.FastjsonTypeHandler"/>
    </resultMap>

<select id="selectList2" resultMap="BaseResultMap" parameterType="java.lang.String">
　　select * from t_json where json->'$.title' like CONCAT('%',#{keyword},'%')
</select>

 

 

----------------------mysql优化------------------

show variables like max_connections

set GLOBAL max_connections=1000

show variables like max_allowed_packet 

set global max_allowed_packet =  40M

 参见 参见 http://t.zoukankan.com/liulaolaiu-p-11744272.html