mysql 之 json 数据类型的使用及高效检索（配合虚拟列 virtual generated column）

mysql 5.7+ 版本开始支持 json 数据类型，可以方便的存储JSON格式的数据，同时配合虚拟列（virtual generated column），可以方便的为 json 列数据的某属性映射虚拟列，建立索引，高效检索。构造json数据方法：json_array() / json_object()json_array / json_object 用于组装 json 数据，json 说的简单些json就是由标量(int， float, string) + 数组 + 对象组合而成的，这两个函数可以方便的用于构造数组和对象的json格式串json_array(item1, item2, item3, …) =>[item1, item2, item3]json_object(key1, val1[, [key2, val2]…]) => {“key1”: “val1”, “key2”: “val2”,…}使用场景例如：select json_object( “username”, “big_cat”, “favorites”, json_array( json_object(“article_id”, 1, “favorited_at”, “2019-01-18”), json_object(“article_id”, 2, “favorited_at”, “2019-01-18”), json_object(“article_id”, 3, “favorited_at”, “2019-01-18”), json_object(“article_id”, 4, “favorited_at”, “2019-01-18”) ));// result{ “username”: “big_cat”, “favorites”: [ {“article_id”: 1, “favorited_at”: “2019-01-18”}, {“article_id”: 2, “favorited_at”: “2019-01-18”}, {“article_id”: 3, “favorited_at”: “2019-01-18”}, {“article_id”: 4, “favorited_at”: “2019-01-18”} ]} 读取json数据方法：json_extract() /col->"$.{property_name}“json_extract 用于读取 json 列的某字段，或者也可以使用 col->"$.{property_name}” 的方式访问json_extract(col, ‘$.{property_name}’) / col->’$.{property_name}‘create table users (id int unsigned not null auto_increment primary key,doc json);insert into users(doc)values (json_object(“name”, “big_cat”, “age”, 28)), (’{“name”: “james”, “age”: 29}’);select json_extract(doc, “$.name”) as name, json_extract(doc, “$.age”) as age from users;select doc->"$.name" as name, doc->"$.age" as age from users;高效检索json数据mysql 提供的一些函数是可以方便我们条件检索json数据的，但无法使用索引，数据量大的时候难免低效。select id, doc->"$.age" from users where json_extract(doc, “$.name”) = “big_cat”;select id, doc->"$.age" from users where doc->"$.name" = “big_cat”;这时我们可以利用同 json 一同新增的特性：虚拟列（virtual generated column）。将需要参与检索的 json 属性映射为虚拟列，在虚拟列上建立索引，便可参与高效检索。另外补充一下，在mysql 5.7+中，支持两种Generated Column，即Virtual Generated Column和Stored Generated Column。前者不存储元数据，后者会将 expression 的计算结果实际的存储下来。其实二者性能差距并不大，若对二者建立索引进行检索操作，前者性能可能会略低于后者，因为前者要对结果集即时的进行 expression 的演算，但后者需要消耗额外的存储空间。需要注意的有：不存储数据的特性也导致只能在虚拟列上建立二级索引，插入数据时不可以向虚拟列插入数值（mysql自行负责演算）。#虚拟列创建ALTER TABLE table_name ADD COLUMN col_name <type> [ GENERATED ALWAYS ] AS ( <expression> ) [ VIRTUAL|STORED ][ UNIQUE [KEY] ] [ [PRIMARY] KEY ] [ NOT NULL ] [ COMMENT <text> ]# 为 user 表的 json 字段的 name 创建虚拟列alter table users add column user_name varchar(10) generated always as (doc->"$.name");# 为虚拟列添加索引alter table users add index index_u_n(user_name);# 检索时可以使用索引explain select id, user_name, doc->"$.age" as age from users where user_name = “big_cat” \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: users partitions: NULL type: refpossible_keys: index_u_n key: index_u_n key_len: 43 ref: const rows: 1 filtered: 100.00 Extra: NULL1 row in set, 1 warning (0.00 sec)下面直接对 json 解析检索的方式是无法用到索引的 explain select id from users where doc->"$.user_name" = “big_cat” \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: users partitions: NULL type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2 filtered: 100.00 Extra: Using where1 row in set, 1 warning (0.00 sec)explain select id from users where json_extract(doc, ‘$.username’) = “big_cat” \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: users partitions: NULL type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 2 filtered: 100.00 Extra: Using where1 row in set, 1 warning (0.00 sec)还有其他 json 的使用这里就不说明了，大家可以参考一下文章：mysql json 使用类型查询函数：https://www.cnblogs.com/ooo0/…MySQL 5.7 虚拟列（virtual columns）：https://www.cnblogs.com/raich…MySQL 5.7原生JSON格式支持：https://www.cnblogs.com/zouca…