如何在MySQL中创建虚拟列来索引JSON列？

How to create a virtual column to index JSON column in MySQL?(如何在MySQL中创建虚拟列来索引JSON列？)

问题描述

我使用的是MySql 8.0 verion，我的表格如下所示：

CREATE TABLE `table1` (
  `id` int(11) NOT NULL,
  `tableType` varchar(45) DEFAULT NULL,
  `jkey` varchar(45) DEFAULT NULL,
  `jval` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) 

此处jval将始终有两个字段，如下所示：

{"group": "group1@abc.com", "user": "user1"}

{"group": "group2@abc.com", "user": "user2"}

我正在尝试将索引添加到JSON columnjval，但出现以下错误：

Operation failed: There was an error while applying the SQL script to the database.
ERROR 3152: JSON column 'jval' supports indexing only via generated columns on a specified JSON path.
SQL Statement:
ALTER TABLE `db1`.`table1` 
ADD INDEX `jval` (`value` ASC) VISIBLE

如何在MySQL中创建virtual column索引jval？

推荐答案

mysql没有直接索引JSON文档的方法，但它为我们提供了另一种选择：生成列。通过从JSON文档中的值生成列，然后对该列进行索引，我们实际上可以为JSON字段编制索引。

Generated Columns的语法为

CREATE TABLE `table1` (
  `id` int(11) NOT NULL,
  `tableType` varchar(45) DEFAULT NULL,
  `jkey` varchar(45) DEFAULT NULL,
  `jval` json NOT NULL,

  `group_virtual` VARCHAR(250) GENERATED ALWAYS AS (`jval` ->> '$.group') NOT NULL, 
  `user_virtual` VARCHAR(250) GENERATED ALWAYS AS (`jval` ->> '$.user') NOT NULL,   
  
  PRIMARY KEY (`id`)
);