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

编程并不是一个机械性的工作，而是需要有思考，有创新的工作，语法是固定的，但解决问题的思路则是依靠人的思维，这就需要我们坚持学习和更新自己的知识。今天golang学习网就整理分享《mysql 之 json 数据类型的使用及高效检索（配合虚拟列 virtual generated column）》，文章讲解的知识点主要包括MySQL、JSON，如果你对数据库方面的知识点感兴趣，就不要错过golang学习网，在这可以对大家的知识积累有所帮助，助力开发能力的提升。

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格式串

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() /

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数据的，但无法使用索引，数据量大的时候难免低效。

#虚拟列创建

ALTER TABLE `table_name` ADD COLUMN `col_name`  [ GENERATED ALWAYS ] AS (  ) [ VIRTUAL|STORED ]
[ UNIQUE [KEY] ] [ [PRIMARY] KEY ] [ NOT NULL ] [ COMMENT  ]

# 为 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: ref
possible_keys: index_u_n
          key: index_u_n
      key_len: 43
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 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: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where
1 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: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where
1 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...

终于介绍完啦！小伙伴们，这篇关于《mysql 之 json 数据类型的使用及高效检索（配合虚拟列 virtual generated column）》的介绍应该让你收获多多了吧！欢迎大家收藏或分享给更多需要学习的朋友吧~golang学习网公众号也会发布数据库相关知识，快来关注吧！