如何在MySQL中为JSON列创建索引？

Question

如何在MySQL服务器中创建Json数据类型的子文档索引？

我知道我们需要从基础表创建一个生成列，然后需要虚拟或存储该列的索引。

但是我想要创建子文档的生成列的语法。

- Aman Aggarwal

Answer 1

要为存储在 JSON 中的值建立索引，请使用存储的生成列。

例如，可以对以下内容的title和category进行索引：

{"title": "Some Title", "category": "Some Category", "url": "...", ...}

使用类似以下的方式：

CREATE TABLE listings (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) AS
    (data->>'$.title') STORED,
  category VARCHAR(255) AS
    (data->>'$.category') STORED,
  data JSON NOT NULL,
  KEY (title),           -- index title
  KEY (category),        -- index category
  KEY (title, category)  -- composite index of title & category
);

了解有关MySQL作为智能JSON存储的更多信息 :-)

- ΔO 'delta zero'

Answer 2

在MySQL 8.0.21版本中，可以使用以下语法：

CREATE TABLE inventory(
items JSON,
INDEX i1 ( (JSON_VALUE(items, '$.name' RETURNING CHAR(50))) ),
INDEX i2 ( (JSON_VALUE(items, '$.price' RETURNING DECIMAL(5,2))) ),
INDEX i3 ( (JSON_VALUE(items, '$.quantity' RETURNING UNSIGNED)) )
);

并使用以下内容进行查询：

SELECT items->"$.price" FROM inventory
WHERE JSON_VALUE(items, '$.name' RETURNING VARCHAR(50)) = "hat";

SELECT * FROM inventory
WHERE JSON_VALUE(items, '$.price' RETURNING DECIMAL(5,2)) <= 100.01;

SELECT items->"$.name" AS item, items->"$.price" AS amount
FROM inventory
WHERE JSON_VALUE(items, '$.quantity' RETURNING UNSIGNED) > 500;

来源: https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-21.html

- NeverEndingQueue

---

这看起来是一个非常有效的答案，我不明白为什么没有评论就被踩了。发布说明也没有太多帮助。它说它相当于调用 CAST(JSON_UNQUOTE(JSON_EXTRACT(json_doc, path)) AS type)，但我不认为我可以执行 INDEX i1 (CAST(JSON_UNQUOTE(JSON_EXTRACT(json_doc, path)) AS type))。不知道啊。所有这些问题都是因为我懒得启动测试环境。 - toraman

好的。我测试了一下，发现它不起作用，这不是@NeverEndingQueue的错。Mysal文档有一个错误，JSON_VALUE不接受VARCHAR，应该改为CHAR。关于两者之间的区别，请参见：https://dev.mysql.com/doc/refman/8.0/en/char.html - toraman

Answer 3

JSON列和其他二进制类型的列一样，不能直接索引；相反，您可以在一个生成的列上创建一个索引，该列从JSON列中提取标量值。查看第“二级索引和生成的虚拟列”部分，获取详细示例。

- Bipil Raut

---

参见：http://mysqlserverteam.com/indexing-json-documents-via-virtual-columns/ - kjdion84