在MySQL 8中索引JSON列

Question

我正在尝试使用json列。Mysql 8.0.17可与多值JSON索引一起使用，如下所示：

CREATE INDEX data__nbr_idx ON a1( (CAST(data->'$.nbr' AS UNSIGNED ARRAY)) )

我有一个名为categories的列，其中包含像这样的JSON ["books", "clothes"]。 我需要获取所有“books”类别的产品。我可以使用“json_contains”或新的“member of”。

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

它可以工作。问题是，当然会有EXPLAIN显示这些查询正在进行完整表扫描，因此它很慢。

所以我需要一些索引。

我通过将“unsigned”类型替换为“char（32）”来更改索引示例，因为我的类别是字符串而不是数字。我在谷歌上找不到任何关于此的示例，因此我认为char（）会很好，但实际上并不是这样。

这是我的索引查询：

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

也尝试过

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

但是选择操作仍然在进行全表扫描。我做错了什么吗？ 如何正确地索引 json 列而不使用虚拟列？

- chojnicki

---

创建所需表达式的生成虚拟列，然后通过该列创建索引。请参阅MySQL 8.0参考手册/.../二级索引和生成列。 - Akina

就像我说的那样，我正在尝试在没有虚拟列的情况下完成这个任务，因为新的Mysql应该具有更好的JSON索引，而不需要这种次要列的解决方法。 - chojnicki

索引只能由标量值创建。JSON数组不是安全的索引数据类型。 - Akina

Answer 1

对于一个多值的json索引，json路径必须匹配，因此使用索引

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

你的查询还必须使用路径 ->'$'（或等效的json_extract(...,'$')）。

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

保持一致，代码就会正常运行。

似乎没有显式路径的索引不像预期那样工作，因此如果要使用整个文档，则必须指定 ->'$'。这可能是一个bug，但也可能是将其强制转换或自动转换为数组的意图行为。如果指定路径，则更加安全可靠。

- Solarflare

---

虽然有些不方便，但好消息是它确实是可能的；现在索引运行得非常顺畅。 - chojnicki

你漏掉了一个右括号和索引查询的结尾。 - chojnicki