1前言
MySQL支持由 RFC 7159 定义的原生JSON 数据类型,该数据类型可以有效访问 JSON(JavaScript Object Notation)中的元素数据。与将JSON 格式的字符串存储为单个字符串类型相比,JSON 数据类型具有以下优势:
MYSQL 8.0,除了提供JSON 数据类型,还有一组 SQL 函数可用于操作 JSON 的值,例如创建JSON对象、增删改查JSON数据中的某个元素。另外,关注终码一生公众号,回复“资料”,送你面试题宝典和大量视频资源下载!
2常用JSON函数
首先,创建表列时候,列要设置为JSON类型:
CREATE TABLE t1 (content JSON);
插入数据,可以像插入varchar类型的数据一样,把json串添加单引号进行插入
mysql> INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}');
Query OK, 1 row affected (0.00 sec)
当然mysql也提供了创建JSON对象的函数:
mysql> INSERT INTO t1 VALUES(JSON_OBJECT("key1","value1","key2","value2"));
Query OK, 1 row affected (0.00 sec)
使用JSON_EXTRACT函数查询JSON类型数据中某个元素的值:
lamba表达式风格查询:
使用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
3MyBatis中使用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>
4
总结
虽然我们实践上不建议把所有扩展字段都放到一个大字段里面。但是即使有原因一定到放,那么也建议选择JSON类型,而不是varcahr和Text类型。
参考:https://dev.mysql.com/doc/refman/8.0/en/json.html
—END—