还在用Text类型? Mysql8.0增强的JSON类型,它不香?

一、前言

MySQL 支持由 RFC 7159 定义的原生JSON 数据类型,该数据类型可以有效访问 JSON(JavaScript Object Notation)中的元素数据。与将JSON 格式的字符串存储为单个字符串类型相比,JSON 数据类型具有以下优势:

  • 自动验证存储在JSON列中的JSON数据格式。无效格式会报错。
  • 优化的存储格式。存储在JSON列中的JSON文档被转换为允许快速读取访问文档元素的内部格式。内部是以二进制格式存储JSON数据。
  • 对JSON文档元素的快速读取访问。当服务器读取JSON文档时,不需要重新解析文本获取该值。通过key或数组索引直接查找子对象或嵌套值,而不需要读取整个JSON文档。
  • 存储JSON文档所需的空间,大致与LONGBLOB或LONGTEXT相同
  • 存储在JSON列中的任何JSON文档的大小都仅限于设置的系统变量max_allowed_packet的值
  • MySQL 8.0.13之前,JSON列不能有非null的默认值。
  • 在 MySQL 8.0 中,优化器可以对 JSON 列执行部分就地更新,而不是删除旧JSON串并将新串完整地写入列。

MYSQL 8.0,除了提供JSON 数据类型,还有一组 SQL 函数可用于操作 JSON 的值,例如创建JSON对象、增删改查JSON数据中的某个元素。

二、常用JSON函数

首先,创建表列时候,列要设置为JSON类型:

CREATE TABLE t1 (content JSON);

插入数据,可以像插入varchar类型的数据一样,把json串添加单引号进行插入:

INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}');

当然mysql也提供了创建JSON对象的函数:

INSERT INTO t1 VALUES(JSON_OBJECT("key1","value1","key2","value2"));

使用JSON_EXTRACT函数查询JSON类型数据中某个元素的值:

mysql> SELECT  JSON_EXTRACT(content,"$.key1") from t1;
+--------------------------------+
| JSON_EXTRACT(content,"$.key1") |
+--------------------------------+
| "value1"                       |
| "value1"                       |
+--------------------------------+
2 rows in set (0.00 sec)

lamba表达式风格查询:

mysql> SELECT content->"$.key1" from t1;
+-------------------+
| content->"$.key1" |
+-------------------+
| "value1"          |
| "value1"          |
+-------------------+
2 rows in set (0.00 sec)

使用JSON_SET函数更新JSON中某个元素的值,如果不存在则添加:

mysql> update t1 set content=JSON_SET(content,"$.key1",'value111');
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

更多JSON类型数据操作函数,可以参考:https://dev.mysql.com/doc/refman/8.0/en/json.html

三、MyBatis中使用JSON类型及其操作函数

比如Device表里面有个JSON类型的content字段,其中含有名称为name的元素,我们来修改和查询name元素对应的值。

ExtMapper中定义修改和查询接口:

@Mapper
public interface DeviceDOExtMapper extends com.zlx.user.dal.mapper.DeviceDOMapper {
    //更新JSON串中名称为name的key的值
    int updateName(@Param("name") String name, @Param("query") DeviceQuery query);
    //查询JSON串中名称为name的key的值
    String selectName(DeviceQuery query);
}

ExtMapper.xml中定义查询sql:

<mapper namespace="com.zlx.user.dal.mapper.ext.DeviceDOExtMapper">

    <!--更新JSON串中名称为name的key的值-->
    <update id="updateName" parameterType="map">
        update device
        <set>
            <if test="name != null">
                content = JSON_SET(content, '$.name', #{name,jdbcType=VARCHAR})
            </if>
        </set>
        <if test="_parameter != null">
            <include refid="Update_By_Example_Where_Clause"/>
        </if>
    </update>

    <!--查询JSON串中名称为name的key的值-->
    <select id="selectName" parameterType="com.zlx.user.dal.model.DeviceQuery" resultType="java.lang.String">
        select
        `content`->'$.name'
        from device
        <if test="_parameter != null">
            <include refid="Example_Where_Clause"/>
        </if>
    </select>
</mapper>

总结

虽然我们实践上不建议把所有扩展字段都放到一个大字段里面。但是即使有原因一定到放,那么也建议选择JSON类型,而不是varcahr和Text类型。

作者:加多,资深Java , 著《Java并发编程之美》 ,《深度剖析Apache Dubbo核心技术内幕》,《Java异步编程实战》,公众号:技术原始积累

参考:

原创文章,转载请注明: 转载自并发编程网 – ifeve.com本文链接地址: 还在用Text类型? Mysql8.0增强的JSON类型,它不香?

FavoriteLoading添加本文到我的收藏
  • Trackback 关闭
  • 评论 (1)
    • manongxiaowen
    • 2021/08/18 3:34下午

    太香了.博主写的很棒.为了感谢博主推荐博主使用一款国产免费接口管理工具-ApiPost.辛苦辛苦

return top