踩坑MybatisPlus映射JSON字段

了解Mysql中text和json数据格式

在MySQL中，TEXT 和 JSON 是两种不同的数据类型，它们的主要区别在于存储的内容和使用方式。

1. TEXT 类型

• 存储内容：TEXT 类型用于存储纯文本数据，可以是任意长度的字符串。
• 用途：适用于存储大段的文本内容，如文章、日志等。
• 操作：TEXT 类型的数据可以通过字符串函数进行操作，如 SUBSTRING、CONCAT 等。
• 索引：TEXT 类型的列不能直接创建索引，但可以创建前缀索引。

2. JSON 类型

• 存储内容：JSON 类型用于存储 JSON 格式的数据，JSON 是一种轻量级的数据交换格式，支持复杂的数据结构，如对象、数组等。
• 用途：适用于存储结构化数据，如配置信息、API 响应等。
• 操作：JSON 类型的数据可以通过专门的 JSON 函数进行操作，如 JSON_EXTRACT、

JSON_SET、JSON_ARRAY 等。

• 索引：JSON 类型的列可以创建虚拟列，并在虚拟列上创建索引，以提高查询性能。

JSON 类型的使用示例

创建表

▼
sql
复制代码
CREATE TABLE example (
  id INT PRIMARY KEY,
  data JSON
);

插入 JSON 数据

▼
sql
复制代码
INSERT INTO example (id, data) 
VALUES (1, '{"name": "Alice", "age": 25, "hobbies": ["reading", "traveling"]}');

查询 JSON 数据

▼
sql
复制代码
-- 查询整个 JSON 字段
SELECT data FROM example WHERE id = 1;

-- 查询 JSON 字段中的某个键值
SELECT JSON_EXTRACT(data, '$.name') AS name FROM example WHERE id = 1;

-- 查询 JSON 字段中的数组
SELECT JSON_EXTRACT(data, '$.hobbies') AS hobbies FROM example WHERE id = 1;

更新 JSON 数据

▼
plsql
复制代码
-- 更新 JSON 字段中的某个键值
UPDATE example SET data = JSON_SET(data, '$.age', 26) WHERE id = 1;
-- 向 JSON 字段中的数组添加元素
UPDATE example SET data = JSON_ARRAY_APPEND(data, '$.hobbies', 'cooking') WHERE id = 1;

创建虚拟列并创建索引

