JSON字段添加索引

版本

mysql：5.7以上

添加索引语句

ALTER TABLE table_name ADD INDEX idx_mv_custinfo_list( ( CAST( 列名 -> '$[*].对应key' AS UNSIGNED array)) );

查询语句

假如查询的字段是custinfo ，json中含有id做为键

查询一个值

WHERE
    94507 MEMBER OF ( custinfo -> '$[*].id' );

查询多个值

WHERE
JSON_CONTAINS(
custinfo -> '$[*].id',
CAST( '[94582, 94507]' AS JSON ));

或者

WHERE
JSON_OVERLAPS (
custinfo -> '$[*].id',
CAST( '[94477, 94582]' AS JSON ));

查看创建的索引

SHOW INDEX FROM table_name;

是否命中索引

EXPLAIN SELECT
    *
FROM
    table_name
WHERE
    94507 MEMBER OF ( custinfo -> '$[*].id' );