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

╰半橙微兮° 2023-07-25 11:20 1阅读 0赞

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

1.  `json_array(item1, item2, item3, ...)`
2.  `=>`
3.  `[item1, item2, item3]`
4.   
5.  `json_object(key1, val1[, [key2, val2]...])`
6.  `=>`
7.  `{"key1": "val1", "key2": "val2",...}`

使用场景例如：

1.  `select json_object(`
2.  `"username", "big_cat",`
3.  `"favorites", json_array(`
4.  `json_object("article_id", 1, "favorited_at", "2019-01-18"),`
5.  `json_object("article_id", 2, "favorited_at", "2019-01-18"),`
6.  `json_object("article_id", 3, "favorited_at", "2019-01-18"),`
7.  `json_object("article_id", 4, "favorited_at", "2019-01-18")`
8.  `)`
9.  `);`
10. `// result`
11. `{`
12. `"username": "big_cat",`
13. `"favorites": [`
14. `{"article_id": 1, "favorited_at": "2019-01-18"},`
15. `{"article_id": 2, "favorited_at": "2019-01-18"},`
16. `{"article_id": 3, "favorited_at": "2019-01-18"},`
17. `{"article_id": 4, "favorited_at": "2019-01-18"}`
18. `]`
19. `}`

### 读取json数据 ###

方法：json\_extract() /`col`\->"$.\{property\_name\}"

json\_extract 用于读取 json 列的某字段，或者也可以使用 `col`\->"$.\{property\_name\}" 的方式访问

json_extract(`col`, '$.{property_name}') / `col`->'$.{property_name}'

1.  ``create table `users` (``
2.  `` `id` int unsigned not null auto_increment primary key,``
3.  `` `doc` json``
4.  `);`
5.   
6.  ``insert into `users`(`doc`)``
7.  `values (json_object("name", "big_cat", "age", 28)), ('{"name": "james", "age": 29}');`
8.   
9.  ``select json_extract(`doc`, "$.name") as `name`, json_extract(`doc`, "$.age") as `age` from `users`;``
10. ``select `doc`->"$.name" as `name`, `doc`->"$.age" as `age` from `users`;``

### 高效检索json数据 ###

mysql 提供的一些函数是可以方便我们条件检索json数据的，但无法使用索引，数据量大的时候难免低效。

1.  `select id, doc->"$.age" from users where json_extract(doc, "$.name") = "big_cat";`
2.  `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自行负责演算）。

1.  `#虚拟列创建`
2.   
3.  ``ALTER TABLE `table_name` ADD COLUMN `col_name` <type> [ GENERATED ALWAYS ] AS ( <expression> ) [ VIRTUAL|STORED ]``
4.  `[ UNIQUE [KEY] ] [ [PRIMARY] KEY ] [ NOT NULL ] [ COMMENT <text> ]`

1.  `# 为 user 表的 json 字段的 name 创建虚拟列`
2.  ``alter table `users` add column `user_name` varchar(10) generated always as (`doc`->"$.name");``
3.  `# 为虚拟列添加索引`
4.  ``alter table `users` add index `index_u_n`(`user_name`);``
5.  `# 检索时可以使用索引`
6.  ``explain select `id`, `user_name`, `doc`->"$.age" as `age` from `users` where `user_name` = "big_cat" \G``
7.  `*************************** 1. row ***************************`
8.  `id: 1`
9.  `select_type: SIMPLE`
10. `table: users`
11. `partitions: NULL`
12. `type: ref`
13. `possible_keys: index_u_n`
14. `key: index_u_n`
15. `key_len: 43`
16. `ref: const`
17. `rows: 1`
18. `filtered: 100.00`
19. `Extra: NULL`
20. `1 row in set, 1 warning (0.00 sec)`

下面直接对 json 解析检索的方式是无法用到索引的

1.  `explain select id from users where doc->"$.user_name" = "big_cat" \G`
2.  `*************************** 1. row ***************************`
3.  `id: 1`
4.  `select_type: SIMPLE`
5.  `table: users`
6.  `partitions: NULL`
7.  `type: ALL`
8.  `possible_keys: NULL`
9.  `key: NULL`
10. `key_len: NULL`
11. `ref: NULL`
12. `rows: 2`
13. `filtered: 100.00`
14. `Extra: Using where`
15. `1 row in set, 1 warning (0.00 sec)`
16. ``explain select id from users where json_extract(`doc`, '$.username') = "big_cat" \G``
17. `*************************** 1. row ***************************`
18. `id: 1`
19. `select_type: SIMPLE`
20. `table: users`
21. `partitions: NULL`
22. `type: ALL`
23. `possible_keys: NULL`
24. `key: NULL`
25. `key_len: NULL`
26. `ref: NULL`
27. `rows: 2`
28. `filtered: 100.00`
29. `Extra: Using where`
30. `1 row in set, 1 warning (0.00 sec)`

还有其他 json 的使用这里就不说明了，大家可以参考一下文章：  
mysql json 使用 类型 查询 函数：[https://www.cnblogs.com/ooo0/...][https_www.cnblogs.com_ooo0_...]  
MySQL 5.7 虚拟列 （virtual columns）：[https://www.cnblogs.com/raich...][https_www.cnblogs.com_raich...]  
MySQL 5.7原生JSON格式支持：[https://www.cnblogs.com/zouca...][https_www.cnblogs.com_zouca...]

[https_www.cnblogs.com_ooo0_...]: https://www.cnblogs.com/ooo0/p/9309277.html
[https_www.cnblogs.com_raich...]: https://www.cnblogs.com/raichen/p/5227449.html
[https_www.cnblogs.com_zouca...]: https://www.cnblogs.com/zoucaitou/p/4424575.html