赞
踩
###本文md文件下载地址
https://download.csdn.net/download/a254939392/89492142
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;
插件非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撒旦士大夫\"}]}' );
-- column->path 语法糖,在实际使用的时候都会在底层自动转化为JSON_EXTRACT
-- column->path 等同于 JSON_EXTRACT(column, path) ,只能指定一个path。
-- select JSON_EXTRACT(txt,'$.activityName') from my_test == select txt->'$."activityName"' from my_test
SELECT * 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
json转换
本文采用最新fastjson2,目前mybatisplus并未支持fastjson2转换,所以需要自定义转换
//自定义fastjson2 @TableField(typeHandler = Fastjson2TypeHandler.class) //mybatisplus 自带fastjosn直接使用 @TableField(typeHandler = FastjsonTypeHandler.class) //mybatisplus 自带Jackson直接使用 @TableField(typeHandler = JacksonTypeHandler.class)
- 1
- 2
- 3
- 4
- 5
- 6
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; } @Override protected Object parse(String json) { return JSON.parseObject(json, type); } @Override protected 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 { /** * 主键 */ @TableId private 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 { /** * 主键 */ @TableId private Long id; /** * 名称 */ private String activityName; }
@Resource private 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);
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)
虚拟列:目前mybiteplus 只能使用动态sql 执行查询。实体无法映射字段的
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。