简单谈谈MySQL5.7 JSON格式检索

MySQL 5.7.7实验室版本开始，InnoDB存储引擎原生支持JSON格式。这一支持并非简单的BLOB数据类型的替代，而是为处理JSON数据提供了集成和优化。为了更好地理解和运用这一特性，让我们深入一下在MySQL中如何操作JSON数据。

在MySQL 5.7版本之后，你可以在创建表时指定列为JSON类型。那么，如何在JSON格式的数据上创建索引以提高查询效率呢？下面是一个详细的步骤说明。

创建一个包含JSON类型列的表。表结构如下：

```sql

CREATE TABLE json_test (

id INT(8) NOT NULL AUTO_INCREMENT,

content JSON NOT NULL,

PRIMARY KEY (id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

```

然后，你可能会想查询JSON列中的特定数据。例如，提取所有名为"zhangsan"的记录的年龄。如果你直接对JSON列使用`json_extract`函数进行查询，并通过`expain`分析查询语句，你会发现它进行了全表扫描，效率较低。

为了提高查询效率，你可以在JSON列上创建虚拟列，并对虚拟列进行索引。步骤如下：

```sql

ALTER TABLE json_test ADD name_virtual VARCHAR(32) GENERATED ALWAYS AS (json_extract(content, '$.name')) VIRTUAL;

CREATE INDEX name_virtual_index ON json_test(name_virtual);

```

现在，你可以使用虚拟列进行检索，提高查询效率。需要注意的是，如果直接在JSON列上使用`json_extract`函数进行比较，仍然会进行全表扫描。

MySQL通过创建虚拟列的方式，实现了空间换时间的策略。虚拟列实际上是对JSON数据中特定路径数据的冗余存储，比较时通过走虚拟列的索引来定位到实际数据，大大提高了查询效率。

这种处理方式类似于在数据库中创建一个触发器，当原始JSON数据发生变化时，虚拟列的数据也会自动更新。通过这种方式，你可以在虚拟列上使用标准的SQL查询和索引技术，而不是直接操作复杂的JSON数据，从而简化了查询操作并提高了性能。