Mysql JSON 类型分享
Mysql json字段了解：
MySQL 中的 JSON 类型是一种用于存储和处理 JSON（JavaScript Object Notation）数据的数据类型。JSON 是一种轻量级的数据交换格式，常用于表示结构化的数据。MySQL 的 JSON 类型提供了以下几个用处和好处：
-
存储和查询复杂的数据结构：JSON 类型允许你将复杂的数据结构以 JSON 格式存储在数据库中，例如嵌套的对象、数组等。这使得存储和查询具有复杂结构的数据变得更加方便和灵活。
-
简化数据模型：使用 JSON 类型可以将多个相关的属性组合成一个 JSON 对象进行存储，而不需要创建多个表和关联关系。这样可以简化数据模型，减少表的数量，提高数据的可读性和维护性。
-
动态模式：JSON 类型允许你在不改变表结构的情况下，动态地添加、删除或修改 JSON 对象中的属性。这对于需要频繁变化的数据模型非常有用，避免了频繁的表结构修改。
-
简化应用层逻辑：通过使用 JSON 类型，你可以将复杂的数据结构直接存储在数据库中，而不需要在应用层进行手动的序列化和反序列化操作。这简化了应用程序的逻辑，减少了开发和维护的工作量。
-
快速查询和索引：MySQL 提供了一些针对 JSON 类型的查询函数和操作符，使得对 JSON 数据进行检索和过滤变得更加高效。此外，你还可以为 JSON 字段创建索引，提高查询性能。
JSON 类型在 MySQL 中的支持从版本 5.7.8 开始引入。在此之前的版本中，可以使用字符串类型（如 VARCHAR）来存储 JSON 数据，但没有专门的 JSON 类型和相关的 JSON 函数。
从 MySQL 5.7.8 开始，你可以使用 JSON 类型来存储和操作 JSON 数据。JSON 类型提供了一些内置的函数和操作符，用于处理 JSON 数据，例如提取、修改和查询 JSON 字段中的数据。
总的来说，MySQL 的 JSON 类型提供了一种灵活、方便和高效地存储和处理复杂结构的数据的方式，简化了数据模型和应用层逻辑，并提高了查询性能。
一、创建表，以及插入数据
建表：
CREATE TABLE file_detail ( file_id varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '文件编号', info json DEFAULT NULL COMMENT '文件详情', describ varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '文件描述', create_time datetime DEFAULT NULL COMMENT '创建时间', update_time datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', finalize char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci GENERATED ALWAYS AS (ifnull(json_unquote(json_extract(info,utf8mb4'$.finalize')),utf8mb4'')) VIRTUAL, PRIMARY KEY (file_id) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;
插入数据：
INSERT INTO file_detail (file_id,info,describ,create_time,update_time) VALUES ( 'test1', JSON_OBJECT( 'tag_array',JSON_ARRAY(JSON_OBJECT('tag_id','1','tag_name','双十一'),JSON_OBJECT('tag_id','2','tag_name','双十二')), 'brand_array',JSON_ARRAY(JSON_OBJECT('brand_id','1','brand_name','roopy')), 'ocr_array',JSON_ARRAY('roopy','护手霜'),
'link','www.roopy.com',
'finalize','Y' ), '润培双十一', NOW(),NOW() );
二：JSON类型的常用函数
插入数据表的JSON函数：
-
JSON_ARRAY JSON数组
-
JSON_OBJECT JSON对象
JSON类型的查询函数：
1.JSON_EXTRACT：用于从 JSON 值中提取指定路径的数据。
语法：
JSON_EXTRACT(json_column, path)
示例：
SELECT JSON_EXTRACT(info, "$.link") as link FROM file_detail WHERE file_id = 'test11';
在上面的示例中，假设你有一个名为 my_table 的表，其中包含一个名为 data 的 JSON 列。JSON_EXTRACT 函数用于从 data 列中提取 name 属性的值。
但是这个获取的值是json的值，还有双引号。
2.JSON_UNQUOTE：去除双引号，一般和JSON_EXTRACT 配合使用
实例：
SELECT JSON_UNQUOTE(JSON_EXTRACT(info, "$.link")) as link FROM file_detail WHERE file_id = 'test11';
MySQL 还提供了 ->> 表达式，使用该表达式可以实现与JSON_UNQUOTE(JSON_EXTRACT相同的功能需求
示例：
SELECT info ->> '$.link' AS link FROM file_detail WHERE file_id = 'test11';
3.JSON_CONTAINS：用于检查 JSON 值是否包含指定的键或值。
语法：
JSON_CONTAINS(json_column, value, path)
示例：
SELECT * FROM file_detail WHERE JSON_CONTAINS(info,'"护手霜"','$.ocr_array');
查询所有文字识别中包含护手霜的文件详情
4.JSON_SEARCH：用于查找 JSON 值中指定键或值的路径。
JSON_SEARCH(json_doc, one_or_all, search_str, escape_char, path)
参数解释如下：
-
json_doc：要搜索的 JSON 文档或 JSON 字符串。 -
one_or_all
：指定搜索模式，可以是以下两个值之一：
-
'one'：返回第一个匹配项的路径。 -
'all'：返回所有匹配项的路径，以逗号分隔。
-
-
search_str：要搜索的值，可以是字符串、数字、布尔值等。 -
escape_char（可选）：用于转义搜索字符串中的特殊字符的转义字符。如果不需要转义，则可以将该参数设置为NULL。 -
path（可选）：指定要搜索的 JSON 路径。如果不提供该参数，则将在整个 JSON 文档中进行搜索。
请注意，JSON_SEARCH 函数返回的是一个字符串，表示匹配的路径。如果找不到匹配项，则返回 NULL。
示例：
SELECT JSON_SEARCH(info, 'one','护手霜', null,'$.ocr_array') FROM file_detail WHERE file_id = 'test11';
查找表中id为test11 ocr_array中为护手霜的路径
JSON类型的修改函数：
-
JSON_SET(json_doc, path, val[, path, val]...)：用于在 JSON 文档中设置指定路径的值。它接受多个路径-值对作为参数，并返回更新后的 JSON 文档。如果路径已存在，则更新其对应的值；如果路径不存在，则创建新的路径并设置对应的值。示例：
UPDATE file_detail SET info = JSON_SET(info, '$.link', 'www.hans.com') WHERE file_id = 'test111';
-
JSON_INSERT(json_doc, path, val[, path, val]...)：用于在 JSON 文档中插入指定路径的值。它接受多个路径-值对作为参数，并返回更新后的 JSON 文档。如果路径已存在，则不进行任何操作。示例：
UPDATE file_detail SET info = JSON_INSERT(info, '$.finalize', 'N') WHERE file_id = 'test1'
-
JSON_ARRAY_INSERT:跟上面类似，用于插入数组UPDATE file_detail SET info = JSON_ARRAY_INSERT(info, '$.tag_array[1]', JSON_OBJECT("tag_id","2","tag_name","双十二")) WHERE file_id = 'test111';
4.JSON_REMOVE(json_doc, path[, path]...)：用于从 JSON 文档中移除指定路径的值。它接受一个或多个路径作为参数，并返回更新后的 JSON 文档。
示例：
UPDATE file_detail SET info = JSON_REMOVE(info, '$.tag_array[1]') WHERE file_id = 'test111'
删除掉tag标签中的第二个标签
结合上面的几个函数：
1.修改标签时，更新所有使用该标签的标签名称
UPDATE file_detail SET info =JSON_SET(info,JSON_UNQUOTE(JSON_SEARCH(info, 'one', #{oldName}, NULL, '$.tag_array[].tag_name')),#{newName}) WHERE JSON_SEARCH(info, 'one', #{oldName}, NULL, '$.tag_array[].tag_name') IS NOT NULL
2.查询使用该标签的文件数量
select count() from file_detail where JSON_SEARCH(info, 'one', #{tagId}, NULL, '$.tag_array[].tag_id') IS NOT NULL
三、JSON类型字段结合Mybatis_plus
domain类：
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
public class FileDetail implements Serializable {
private static final long serialVersionUID = 5006880643442005239L;
/**
* 文件编号
*/
@TableId(value = "file_id")
private String fileId;
/**
* 文件详情
*/
// @TableField(typeHandler = FileDetailInfoTypeHandler.class)
@TableField(typeHandler = FastjsonTypeHandler.class)
private FileDetailInfo info;
/**
* 文件描述
*/
private String describ;
/**
* 是否定稿
*/
private String finalize;
/**
* 创建时间
*/
@JsonFormat(
pattern = "yyyy-MM-dd HH:mm:ss"
)
private LocalDateTime createTime;
/**
* 更新时间
*/
@JsonFormat(
pattern = "yyyy-MM-dd HH:mm:ss"
)
private LocalDateTime updateTime;
}
mybatis 本身不支持json的转化，所以我们在mybatis处理的时候手动修改
BaseTypeHandler 是 MyBatis 中的一个抽象类，用于处理数据库字段与 Java 对象之间的类型转换。它包含了以下四个抽象方法：
-
setParameter(PreparedStatement ps, int i, T parameter, JdbcType jdbcType)：将 Java 对象转换为数据库字段，并设置到 PreparedStatement 对象中。其中，ps是 PreparedStatement 对象，i是参数的位置，parameter是要设置的 Java 对象，jdbcType是数据库字段的 JDBC 类型。 -
getResult(ResultSet rs, String columnName)：从 ResultSet 对象中获取指定列名的数据库字段，并将其转换为相应的 Java 对象。返回值为转换后的 Java 对象。 -
getResult(ResultSet rs, int columnIndex)：从 ResultSet 对象中获取指定列索引的数据库字段，并将其转换为相应的 Java 对象。返回值为转换后的 Java 对象。 -
getResult(CallableStatement cs, int columnIndex)：从 CallableStatement 对象中获取指定列索引的数据库字段，并将其转换为相应的 Java 对象。返回值为转换后的 Java 对象。我们可以继承这个类，重写这几个方法来满足一些个性化的需求，比如时间格式转换，类型转换，加密解密，自定义数据格式化（比如我们的fen->yuan）等等
新写一个类，并且重写这四个方法，在特定的字段上使用这个新的Handler：(当然我们可以直接使用mybatis-plus提供的FastJson直接在字段上进行注解：@TableField(typeHandler = FastjsonTypeHandler.class))
import cn.hutool.core.util.CharUtil; import com.alibaba.fastjson.JSON; import com.alibaba.fastjson.JSONArray; import com.alibaba.fastjson.JSONObject; import com.nala.test.domain.FileDetailInfo; import org.apache.commons.lang3.StringUtils; import org.apache.ibatis.type.*; import org.springframework.stereotype.Repository;
import java.sql.CallableStatement; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.Objects;
/** * ** Fastjson TypeHandler**，用于将对象转换为 JSON *字符串 * * @author** *NALA * / *public class AbstractObjectTypeHandler<T> extends BaseTypeHandler<T> { */ * * *定义设置参数时，该如何把Java类型的参数转换为对应的数据库类型 * * * @param** *ps * * @param** *i * * @param** *parameter * * @param** *jdbcType * * @throws** SQLException * **/ * @Override public void setNonNullParameter(PreparedStatement ps, int i, T parameter, JdbcType jdbcType) throws SQLException { if (Objects.nonNull(parameter)) { ps.setString(i, JSON.toJSONString*(parameter)); } }
*/** * * *定义通过字段名称获取字段数据时，如何把数据库类型转换为对应的Java类型 * * * @param** *rs * * @param** *columnName * * @return ** * @throws** *SQLException * **/ * @Override public T getNullableResult(ResultSet rs, String columnName) throws SQLException { return deserialize(rs.getString(columnName)); }
*/** * * *定义通过字段索引获取字段数据时，如何把数据库类型转换为对应的Java类型 * * * @param** *rs * * @param** *columnIndex * * @return ** * @throws** *SQLException * **/ * @Override public T getNullableResult(ResultSet rs, int columnIndex) throws SQLException { return deserialize(rs.getString(columnIndex)); }
*/** * * *定义调用存储过程后，如何把数据库类型转换为对应的Java类型 * * * @param** *cs * * @param** *columnIndex * * @return ** * @throws** *SQLException * **/ * @Override public T getNullableResult(CallableStatement cs, int columnIndex) throws SQLException { return deserialize(cs.getString(columnIndex)); }
*/** * * *数据反序列化 * * * @param** data * * @return ** **/ * private T deserialize(String data) { if (StringUtils.isEmpty(data)) { return null; } Class<T> clazz = (Class<T>) getRawType(); if (CharUtil.BRACKET_START* == data.charAt(0)) { return JSONArray.parseObject(data, clazz); } else { return JSONObject.parseObject(data, clazz); } }
这是一个公共类，其他如果有需求，继承这个类就行了
public class FileDetailInfoTypeHandler extends AbstractObjectTypeHandler<FileDetailInfo> {
}
注意：我们重新需要在配置中定义mybatis_plus扫描的位置让其生效
#mybatis—plus *自定handler的扫描路径 *mybatis-plus: type-handlers-package: com.nala.test.handler
