mysql8 json 索引 mysql json类型索引

转载

西门吹雪 2023-08-06 23:13:48

文章标签 mysql8 json 索引 mysql JSON json MySQL 文章分类 MySQL 数据库

背景

* 在MySQL 5.7.8中，MySQL支持由RFC 7159定义的本地JSON数据类型，它支持对JSON(JavaScript对象标记)文档中的数据进行有效访问.

* MySQL会对DML JSON数据自动验证。无效的DML JSON数据操作会产生错误.

优化的存储格式。存储在JSON列中的JSON文档转换为一种内部格式，允许对Json元素进行快速读取访问.
MySQL Json类型支持通过虚拟列方式建立索引，从而增加查询性能提升.

Json 索引

创建Json索引表 json_key [ name 为虚拟列, virtual 表明不占用磁盘空间 ]

[ GENERATED ALWAYS 与 VIRTUAL可以不写 ]

指定获取json中的name key

mysql> CREATE TABLE json_key(
-> uid BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,
-> data JSON NOT NULL,
-> name VARCHAR(32) GENERATED ALWAYS AS (json_extract(data, ‘$.name‘)) VIRTUAL,
-> )ENGINE=INNODB CHARSET=utf8mb4;

插入数据带 data中name key [ 插入数据时需要显示指定非虚拟列 ]

mysql> INSERT INTO json_key(uid, data) SELECT NULL, JSON_OBJECT(‘name‘, ‘tom‘, ‘sex‘, ‘male‘, ‘age‘, ‘26‘);
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0

插入数据不带 data中name key

mysql> INSERT INTO json_key(uid, data) SELECT NULL, JSON_OBJECT(‘sex‘, ‘female‘, ‘age‘, ‘29‘);
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0

查看json_key所有数据

mysql> select * from json_key;
±----±--------------------------------------------±------+
| uid | data | name |
±----±--------------------------------------------±------+
| 1 | {“age”: “26”, “sex”: “male”, “name”: “tom”} | “tom” |
| 2 | {“age”: “29”, “sex”: “female”} | NULL |
±----±--------------------------------------------±------+
2 rows in set (0.01 sec)

mysql原生并不支持json列中的属性索引，但是我们可以通过mysql的虚拟列间接的为json中的某些属性创建索引，原理就是为json中的属性创建虚拟列，然后通过给虚拟列建立索引，从而间接的给属性创建了索引。

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

如果需要Stored Generated Golumn的话，可能在Virtual Generated Column上建立索引更加合适，一般情况下，都使用Virtual Generated Column，这也是MySQL默认的方式

许你写的格式如下：

fieldname [ GENERATED ALWAYS ] AS ( ) [ VIRTUAL|STORED ] [ UNIQUE [KEY] ] [ [PRIMARY] KEY ] [ NOT NULL ] [ COMMENT ]
首先我们创建一张带有虚拟列的表新表:

CREATE TABLE players (
id INT UNSIGNED NOT NULL primary key auto_increment,
player JSON NOT NULL,
vname VARCHAR(50) GENERATED ALWAYS AS (player ->> ‘$.name’) NOT NULL – name的虚拟列
);
利用操作符-» 来引用JSON字段中的KEY。在本例中字段names_virtual为虚拟字段，我把它定义成不可以为空。在实际的工作中，需要结合具体的情况来定。因为JSON本身是一种弱结构的数据对象。也就是说的它的结构不是固定不变的。

然后我们查一下这个表的列有哪些：

SHOW COLUMNS FROM players;
结果如下，会发现vname的附加信息里面显示列类型为虚拟生成列：

mysql8 json 索引 mysql json类型索引_json