给MySQL的JSON数据创建索引

23 May 2018

Reading time ~1 minute

MySQL从5.7.8版本开始支持JSON数据，给处理一些动态变化的数据存储带来方便，但是MySQL中的JSON数据要通过索引进行查询，需要用到虚拟字段，通过对虚拟字段创建索引从而利用该索引对JSON数据进行查询。

创建一张包含JSON数据和虚拟索引的表

 CREATE TABLE log(
    uid BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    meta JSON NOT NULL,
    username VARCHAR(32) GENERATED ALWAYS AS (`meta` ->> '$.username') VIRTUAL,
    INDEX username_idx (username)
    )ENGINE=INNODB CHARSET=utf8mb4;

如果是给已经建好的表添加索引，通过ALTER TABLE命令先添加一列虚拟列再创建索引

ALTER TABLE log 
  ADD COLUMN username VARCHAR(32) GENERATED ALWAYS AS (`meta` ->> '$.username') VIRTUAL
  ADD INDEX username_idx username;

后续查询时便可利用上username_idx这个索引，如

 SELECT meta->>"$.username" AS name FROM log WHERE username = "admin";

如果是PostgreSQL，可以不必通过虚拟列便可对jsonb数据使用索引。

CREATE INDEX idxgin ON log USING gin (meta);

在JSON数据的支持方面MySql和PostgreSQL还是有些差距，PostgreSQL可以直接对JSON数据创建索引。