MySQL8 JSON型別使用筆記

快乐之源發表於2024-06-25
MySqlJSON型別筆記

多值索引（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略低

相關文章