▼
sql
复制代码
-- 创建虚拟列
ALTER TABLE example ADD COLUMN name VARCHAR(255) 
AS (JSON_UNQUOTE(JSON_EXTRACT(data, '$.name')) VIRTUAL;
-- 在虚拟列上创建索引
CREATE INDEX idx_name ON example (name);

补充：

MySQL 中，虚拟列（Generated Column） 是一种特殊的列，它的值是通过表达式计算得出的，而不是直接存储数据。虚拟列可以分为两种类型：

1. VIRTUAL：虚拟列的值在查询时动态计算，不占用存储空间。
2. STORED：虚拟列的值在插入或更新时计算并存储，占用存储空间。

虚拟列的主要用途是简化查询、优化性能以及支持对 JSON 或其他复杂数据类型的索引。以下是虚拟列的优缺点：

优点

1. 简化查询

• 虚拟列可以将复杂的计算逻辑封装在列定义中，查询时可以直接使用虚拟列，而不需要重复编写复杂的表达式。
• 例如，从 JSON 数据中提取某个字段并创建虚拟列后，可以直接查询虚拟列，而不需要每次都使用 JSON_EXTRACT。

2. 支持索引

• 虚拟列可以创建索引，从而显著提高查询性能。
• 对于 JSON 数据类型，MySQL 不支持直接对 JSON 字段创建索引，但可以通过虚拟列提取 JSON 中的某个字段并创建索引。

3. 数据一致性

• 虚拟列的值是通过表达式计算得出的，因此可以保证数据的一致性。
• 例如，如果虚拟列是基于其他列计算的，那么当基础列的值发生变化时，虚拟列的值会自动更新。

4. 节省存储空间（VIRTUAL 类型）

• VIRTUAL 类型的虚拟列不存储实际数据，只在查询时动态计算，因此不会占用额外的存储空间。

5. 灵活性

• 虚拟列可以基于复杂的表达式创建，支持数学运算、字符串操作、JSON 函数等。

缺点

1. 性能开销（VIRTUAL 类型）virtual

• VIRTUAL 类型的虚拟列在每次查询时都需要动态计算，如果表达式复杂或数据量较大，可能会影响查询性能。
• 对于频繁查询的场景，STORED 类型的虚拟列可能更适合，因为它会预先计算并存储结果。

2. 存储开销（STORED 类型）stored

• STORED 类型的虚拟列会占用存储空间，因为它的值是预先计算并存储的。
• 如果虚拟列的数据量较大，可能会增加表的存储需求。

3. 不支持所有表达式

• 虚拟列的表达式有一些限制，例如不能使用子查询、存储过程、用户定义函数等。
• 某些复杂的计算可能无法通过虚拟列实现。

4. 维护复杂性

• 如果虚拟列的表达式依赖于其他列，那么当表结构发生变化时（例如修改列名或删除列），可能需要同步更新虚拟列的定义。
• 虚拟列的表达式逻辑需要谨慎设计，避免出现性能瓶颈或逻辑错误。

5. 兼容性

• 虚拟列是 MySQL 5.7 及以上版本引入的功能，旧版本的 MySQL 不支持虚拟列。

使用场景

1. JSON 数据索引

• 从 JSON 字段中提取某个键值并创建虚拟列，然后在该列上创建索引。

例如：

▼
sql
复制代码
ALTER TABLE example ADD COLUMN name VARCHAR(255) 
AS (JSON_UNQUOTE(JSON_EXTRACT(data, '$.name'))) VIRTUAL;
CREATE INDEX idx_name ON example (name);

2. 计算字段

例如，基于价格和数量计算总金额：

▼
sql
复制代码
ALTER TABLE orders ADD COLUMN total_amount DECIMAL(10, 2) 
AS (price * quantity) STORED;

3. 数据格式化

例如，将日期字段格式化为字符串：

▼
sql
复制代码
ALTER TABLE events ADD COLUMN event_date_str VARCHAR(10)
AS (DATE_FORMAT(event_date, '%Y-%m-%d')) VIRTUAL;

总结

根据具体需求选择合适的虚拟列类型，可以显著提升数据库的性能和灵活性。

总结

• TEXT 类型适合存储纯文本数据，而 JSON 类型适合存储结构化数据。
• JSON 类型提供了丰富的函数来操作 JSON 数据，并且可以通过虚拟列和索引来优化查询性能。

MybatisPlus如何使用JSON类型

数据库实体

引入依赖

▼
xml
复制代码
<dependency>
  <groupId>com.baomidou</groupId>
  <artifactId>mybatis-plus-boot-starter</artifactId>
  <version>3.5.2</version>
</dependency>
<dependency>
  <groupId>com.mysql</groupId>
  <artifactId>mysql-connector-j</artifactId>
  <version>8.0.33</version>
</dependency>

创建实体

▼
java
复制代码
@Data
// 注意这里一定要开启注解映射
@TableName(autoResultMap = true)
public class CaseInfo implements Serializable {
    private static final long serialVersionUID = 1L;
    /**
     * 配置id
     */
    @TableId(type = IdType.ASSIGN_ID)
    private Long id;

    /**
     * 房间id
     */
    @TableField("roomId")
    private Long roomId;

    /**
     * 配置
     */
    // 配置类型处理器可以自己写，也可以用其他的 JacksonTypeHandler.class 看自己
    // 如果没有在类开头添加注解映射 ，当然你也可以用显示注解 看自己
    @TableField(value = "caseInfo",typeHandler = HutoolJsonTypeHandler.class)
    @TableField(typeHandler = HutoolJsonTypeHandler.class)
    private CaseInfos caseInfo;
}

▼
java
复制代码
@Data
public class CaseInfos implements Serializable {
private static final long serialVersionUID = 1L;
    private String size;
    private String tag;
}

自己实现类型处理器

▼
java
复制代码
public class HutoolJsonTypeHandler extends AbstractJsonTypeHandler<CaseInfos> {

    @Override
    public CaseInfos parse(String json) {
        // 将JSON字符串转换为CaseInfo对象
        if (json == null || json.isEmpty()) {
            return null;
        }
        try {
            return JSONUtil.toBean(json, CaseInfos.class);
        } catch (Exception e) {
            System.err.println("JSON解析异常: " + e.getMessage());
            return null;
        }
    }

    @Override
    public String toJson(CaseInfos obj) {
        // 将CaseInfo对象转换为JSON字符串
        if (obj == null) {
            return null;
        }
        try {
            return JSONUtil.toJsonStr(obj);
        } catch (Exception e) {
            System.err.println("JSON转换异常: " + e.getMessage());
            return null;
        }
    }
}

增删改查

▼
java
复制代码
@SpringBootTest
class CaseServiceTest {

    @Resource
    private CaseService caseService;

    // 生成增删改查
    @Test
    void save() {
        CaseInfo aCase = new CaseInfo();
        aCase.setRoomId(1L);
        CaseInfos caseInfo = new CaseInfos();
        caseInfo.setSize("big");
        caseInfo.setTag("恐怖");
        aCase.setCaseInfo(caseInfo);
        caseService.save(aCase);
    }

    @Test
    public void updateById() {
        Long caseId = 1902930311949942786L;
        String newSizeValue = "big";

        // 使用 LambdaUpdateWrapper 和 MySQL JSON 函数更新单个字段
        LambdaUpdateWrapper<CaseInfo> updateWrapper = new LambdaUpdateWrapper<>();
        updateWrapper.eq(CaseInfo::getId, caseId)
        .setSql(String.format("caseInfo = JSON_SET(caseInfo, '$.size', '%s')", newSizeValue));
        // 执行更新操作
        boolean updateResult = caseService.update(updateWrapper);

        if (updateResult) {
            System.out.println("Update successful.");
        } else {
            System.out.println("Update failed.");
        }
    }

    /**
     * 更新JSON对象中的单个字段
     * 示例1：更新size字段
     */
    @Test
    public void updateJsonField_Size() {
        // 1. 指定要更新的记录ID
        Long caseId = 1902930311949942786L;
        // 2. 设置新的size值
        String newSizeValue = "medium";

        // 3. 使用LambdaUpdateWrapper和MySQL的JSON_SET函数
        LambdaUpdateWrapper<CaseInfo> updateWrapper = new LambdaUpdateWrapper<>();
        updateWrapper.eq(CaseInfo::getId, caseId)
        .setSql(String.format("caseInfo = JSON_SET(caseInfo, '$.size', '%s')", newSizeValue));

        // 4. 执行更新操作
        boolean updateResult = caseService.update(updateWrapper);

        System.out.println("更新size字段 " + (updateResult ? "成功" : "失败"));

        // 5. 验证更新结果
        if (updateResult) {
            CaseInfo updatedCase = caseService.getById(caseId);
            System.out.println("更新后的记录: " + updatedCase);
            System.out.println("size字段新值: " + (updatedCase.getCaseInfo() != null ? updatedCase.getCaseInfo().getSize() : "null"));
        }
    }

    /**
     * 使用MySQL ->> 操作符更新JSON字段
     * 这是一种更简洁的方式
     */
    @Test
    public void updateJsonField_UsingArrowOperator() {
        // 1. 指定要更新的记录ID
        Long caseId = 1902930311949942786L;
        // 2. 设置新的值
        String newSizeValue = "超大";

        // 3. 使用LambdaUpdateWrapper和MySQL的 ->> 操作符
        LambdaUpdateWrapper<CaseInfo> updateWrapper = new LambdaUpdateWrapper<>();
        updateWrapper.eq(CaseInfo::getId, caseId)
        .setSql("JSON_SET(caseInfo, '$.size', '" + newSizeValue + "') -> '$'");

        // 4. 执行更新操作
        boolean updateResult = caseService.update(null, updateWrapper);

        System.out.println("使用 ->> 操作符更新字段 " + (updateResult ? "成功" : "失败"));

        // 5. 验证更新结果
        if (updateResult) {
            CaseInfo updatedCase = caseService.getById(caseId);
            System.out.println("更新后的记录: " + updatedCase);
            System.out.println("size字段新值: " + (updatedCase.getCaseInfo() != null ? updatedCase.getCaseInfo().getSize() : "null"));
        }
    }

    /**
     * 查询JSON字段中特定值的记录
     * 使用MySQL ->> 操作符
     */
    @Test
    public void queryUsingJsonOperator() {
        // 使用原生SQL进行查询
        String sql = "SELECT * FROM case_info WHERE caseInfo->>'$.size' = 'medium'";
        System.out.println("执行SQL: " + sql);

        // 获取所有记录并过滤
        List<CaseInfo> allCases = caseService.list();
        System.out.println("找到符合条件的记录:");

        for (CaseInfo aCase : allCases) {
            if (aCase.getCaseInfo() != null && "medium".equals(aCase.getCaseInfo().getSize())) {
                System.out.println(aCase);
            }
        }
    }

    /**
     * 更新JSON对象中的单个字段
     * 示例2：更新tag字段
     */
    @Test
    public void updateJsonField_Tag() {
        // 1. 指定要更新的记录ID
        Long caseId = 1902930311949942786L;
        // 2. 设置新的tag值
        String newTagValue = "科幻";

        // 3. 使用LambdaUpdateWrapper和MySQL的JSON_SET函数
        LambdaUpdateWrapper<CaseInfo> updateWrapper = new LambdaUpdateWrapper<>();
        updateWrapper.eq(CaseInfo::getId, caseId)
                .setSql(String.format("caseInfo = JSON_SET(caseInfo, '$.tag', '%s')", newTagValue));

        // 4. 执行更新操作
        boolean updateResult = caseService.update(updateWrapper);

        System.out.println("更新tag字段 " + (updateResult ? "成功" : "失败"));

        // 5. 验证更新结果
        if (updateResult) {
            CaseInfo updatedCase = caseService.getById(caseId);
            System.out.println("更新后的记录: " + updatedCase);
            System.out.println("tag字段新值: " + (updatedCase.getCaseInfo() != null ? updatedCase.getCaseInfo().getTag() : "null"));
        }
    }

    /**
     * 同时更新JSON对象中的多个字段
     */
    @Test
    public void updateMultipleJsonFields() {
        // 1. 指定要更新的记录ID
        Long caseId = 1902930311949942786L;
        // 2. 设置新的值
        String newSizeValue = "small";
        String newTagValue = "推理";

        // 3. 使用LambdaUpdateWrapper和MySQL的JSON_SET函数同时更新多个字段
        LambdaUpdateWrapper<CaseInfo> updateWrapper = new LambdaUpdateWrapper<>();
        updateWrapper.eq(CaseInfo::getId, caseId)
                .setSql(String.format("caseInfo = JSON_SET(caseInfo, '$.size', '%s', '$.tag', '%s')",
                        newSizeValue, newTagValue));

        // 4. 执行更新操作
        boolean updateResult = caseService.update(updateWrapper);

        System.out.println("同时更新多个字段 " + (updateResult ? "成功" : "失败"));

        // 5. 验证更新结果
        if (updateResult) {
            CaseInfo updatedCase = caseService.getById(caseId);
            System.out.println("更新后的记录: " + updatedCase);
            if (updatedCase.getCaseInfo() != null) {
                System.out.println("size字段新值: " + updatedCase.getCaseInfo().getSize());
                System.out.println("tag字段新值: " + updatedCase.getCaseInfo().getTag());
            }
        }
    }

    @Test
    void removeById() {
        caseService.removeById(1L);
    }

    @Test
    void list() {
        List<CaseInfo> list = caseService.list();
        for (CaseInfo aCase : list) {
            System.out.println(aCase);
        }
    }

}

到这你就应该学会了该如何在OJ项目中使用JSON格式来处理判题信息等等JSON类型的字段了把

如果还有问题

那你有没好好看我的文档

回答我！！！ look in my eyes tell me why why baby why