springboot笔记示例七：mybiteplus框架mysql8新类型json集成

mysql8json_0">springboot笔记示例七：mysql8新类型json集成

###本文md文件下载地址

https://download.csdn.net/download/a254939392/89492142

md文件下载

建表SQL

CREATE TABLE `my_test` (`id` int unsigned NOT NULL AUTO_INCREMENT,`txt` json DEFAULT NULL,`txt_array` json DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

insert语句

插件非json插入时会自动校验格式会报错，{}或者[] [{}] 是被允许的。

对于 JSON 文档，KEY 名不能重复。

如果插入的值中存在重复 KEY，在 MySQL 8.0.3 之前，遵循 first duplicate key wins 原则，会保留第一个 KEY，后面的将被丢弃掉。从 MySQL 8.0.3 开始，遵循的是 last duplicate key wins 原则，只会保留最后一个 KEY。

INSERT INTO `my_test` ( `txt`, `txt_array` )VALUES( '{}', '[{}]' );
INSERT INTO `my_test` ( `txt`, `txt_array` )VALUES( '{}', '[]' );INSERT INTO `my_test` ( `txt`, `txt_array` ) VALUES( '{\"id\": 1, \"signUp\": 0, \"activityName\": \"撒旦士大夫\"}', NULL );
INSERT INTO `my_test` ( `txt`, `txt_array` )
VALUES ( '{\"id\": 1, \"signUp\": 0, \"activityName\": \"撒旦士大夫\"}', '{\"list\": [{\"id\": 1, \"signUp\": 0, \"activityName\": \"撒旦士大夫\"}, {\"id\": 2, \"signUp\": 0, \"activityName\": \"222撒旦士大夫\"}]}' );

select语句

-- column->path 语法糖，在实际使用的时候都会在底层自动转化为JSON_EXTRACT
-- column->path 等同于 JSON_EXTRACT(column, path) ，只能指定一个path。
--  select JSON_EXTRACT(txt,'$.activityName') from my_test == select txt->'$."activityName"' from my_testSELECT * FROM my_test WHERE txt->'$."activityName"' = '撒旦士大夫';
SELECT * FROM my_test WHERE txt->'$."activityName"' = '撒旦士大夫' and  txt->'$."type"' = 1
SELECT id,txt,txt_array FROM my_test WHERE txt -> '$.activityName' LIKE CONCAT('%','大夫','%') AND txt -> '$.type' = 1
SELECT * FROM my_test WHERE txt->'$."id"' = 1;
SELECT id, txt -> '$.*' AS nam11e FROM my_test

mybiteplus集成

json转换

本文采用最新fastjson2，目前mybatisplus并未支持fastjson2转换，所以需要自定义转换

//自定义fastjson2
@TableField(typeHandler = Fastjson2TypeHandler.class)
//mybatisplus 自带fastjosn直接使用
@TableField(typeHandler = FastjsonTypeHandler.class)
//mybatisplus 自带Jackson直接使用
@TableField(typeHandler = JacksonTypeHandler.class)

package com.config;import com.alibaba.fastjson2.JSON;
import com.baomidou.mybatisplus.core.toolkit.Assert;
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 org.apache.ibatis.type.MappedTypes;
/*** @Title: Fastjson2 实现 JSON 字段类型处理器* @Description: fastjson2转换类* @author: lihainan* @date: 2023年12月26日 下午1:22:04* @version: V1.0* @Copyright: nit逆天开源版权*/
@Slf4j
@MappedTypes({Object.class})
@MappedJdbcTypes(JdbcType.VARCHAR)
public class Fastjson2TypeHandler extends AbstractJsonTypeHandler<Object> {private final Class<?> type;public Fastjson2TypeHandler(Class<?> type) {if (log.isTraceEnabled()) {log.trace("FastjsonTypeHandler(" + type + ")");}Assert.notNull(type, "Type argument cannot be null");this.type = type;}@Overrideprotected Object parse(String json) {return JSON.parseObject(json, type);}@Overrideprotected String toJson(Object obj) {return JSON.toJSONString(obj);}
}

entity定义

package com.entity;import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.config.Fastjson2TypeHandler;
import com.dto.business.ActivityList;
import lombok.Data;
import java.io.Serializable;
@Data
@TableName(value = "my_test", autoResultMap = true)//autoResultMap = true 这个必须有哦
public class MyTest implements Serializable {/*** 主键*/@TableIdprivate Long id;/*** json类型转换为javabean*/@TableField(typeHandler = JacksonTypeHandler.class)private Activity txt;/*** json数组类型转换为javabean*/@TableField(typeHandler = Fastjson2TypeHandler.class)private List<Activity> txtArray;
}@Data
public class Activity implements Serializable {/*** 主键*/@TableIdprivate Long id;/*** 名称*/private String activityName;
}

curd

 	@Resourceprivate MyTestDao myTestDao;@GetMapping("/insert")public void insert() {Activity node = new Activity();node.setActivityName("撒旦士大夫");Activity node2 = new Activity();node2.setActivityName("222撒旦士大夫");List<Activity> list = Lists.newArrayList();list.add(node);list.add(node2);MyTest myTest = new MyTest();myTest.setTxt(node);myTest.setTxtArray(list);myTestDao.insert(myTest);}

select

MyTest test = new MyTest();
Activity node = new Activity();
node.setActivityName("大夫");
node.setType(1);
test.setTxt(node);LambdaQueryWrapper<MyTest> queryWrapper = Wrappers.lambdaQuery();
queryWrapper.apply(Objects.nonNull(test.getTxt()), "txt -> '$.activityName' LIKE CONCAT('%',{0},'%')", test.getTxt().getActivityName())//模糊查询.apply(Objects.nonNull(test.getTxt().getType()), "txt -> '$.type' = {0}", test.getTxt().getType());//精确查询List<MyTest> res = myTestDao.selectList(queryWrapper);

json_190">json索引优化

mysql官方说明地址：https://dev.mysql.com/doc/refman/8.0/en/create-table-secondary-indexes.html

json类型，无法直接创建索引，需要创建二级索引，创建虚拟列实现索引需求，

CREATE TABLE `my_test` (`id` int unsigned NOT NULL AUTO_INCREMENT,`txt` json DEFAULT NULL,`txt_array` json DEFAULT NULL,-- 虚拟列 需要设置表达式`js_id` int GENERATED ALWAYS AS (json_extract(`txt`,_utf8mb4'$.id')) VIRTUAL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=357 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;-- 增加虚拟列
ALTER TABLE `brm_pro_test`.`my_test` 
ADD COLUMN `sd` varchar AS (json_extract(`txt`,_utf8mb4'$.activityName')) VIRTUAL NULL 
-- 创建索引
ADD INDEX `index_key_id`(`js_id` ASC) USING BTREE;-- 使用虚拟列查询
explain
SELECT * FROM my_test WHERE js_id = 1;
+----+-------------+---------+------------+------+---------------+--------------+---------+------
| id | select_type | table   | partitions | type | possible_keys | key          | key_len | ref   | rows | filtered | Extra |
|  1 | SIMPLE      | my_test | NULL       | ref  | index_key_id  | index_key_id | 5       | const |  1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+--------------+---------+--------- 无索引
mysql> explain SELECT * FROM my_test WHERE JSON_EXTRACT(txt,'$[1]') = 1;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+
|  1 | SIMPLE      | my_test | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  356 |   100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+-
1 row in set (0.17 sec)