Mysql创建json字段索引的两种方式

下面是关于MySQL创建JSON字段索引的两种方式的攻略。

方式一：使用虚拟列

准备工作

在 MySQL 5.7.8 版本及以后，支持通过自定义虚拟列的方式对表中的 JSON 字段进行索引。因此，在开始之前需要确保你的 MySQL 版本不低于 5.7.8。

操作步骤

接下来，我们假设有一个名为 users 的表，其中有一个 JSON 字段 info，现在我们要对该字段进行索引。

1. 创建虚拟列

使用 ALTER TABLE 语句创建虚拟列，虚拟列的类型为 GENERATED COLUMN，通过该列将 JSON 字段解析成一个可被索引的列。

ALTER TABLE users ADD COLUMN info_parsed JSON AS (JSON_EXTRACT(info, '$')) STORED;

这条语句会在 users 表中创建一个名为 info_parsed 的 JSON 类型的虚拟列，它的值就是 info 字段解析出来的 JSON 对象。

1. 创建索引

使用 CREATE INDEX 语句创建索引，将虚拟列 info_parsed 作为索引建立即可。示例代码如下：

CREATE INDEX index_info_parsed ON users (info_parsed);

现在，我们已经成功地创建了一个 info_parsed 索引，可以使用该索引优化对 info 字段的查询操作。

下面是一个完整的例子：

-- 在 users 表中添加 info 列
ALTER TABLE users ADD COLUMN info JSON;

-- 向 users 表中插入数据
INSERT INTO users (info) VALUES ('{"id": 1, "name": "Tom", "age": 18}');

-- 创建虚拟列
ALTER TABLE users ADD COLUMN info_parsed JSON AS (JSON_EXTRACT(info, '$')) STORED;

-- 创建索引
CREATE INDEX index_info_parsed ON users (info_parsed);

-- 查询
EXPLAIN SELECT * FROM users WHERE JSON_EXTRACT(info, '$.name') = 'Tom';

方式二：使用函数索引

准备工作

在 MySQL 8.0 版本之后，支持 JSON 字段的索引，这种方式需要使用到 JSON_VALUE 等一系列的 JSON 函数，因此需要确保你的 MySQL 版本不低于 8.0。

操作步骤

接下来还是假设有一个名为 users 的表，其中有一个 JSON 字段 info，现在我们要对该字段进行索引。

1. 创建索引

使用 CREATE INDEX 语句创建索引，将 JSON 函数 JSON_EXTRACT、JSON_UNQUOTE 和 JSON_TYPE 构成的存储函数（stored function）作为索引建立即可。示例代码如下：

CREATE INDEX index_info ON users (JSON_TYPE(JSON_EXTRACT(JSON_UNQUOTE(info), '$')));

1. 查询

使用 JSON 函数进行查询即可。示例代码如下：

SELECT * FROM users WHERE JSON_EXTRACT(JSON_UNQUOTE(info), '$.name') = 'Tom';

下面是一个完整的例子：

-- 在 users 表中添加 info 列
ALTER TABLE users ADD COLUMN info JSON;

-- 向 users 表中插入数据
INSERT INTO users (info) VALUES ('{"id": 1, "name": "Tom", "age": 18}');

-- 创建索引
CREATE INDEX index_info ON users (JSON_TYPE(JSON_EXTRACT(JSON_UNQUOTE(info), '$')));

-- 查询
EXPLAIN SELECT * FROM users WHERE JSON_EXTRACT(JSON_UNQUOTE(info), '$.name') = 'Tom';

以上就是 MySQL 创建 JSON 字段索引的两种方式的攻略，希望对你有所帮助。