网站框架类型,食品网站建设目的,流量宝做网站流量,家具全屋定制Mysql JSON 类型分享
Mysql json字段了解#xff1a;
MySQL 中的 JSON 类型是一种用于存储和处理 JSON#xff08;JavaScript Object Notation#xff09;数据的数据类型。JSON 是一种轻量级的数据交换格式#xff0c;常用于表示结构化的数据。MySQL 的 JSON 类型提供了以…Mysql JSON 类型分享
Mysql json字段了解
MySQL 中的 JSON 类型是一种用于存储和处理 JSONJavaScript 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 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci ROW_FORMATDYNAMIC; 插入数据
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 AbstractObjectTypeHandlerT extends BaseTypeHandlerT { */ * * *定义设置参数时该如何把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; } ClassT clazz (ClassT) getRawType(); if (CharUtil.BRACKET_START* data.charAt(0)) { return JSONArray.parseObject(data, clazz); } else { return JSONObject.parseObject(data, clazz); } }
这是一个公共类其他如果有需求继承这个类就行了
public class FileDetailInfoTypeHandler extends AbstractObjectTypeHandlerFileDetailInfo {
}
注意我们重新需要在配置中定义mybatis_plus扫描的位置让其生效
#mybatis—plus *自定handler的扫描路径 *mybatis-plus: type-handlers-package: com.nala.test.handler
