mysql的json数据类型使用

mysql的json数据类型使用

前言

提示：从 MySQL 5.7.8 开始，MySQL 支持由RFC 7159定义的本机JSON数据类型，该类型可有效访问 JSON(JavaScript 对象 table 示法)文档中的数据。与将 JSON 格式的字符串存储在字符串列中相比，JSON数据类型具有以下优点：

自动验证存储在JSON列中的 JSON 文档。无效的文档会产生错误。

优化的存储格式。存储在JSON列中的 JSON 文档将转换为内部格式，从而可以快速读取文档元素。

---

适用场景：需要用到key-value的结构化信息的场合，如通用的配置类

---

一、json函数的增、删、改、查？

新增：

INSERT INTO comconfig (id,type,item,valid,create_by,create_time)  VALUES('202103161519080005001','1','{"b_maxperosn":"10","t_maxperosn":"10","carlength":"15","carwidth":"5","carheight":"4","carweight":"30"}','1','system',SYSDATE());
#使用JSON_OBJECT()函数构造json对象
INSERT INTO comconfig (id,type,item,valid,create_by,create_time)  VALUES('202103161519080005002','1',
JSON_OBJECT("b_maxperosn",11,"t_maxperosn",11,"carlength",11,"carwidth",11,"carheight",11,"carweight",11),'1','system',SYSDATE());

查询：

->>符号 与JSON_UNQUOTE功能一致

 SELECT id ,valid , type,
        item ->> '$.b_maxperosn' as'b_maxperosn',
        item ->> '$.t_maxperosn' as't_maxperosn',
        item ->> '$.carlength' as'carlength',
        item ->> '$.carwidth' as'carwidth',
        item ->> '$.carheight' as'carheight',
        item ->> '$.carweight' as'carweight'
        from comconfig where type = '1' and valid = '1';

 #  JSON_KEYS：返回key数组，本质上还是字符串
SELECT JSON_KEYS(item) cfg_list FROM comconfig where type = '2' and valid = '1';

修改：

# JSON_SET 替换现有路径的值，并添加不存在的路径的值，(类似于saveOrUpdate)
--b_maxperosn更新成功，同时test列插入成功
UPDATE comconfig SET item = JSON_SET(item, '$.b_maxperosn', '1000','$.test','0') WHERE id = '202103161519080005001';
--b_maxperosn成功更新旧值1000为666
UPDATE comconfig SET item = JSON_SET(item, '$.b_maxperosn', '666') WHERE id = '202103161519080005001';

# JSON_INSERT 添加新值，但不替换现有值：(类似于save)
--插入 "hello": "world",b_maxperosn字段不更新
UPDATE comconfig SET item = JSON_INSERT(item, '$.b_maxperosn', '666','$.hello','world') WHERE id = '202103161519080005001';
--b_maxperosn、test列更新成，woqu列忽略

#JSON_REPLACE 替换现有值，并忽略新值：(类似于update) 
--成功更新旧值666为555，nihao字段忽略
UPDATE comconfig SET item = JSON_REPLACE(item, '$.b_maxperosn', '555','$.nihao','11') WHERE id = '202103161519080005001';

删除

#JSON_REMOVE 删除json指定元素hello、test
UPDATE comconfig SET item = JSON_REMOVE(item, '$.hello','$.test') WHERE id = '202103161519080005001';

二、mybatis 使用步骤

1.dao层

代码如下（示例）：

	//根据类型获取配置json的key字段数组
	String getConfigArray(@Param("type") String type);
	//根据类型和json的key字段集合 查询配置信息
	Map<String,Object> getConfigInfoByType(@Param("type") String type,@Param("cfgList") List<String> cfgList);
	//根据id和配置字段更新json字段
	int updateById(@Param("cfgMap") Map<String,Object> cfgMap,@Param("id") String id);

2.mapper.xml

代码如下（示例）：

	<select id="getConfigArray" resultType="java.lang.String">
        SELECT JSON_KEYS(item) cfg_list FROM comconfig where type = #{type} and valid = '1';
    </select>

    <select id="getConfigInfoByType" resultType="java.util.Map" parameterType="java.util.List">
         SELECT id ,valid , type,
            <foreach collection="cfgList" separator="," index="index" item="item">
                item ->> '$.${item}' as '${item}'
            </foreach>
        from comconfig where type = #{type} and valid = '1';
    </select>

    <update id="updateById">
        update comconfig
        <set>
            <foreach collection="cfgMap.entrySet()" index="key" item="value" separator=",">
                <if test="key != 'id'.toString()">
                    item = JSON_REPLACE(item, '$.${key}', #{value})
                </if>
            </foreach>
        </set>
        where
        id = #{id}
    </update>

---

总结

以上就是今天要讲的内容，本文仅仅简单介绍了json类型的使用，而json提供了大量能使我们快速便捷地处理数据的函数和方法，具体请参考mysql官方文档[link](https://dev.mysql.com/doc/refman/5.7/en/).。