Mysql JSON索引与关联查询
JSON Path语法
// 比较简单，举两个例子
set @jsonSource = {"aaa": [{"ccc":"1"},{"ccc":"2"},],"bbb":"bbb的值"
};
@jsonSource-> "$.bbb" // 结果为 bbb的值
@jsonSource-> "$.aaa[*].ccc" // 结果为 ["1","2"] |
给JSON字段添加索引
Mysql 8.0.x版本后，支持对json字段创建索引，直接 create index 即可，需要使用cast方法将json目标字段转换成可以创建索引的类型。有两种情况：
普通索引：每条记录和json字段为1对1关系
多值索引：每条记录和json字段为1对多关系（对应array，或是jsonPath取值结果是array情况）
-- 普通索引 ADD INDEX index_modify_user((CAST(permission_json->'$.aaa' AS CHAR(64)))) -- 多值索引 （区别仅在于用cast 方法转化成一个 ARRAY） ADD INDEX index_modify_user((CAST(permission_json->'$.modify[*]' AS CHAR(64) ARRAY ))) |
根据JSON索引查询
只有少数语句支持JSON字段索引。如果需要在索引上查询数据，使用member of。
使用member of语句可以使json上建的索引生效，其他使用json索引语句参考官网。
具体语法： ‘Value’ member of ( 'Path' )
例如：
SELECT topic_id FROM topic WHERE "123" member of (permission_json->'$.modify'); |
对应Jooq语法：
// Jooq不支持member of ，我们自己封装了一个condition：JooqSyntax.memberOfCondition dsl.select(TOPIC.TOPIC_ID).from(TOPIC).where(JooqSyntax.memberOfCondition(userId, TOPIC.PERMISSION_JSON, "$.modify")) |
JSON字段关联查询
先使用JSON_TABLE方法将json转化成一个临时表，再进行关联，例如
SELECT u.* FROM topic AS t, user AS u INNER JOINJSON_TABLE(t.permission_json,'$.modify[*]' COLUMNS( uid VARCHAR(50) PATH "$")) AS temp ON u.user_id = temp.uid WHERE t.topic_id= 1481071225944932352; |
注意临时表一定要指定（AS）一个表名
对应Jooq语法：
// 先定义出一个临时表Table tempTable = DSL.jsonTable(TOPIC.PERMISSION_JSON, DSL.field("{0}", String.class,"$.modify[*]" )).column("uid", SQLDataType.VARCHAR(32)).path("$").asTable("temp");// 再使用临时表关联查询List |
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！