在MySQL 8中索引JSON列 [英] Indexing JSON column in MySQL 8

问题描述

所以我正在尝试json列。 Mysql 8.0.17应该与多值JSON索引一起使用，像这样：

So I'm experimenting with json column. Mysql 8.0.17 supposed to work with multi value JSON indexes, like this:

创建索引数据__nbr_idx ON a1（（CAST（data-> '$ .nbr'作为未签名的数组）））

我具有JSON的列类别，例如[[books， clothes] 。
我需要从书籍类别中获取所有产品。我可以使用 json_contains或新的 member of。

I have column categories with JSON like this ["books", "clothes"]. I need to get all products from "books" category. I can use "json_contains" or new "member of".

SELECT * FROM products WHERE JSON_CONTAINS(categories, '\"books\"')
SELECT * FROM products WHERE "books" MEMBER OF(categories)

并且有效。问题在于，当然EXPLAIN会显示出正在进行全表扫描的查询，因此它很慢。

And it works. The problem is that of course EXPLAIN will reveal that there queries are making full table scan, and because of that it is slow.

所以我需要一些索引。

我更改了索引示例，将 unsigned类型替换为 char（32），因为我的类别是字符串而不是数字。我在Google中找不到任何示例，因此我认为char（）会很好，但不是。

I changed index example by replacing "unsigned" type with "char(32) since my categories are strings and not numbers. I cannot find any example for this in google so I assumed that char() will be fine, but not.

这是我的索引查询：

CREATE INDEX categories_index ON products((CAST(categories AS CHAR(32) ARRAY)))

也尝试过

CREATE INDEX categories_index ON products((CAST(categories->'$' AS CHAR(32) ARRAY)))

但是选择仍在进行全表扫描，我在做什么错？ b $ b如何在不使用虚拟列的情况下正确索引json列？

but selects are still making full table scan. What I'm doing wrong? How to correctly index json column without using virtual columns?

推荐答案

对于多值json索引，json路径必须匹配，所以带有索引

For a multi-valued json index, the json paths have to match, so with an index

CREATE INDEX categories_index 
ON products((CAST(categories->'$' AS CHAR(32) ARRAY)))

您的查询还必须使用路径->' $'（或等效的 json_extract（...，'$'））

your query has to also use the path ->'$' (or the equivalent json_extract(...,'$'))

SELECT * FROM products WHERE "books" MEMBER OF(categories->'$')

使其保持一致并且可以正常工作。

Make it consistent and it should work.

似乎没有明确路径的索引无法按预期工作，因此您如果要使用整个文档，则必须指定->'$'。这可能是一个错误，但是也可能是强制转换或自动广播到数组的预期行为。如果您指定路径，则将是安全的。

It seems though that an index without an explicit path does not work as expected, so you have to specify ->'$' if you want to use the whole document. This is probably a bug, but could also be an intended behaviour of casting or autocasting to an array. If you specify the path you'll be on the safe side.

这篇关于在MySQL 8中索引JSON列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！