MySQL8 JSON类型使用笔记

多值索引（MYSQL版本>8.0.17）

参考：MySQL JSON单字段和多字段（集合）索引创建和使用

JSONARRAY类型

创建

ALTER Table student ADD INDEX index_name((CAST( colum_name -> "$[*]"  as UNSIGNED ARRAY)));

查询

    select * from student where JSON_CONTAINS(book_ids ->'$[*]','-1');
    select * from student where -1 MEMBER OF(book_ids ->'$[*]');
    select * from student where JSON_OVERLAPS(book_ids ->'$[*]',CAST('[1,-1]' AS JSON));

JSONOBJECT类型

创建

    ALTER TABLE customers ADD INDEX index_name( (CAST(colum_name ->'$.ids' AS UNSIGNED ARRAY)) );

colum_name 的内容为

{
   "user":"Bob",
   "ids":[94477,94536]
}

查询

    SELECT * FROM customers WHERE 94507 MEMBER OF(custinfo->'$.ids');
    SELECT * FROM customers WHERE JSON_CONTAINS(custinfo->'$.ids', CAST('[94507,94582]' AS JSON));
    SELECT * FROM customers WHERE JSON_OVERLAPS(custinfo->'$.ids', CAST('[94507,94582]' AS JSON));

查询数据使用参考

• 只支持MEMBER OF,JSON_CONTAINS,JSON_OVERLAB 三个函数可以使用到多值索引
• 索引不支持用于表关联
• 不能结合前缀索引
• 不支持排序

动态SQL示例

<if test="post_ids != null and post_ids.size > 0 ">
    OR JSON_OVERLAPS(post_ids ->'$[*]',#{post_ids, typeHandler=com.xr.common.mybatis.handler.JsonListLongTypeHandler})
</if>

<if test="user_id != null">
    OR JSON_CONTAINS(user_ids ->'$[*]',CAST(#{user_id} AS CHAR) )
</if>

特别注意
JSON_CONTAINS(book_ids ->'$[*]','-1');如果参数不是数据库中的字段的话，一定要加引号。就算是整型也得加！
对于已知值得情况下，使用MEMBER OF 效率高于JSON_CONTAINS。
JSON_CONTAINS第二个参数可以是指定值或者数据库中字段由于需要解析所以效率比MEMBER OF略低