MySQL · 最佳实践 · 如何索引JSON字段

骑猪看日落 2022-04-12 02:38 1029阅读 0赞

## 概述 ##

MySQL从5.7.8起开始支持JSON字段，这极大的丰富了MySQL的数据类型。也方便了广大开发人员。但MySQL并没有提供对JSON对象中的字段进行索引的功能，至少没有直接对其字段进行索引的方法。本文将介绍利用MySQL 5.7中的虚拟字段的功能来对JSON对象中的字段进行索引。

### 示例数据 ###

我们将基于下面的JSON对象进行演示

{
        "id": 1,  
        "name": "Sally",  
        "games_played":{    
           "Battlefield": {
              "weapon": "sniper rifle",
              "rank": "Sergeant V",
              "level": 20
            },                                                                                                                          
           "Crazy Tennis": {
              "won": 4,
              "lost": 1
            },  
           "Puzzler": {
              "time": 7
            }
         }
     }

表的基本结构

CREATE TABLE `players` (  
        `id` INT UNSIGNED NOT NULL,
        `player_and_games` JSON NOT NULL,
        PRIMARY KEY (`id`)
    );

如果只是基于上面的表的结构我们是无法对JSON字段中的Key进行索引的。接下来我们演示如何借助虚拟字段对其进行索引

### 增加虚拟字段 ###

虚拟列语法如下

<type> [ GENERATED ALWAYS ] AS ( <expression> ) [ VIRTUAL|STORED ]
    [ UNIQUE [KEY] ] [ [PRIMARY] KEY ] [ NOT NULL ] [ COMMENT <text> ]

在MySQL 5.7中，支持两种Generated Column，即Virtual Generated Column和Stored Generated Column，前者只将Generated Column保存在数据字典中（表的元数据），并不会将这一列数据持久化到磁盘上；后者会将Generated Column持久化到磁盘上，而不是每次读取的时候计算所得。很明显，后者存放了可以通过已有数据计算而得的数据，需要更多的磁盘空间，与Virtual Column相比并没有优势，因此，MySQL 5.7中，不指定Generated Column的类型，默认是Virtual Column。

[原文链接][Link 1]

[Link 1]: http://click.aliyun.com/m/1000027164/