JSON 介绍

  • JSON（JavaScript Object Notation）是一种轻量级的数据交换语言，并且是独立于语言的文本格式。
  • 一些 NoSQL 数据库选择 JSON 作为其数据存储格式，比如：MongoDB、CouchDB 等。
  • MySQL 5.7.x 开始支持 JSON 数据类型。

官方文档（JSON 类型）：https://dev.mysql.com/doc/refman/8.0/en/json.html

JSON 类型适合用来处理一些经常需要变动表结构的情况。应该结构化数据是需要预先定义好表结构的，如果后面业务有变动，需要通过 ALTER TABLE 命令来改变表结构，而每次 ALTER TABLE 的代价很大，如果表的数据量很大，需要花费很长时间，这种情况下，可以尝试使用 MySQL 的 JSON 类型。

结构化 & 非结构化

结构化数据：通常我们说的关系型数据就是结构化数据，二维表结构（行和列），使用 SQL 语句进行操作。

非结构化数据：比如像 MongoDB 就是使用 JSON 作为其数据存储格式，无结构定义（Schema Free）。

  1. --
  2. -- SQL创建User表
  3. --
  4. create table user (
  5.     id bigint not null auto_increment,
  6.     user_name varchar(10),
  7.     age int,
  8.     primary key(id)
  9. );
  11. #
  12. # JSON定义的User表
  13. #
  14. db.user.insert({
  15.     user_name:"tom",
  16.     age:30
  17. })
  19. db.createCollection("user")

JSON VS BLOB

JSON

  • JSON 数据可以做有效性检查。
  • JSON 使得查询性能提升。
  • JSON 支持部分属性索引，通过虚拟列的功能可以对 JSON 中的部分数据进行索引。

BLOB

  • BLOB 类型无法在数据库层做约束性检查。
  • BLOB 进行查询，需要遍历所有字符串。
  • BLOB 做只能做指定长度的索引。

5.7之前，只能把 JSON 当作 BLOB 进行存储。数据库层面无法对 JSON 数据做一些操作，只能由应用程序处理。

JSON 操作示例

JSON入门

  1. --
  2. -- 创建带json字段的表
  3. --
  4. mysql> create table user (
  5.     -> uid int auto_increment,
  6.     -> data json,
  7.     -> primary key(uid)
  8.     -> );
  9. Query OK, 0 rows affected (0.11 sec)
  11. --
  12. -- 插入json数据
  13. --
  14. mysql> insert into user values (
  15.     -> null,  -- 自增长数据，可以插入null
  16.     -> '{
  17.     '> "name":"tom",
  18.     '> "age":18,
  19.     '> "address":"SZ"
  20.     '> }'
  21.     -> );
  22. Query OK, 1 row affected (0.03 sec)
  24. mysql> insert into user values (
  25.     -> null,
  26.     -> '{
  27.     '> "name":"jim",
  28.     '> "age":28,
  29.     '> "mail":"jim@163.com"
  30.     '> }'
  31.     -> );
  32. Query OK, 1 row affected (0.02 sec)
  34. mysql> insert into user values ( null, "can you insert it?");  -- 无法插入，因为是JSON类型
  35. ERROR 3140 (22032): Invalid JSON text: "Invalid value." at position 0 in value (or column) can you insert it?.  -- 这短话有单引号，但是渲染有问题，所以这里去掉了
  37. mysql> select * from user;
  38. +-----+---------------------------------------------------+
  39. | uid | data                                              |
  40. +-----+---------------------------------------------------+
  41. |   1 | {"age": 18, "name": "tom", "address": "SZ"}       |  -- 这个json中有address字段
  42. |   2 | {"age": 28, "mail": "jim@163.com", "name": "jim"} |  -- 这个json中有mail字段
  43. +-----+---------------------------------------------------+
  44. 2 rows in set (0.00 sec)

JSON 常用函数介绍

官方文档（JSON 函数）：https://dev.mysql.com/doc/refman/8.0/en/json-function-reference.html

  1. --
  2. -- 使用json_extract提取数据
  3. -- 原型 : JSON_EXTRACT(json_doc, path[, path] ...) 
  4. --
  5. mysql> select json_extract('[10, 20, [30, 40]]', '$[1]');                  
  6. +--------------------------------------------+
  7. | json_extract('[10, 20, [30, 40]]', '$[1]') |
  8. +--------------------------------------------+
  9. | 20                                         |  -- 从list中抽取 下标 为1的元素（下标从0开始）
  10. +--------------------------------------------+
  11. 1 row in set (0.00 sec)
  13. mysql> select
  14.     -> json_extract(data, '$.name'),    -- 提起name字段的数据
  15.     -> json_extract(data, '$.address')  -- 提取address字段的数据
  16.     -> from user;
  17. +------------------------------+---------------------------------+
  18. | json_extract(data, '$.name') | json_extract(data, '$.address') |
  19. +------------------------------+---------------------------------+
  20. | "tom"                        | "SZ"                            |
  21. | "jim"                        | NULL                            |  -- jim 没有address字段，填充了NULL
  22. +------------------------------+---------------------------------+
  23. 2 rows in set (0.00 sec)
  25. --
  26. -- json_object 将list(K-V对)封装成json格式
  27. -- 原型 : JSON_OBJECT([key, val[, key, val] ...])
  28. --
  29. mysql> select json_object("name", "jery", "email", "jery@163.com", "age",33);
  30. +----------------------------------------------------------------+
  31. | json_object("name", "jery", "email", "jery@163.com", "age",33) |
  32. +----------------------------------------------------------------+
  33. | {"age": 33, "name": "jery", "email": "jery@163.com"}           |  -- 封装成了K-V对
  34. +----------------------------------------------------------------+
  35. 1 row in set (0.00 sec)
  37. mysql> insert into user values ( 
  38.     -> null,
  39.     -> json_object("name", "jery", "email", "jery@163.com", "age",33)  -- 进行封装
  40.     -> );
  41. Query OK, 1 row affected (0.03 sec)
  43. mysql> select * from user;
  44. +-----+------------------------------------------------------+
  45. | uid | data                                                 |
  46. +-----+------------------------------------------------------+
  47. |   1 | {"age": 18, "name": "tom", "address": "SZ"}          |
  48. |   2 | {"age": 28, "mail": "jim@163.com", "name": "jim"}    |
  49. |   4 | {"age": 33, "name": "jery", "email": "jery@163.com"} |
  50. +-----+------------------------------------------------------+
  51. 3 rows in set (0.00 sec)
  54. --
  55. -- json_insert 插入数据
  56. -- 原型 : JSON_INSERT(json_doc, path, val[, path, val] ...)
  57. --
  58. mysql> set @j = '{ "a": 1, "b": [2, 3]}';
  59. Query OK, 0 rows affected (0.00 sec)
  61. mysql> select json_insert(@j, '$.a', 10, '$.c', '[true, false]');                 
  62. +----------------------------------------------------+
  63. | json_insert(@j, '$.a', 10, '$.c', '[true, false]') |
  64. +----------------------------------------------------+
  65. | {"a": 1, "b": [2, 3], "c": "[true, false]"}        |  -- a还是=1，存在的被忽略，不受影响
  66. +----------------------------------------------------+  -- c之前不存在，则插入
  67. 1 row in set (0.00 sec)
  69. mysql> update user set data = json_insert(data, "$.address_2", "BJ") where uid = 1;  -- 插入 addres_2
  70. Query OK, 1 row affected (0.03 sec)
  71. Rows matched: 1  Changed: 1  Warnings: 0
  73. mysql> select * from user;
  74. +-----+----------------------------------------------------------------+
  75. | uid | data                                                           |
  76. +-----+----------------------------------------------------------------+
  77. |   1 | {"age": 18, "name": "tom", "address": "SZ", "address_2": "BJ"} |  -- 增加了addres_2 : "BJ"
  78. |   2 | {"age": 28, "mail": "jim@163.com", "name": "jim"}              |
  79. |   4 | {"age": 33, "name": "jery", "email": "jery@163.com"}           |
  80. +-----+----------------------------------------------------------------+
  81. 3 rows in set (0.00 sec)
  83. --
  84. -- json_merge 合并数据并返回。注意：原数据不受影响
  85. -- 原型 : JSON_MERGE(json_doc, json_doc[, json_doc] ...)
  86. --
  87. mysql> select json_merge('{"name": "x"}', '{"id": 47}');    -- 原来有两个JSON             
  88. +-------------------------------------------+
  89. | json_merge('{"name": "x"}', '{"id": 47}') |
  90. +-------------------------------------------+
  91. | {"id": 47, "name": "x"}                   |  -- 合并多个JSON
  92. +-------------------------------------------+
  93. 1 row in set (0.00 sec)
  95. mysql> select 
  96.     -> json_merge(
  97.     -> json_extract(data, '$.address'),      -- json 1
  98.     -> json_extract(data, '$.address_2'))    -- jons 2
  99.     -> from user where uid = 1;
  100. +---------------------------------------------------------------------------------+
  101. | json_merge( json_extract(data, '$.address'), json_extract(data, '$.address_2')) |
  102. +---------------------------------------------------------------------------------+
  103. | ["SZ", "BJ"]                                                                    |  -- 合并成一个json
  104. +---------------------------------------------------------------------------------+
  105. 1 row in set (0.00 sec)
  107. --
  108. -- json_array_append 追加数据
  109. -- 原型 : JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...) 
  110. -- json_append 在5.7.9 中重命名为 json_array_append
  111. --
  112. mysql> set @j = '["a", ["b", "c"], "d"]';     -- 下标为1的元素中只有["b", "c"]
  113. Query OK, 0 rows affected (0.00 sec)
  115. mysql> select json_array_append(@j, '$[1]', 1);                       
  116. +----------------------------------+
  117. | json_array_append(@j, '$[1]', 1) |
  118. +----------------------------------+
  119. | ["a", ["b", "c", 1], "d"]        |    --  现在插入了 数字 1
  120. +----------------------------------+
  121. 1 row in set (0.00 sec)
  122. mysql> update user set data = json_array_append(
  123.     -> data,
  124.     -> '$.address',
  125.     -> json_extract(data, '$.address_2'))
  126.     -> where uid = 1;
  127. Query OK, 1 row affected (0.02 sec)
  128. Rows matched: 1  Changed: 1  Warnings: 0
  130. mysql> select * from user;
  131. +-----+------------------------------------------------------------------------+
  132. | uid | data                                                                   |
  133. +-----+------------------------------------------------------------------------+
  134. |   1 | {"age": 18, "name": "tom", "address": ["SZ", "BJ"], "address_2": "BJ"} | --address_2追加到address
  135. |   2 | {"age": 28, "mail": "jim@163.com", "name": "jim"}                      |
  136. |   4 | {"age": 33, "name": "jery", "email": "jery@163.com"}                   |
  137. +-----+------------------------------------------------------------------------+
  138. 3 rows in set (0.00 sec)
  141. --
  142. -- json_remove 从json记录中删除数据
  143. -- 原型 : JSON_REMOVE(json_doc, path[, path] ...)
  144. --
  145. mysql> set @j = '["a", ["b", "c"], "d"]';   
  146. Query OK, 0 rows affected (0.00 sec)
  148. mysql> select json_remove(@j, '$[1]');
  149. +-------------------------+
  150. | json_remove(@j, '$[1]') |
  151. +-------------------------+
  152. | ["a", "d"]              |  -- 删除了下标为1的元素["b", "c"]
  153. +-------------------------+
  154. 1 row in set (0.00 sec)
  156. mysql> update user set data = json_remove(data, "$.address_2") where uid = 1;
  157. Query OK, 1 row affected (0.03 sec)
  158. Rows matched: 1  Changed: 1  Warnings: 0
  160. mysql> select * from user;
  161. +-----+------------------------------------------------------+
  162. | uid | data                                                 |
  163. +-----+------------------------------------------------------+
  164. |   1 | {"age": 18, "name": "tom", "address": ["SZ", "BJ"]}  |  -- address_2 的字段删除了
  165. |   2 | {"age": 28, "mail": "jim@163.com", "name": "jim"}    |
  166. |   4 | {"age": 33, "name": "jery", "email": "jery@163.com"} |
  167. +-----+------------------------------------------------------+
  168. 3 rows in set (0.00 sec)

JSON 创建索引

JSON 类型数据本身无法直接创建索引，变相的通过将需要索引的 JSON 数据重新生成虚拟列（Virtual Columns）之后，对该列进行索引。

官方文档（JSON 创建索引）：https://dev.mysql.com/doc/refman/8.0/en/create-table.html#create-table-secondary-indexes-virtual-columns

新建表时创建 JSON 索引

  1. mysql> create table test_inex_1(
  2.     -> data json,
  3.     -> gen_col varchar(10) generated always as (json_extract(data, '$.name')),  -- 抽取data中的name， 生成新的一列，名字为gen_col
  4.     -> index idx (gen_col)  -- 将gen_col 作为索引
  5.     -> );
  6. Query OK, 0 rows affected (0.13 sec)
  8. mysql> show create table test_index_1；
  9. -- -----省略表格线-----
  10. | test_index_1 | CREATE TABLE `test_index_1` (
  11.   `data` json DEFAULT NULL,
  12.   `gen_col` varchar(10) GENERATED ALWAYS AS (json_extract(data, '$.name')) VIRTUAL,
  13.   KEY `idx` (`gen_col`)
  14. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
  15. -- -----省略表格线-----
  16. 1 row in set (0.00 sec)
  18. mysql> insert into test_index_1(data) values ('{"name":"tom", "age":18, "address":"SH"}');
  19. Query OK, 1 row affected (0.04 sec)
  21. mysql> insert into test_index_1(data) values ('{"name":"jim", "age":28, "address":"SZ"}');      
  22. Query OK, 1 row affected (0.03 sec)
  24. mysql> select * from test_index_1;
  25. +---------------------------------------------+---------+
  26. | data                                        | gen_col |
  27. +---------------------------------------------+---------+
  28. | {"age": 18, "name": "tom", "address": "SH"} | "tom"   |
  29. | {"age": 28, "name": "jim", "address": "SZ"} | "jim"   |
  30. +---------------------------------------------+---------+
  31. 2 rows in set (0.00 sec)
  33. mysql> select json_extract(data,"$.name") as username from test_index_1 where gen_col="tom";  -- 如果这样做，为空，原因如下
  34. Empty set (0.00 sec)
  36. mysql> select hex('"');
  37. +----------+
  38. | hex('"') |
  39. +----------+
  40. | 22       |  -- 双引号的 16进制
  41. +----------+
  42. 1 row in set (0.00 sec)
  44. mysql> select hex(gen_col) from test_index_1;
  45. +--------------+
  46. | hex(gen_col) |
  47. +--------------+
  48. | 226A696D22   |  -- 双引号本身也作为了存储内容
  49. | 22746F6D22   |
  50. +--------------+
  51. 2 rows in set (0.00 sec)
  53. mysql> select json_extract(data,"$.name") as username from test_index_1 where gen_col='"tom"';  -- 使用'"tome"',用单引号括起来
  54. +----------+
  55. | username |
  56. +----------+
  57. | "tom"    |  -- 找到了对应的数据
  58. +----------+
  59. 1 row in set (0.00 sec)
  61. mysql> explain select json_extract(data,"$.name") as username from test_index_1 where gen_col='"tom"'\G
  62. *************************** 1. row ***************************
  63.            id: 1
  64.   select_type: SIMPLE
  65.         table: test_index_1
  66.    partitions: NULL
  67.          type: ref 
  68. possible_keys: idx    -- 使用了 key idx
  69.           key: idx
  70.       key_len: 43
  71.           ref: const
  72.          rows: 1
  73.      filtered: 100.00
  74.         Extra: NULL
  75. 1 row in set, 1 warning (0.00 sec)
  77. ---
  78. --- 建立表的时候去掉双引用
  79. ---
  81. mysql> create table test_index_2 (
  82.     -> data json,
  83.     -> gen_col varchar(10) generated always as (
  84.     ->    json_unquote(    -- 使用json_unquote函数进行去掉双引号
  85.     ->             json_extract(data, "$.name")
  86.     ->    )),
  87.     -> key idx(gen_col)
  88.     -> );
  89. Query OK, 0 rows affected (0.13 sec)
  91. mysql> show create table test_index_2;
  92. -- -----省略表格线-----
  93. | test_index_2 | CREATE TABLE `test_index_2` (
  94.   `data` json DEFAULT NULL,
  95.   `gen_col` varchar(10) GENERATED ALWAYS AS (json_unquote(
  96.             json_extract(data, "$.name")
  97.    )) VIRTUAL,
  98.   KEY `idx` (`gen_col`)
  99. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
  100. -- -----省略表格线-----
  101. 1 row in set (0.00 sec)
  103. mysql> insert into test_index_2(data) values ('{"name":"tom", "age":18, "address":"SH"}');
  104. Query OK, 1 row affected (0.03 sec)
  106. mysql> insert into test_index_2(data) values ('{"name":"jim", "age":28, "address":"SZ"}');
  107. Query OK, 1 row affected (0.02 sec)
  109. mysql> select json_extract(data,"$.name") as username from test_index_2 where gen_col="tom";  -- 未加单引号
  110. +----------+
  111. | username |
  112. +----------+
  113. | "tom"    |  -- 可以找到数据
  114. +----------+
  115. 1 row in set (0.00 sec)
  117. mysql> explain select json_extract(data,"$.name") as username from test_index_2 where gen_col="tom"\G
  118. *************************** 1. row ***************************
  119.            id: 1
  120.   select_type: SIMPLE
  121.         table: test_index_2
  122.    partitions: NULL
  123.          type: ref
  124. possible_keys: idx   -- 使用了 key idx
  125.           key: idx
  126.       key_len: 43
  127.           ref: const
  128.          rows: 1
  129.      filtered: 100.00
  130.         Extra: NULL
  131. 1 row in set, 1 warning (0.00 sec)

修改已存在的表创建 JSON 索引

  1. --
  2. -- 使用之前的user表操作
  3. --
  4. mysql> show create table user;
  5. -- -----省略表格线-----
  6. | user  | CREATE TABLE `user` (
  7.   `uid` int(11) NOT NULL AUTO_INCREMENT,
  8.   `data` json DEFAULT NULL,
  9.   PRIMARY KEY (`uid`)
  10. ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 |
  11. -- -----省略表格线-----
  12. 1 row in set (0.00 sec)
  14. mysql> select * from user;
  15. +-----+------------------------------------------------------+
  16. | uid | data                                                 |
  17. +-----+------------------------------------------------------+
  18. |   1 | {"age": 18, "name": "tom", "address": ["SZ", "BJ"]}  |
  19. |   2 | {"age": 28, "mail": "jim@163.com", "name": "jim"}    |
  20. |   4 | {"age": 33, "name": "jery", "email": "jery@163.com"} |
  21. +-----+------------------------------------------------------+
  23. mysql> alter table user 
  24.     -> add user_name varchar(32)
  25.     -> generated always as (json_extract(data,"$.name")) virtual;
  26. Query OK, 0 rows affected (0.05 sec)
  27. Records: 0  Duplicates: 0  Warnings: 0
  28. -- virtual 关键字是不将该列的字段值存储，对应的是stored
  30. mysql> select user_name from user;  
  31. +-----------+
  32. | user_name |
  33. +-----------+
  34. | "tom"     |
  35. | "jim"     |
  36. | "jery"    |
  37. +-----------+
  38. 3 rows in set (0.00 sec)
  40. mysql> alter table user add index idx(user_name);          
  41. Query OK, 0 rows affected (0.13 sec)
  42. Records: 0  Duplicates: 0  Warnings: 0
  44. mysql> select * from user where user_name='"tom"';  -- 加单引号
  45. +-----+-----------------------------------------------------+-----------+
  46. | uid | data                                                | user_name |
  47. +-----+-----------------------------------------------------+-----------+
  48. |   1 | {"age": 18, "name": "tom", "address": ["SZ", "BJ"]} | "tom"     |
  49. +-----+-----------------------------------------------------+-----------+
  50. 1 row in set (0.00 sec)
  52. mysql> explain select * from user where user_name='"tom"'\G
  53. *************************** 1. row ***************************
  54.            id: 1
  55.   select_type: SIMPLE
  56.         table: user
  57.    partitions: NULL
  58.          type: ref
  59. possible_keys: idx   -- 使用了 key idx
  60.           key: idx
  61.       key_len: 131
  62.           ref: const
  63.          rows: 1
  64.      filtered: 100.00
  65.         Extra: NULL
  66. 1 row in set, 1 warning (0.00 sec)
  68. mysql> show create table user;
  69. -- -----省略表格线-----
  70. | user  | CREATE TABLE `user` (
  71.   `uid` int(11) NOT NULL AUTO_INCREMENT,
  72.   `data` json DEFAULT NULL,
  73.   `user_name` varchar(32) GENERATED ALWAYS AS (json_extract(data,"$.name")) VIRTUAL,
  74.   `user_name2` varchar(32) GENERATED ALWAYS AS (json_extract(data,"$.name")) VIRTUAL,
  75.   PRIMARY KEY (`uid`),
  76.   KEY `idx` (`user_name`)
  77. ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 |
  78. -- -----省略表格线-----
  79. 1 row in set (0.00 sec)

附录

  1. --
  2. -- 老师演示JSON的SQL
  3. --
  4. drop table if exists User;
  6. CREATE TABLE User (
  7.     uid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  8.     name VARCHAR(32) NOT NULL,
  9.     email VARCHAR(256) NOT NULL,
  10.     address VARCHAR(512) NOT NULL,
  11.     UNIQUE KEY (name),
  12.     UNIQUE KEY (email)
  13. );
  15. INSERT INTO User VALUES (NULL,'David','david@gmail','Shanghai ...');
  16. INSERT INTO User VALUES (NULL,'Amy','amy@gmail','Beijing ...');
  17. INSERT INTO User VALUES (NULL,'Tom','tom@gmail','Guangzhou ...');
  19. SELECT * FROM User;
  21. ALTER TABLE User ADD COLUMN address2 VARCHAR(512) NOT NULL;
  22. ALTER TABLE User ADD COLUMN passport VARCHAR(64) NOT NULL;
  24. DROP TABLE IF EXISTS UserJson;
  26. CREATE TABLE UserJson(
  27.     uid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  28.     data JSON
  29. );
  31. truncate table UserJson;
  33. insert into UserJson 
  34. SELECT 
  35.     uid,JSON_OBJECT('name',name,'email',email,'address',address) AS data
  36. FROM
  37.     User;
  39. SELECT * FROM UserJson; 
  41. SELECT uid,JSON_EXTRACT(data,'$.address2') from UserJson;
  43. UPDATE UserJson
  44. set data = json_insert(data,"$.address2","HangZhou ...")
  45. where uid = 1;
  47. SELECT JSON_EXTRACT(data,'$.address[1]') from UserJson;
  49. select json_merge(JSON_EXTRACT(data,'$.address') ,JSON_EXTRACT(data,'$.address2')) 
  50. from UserJson;
  52. begin;
  53. UPDATE UserJson
  54. set data = json_array_append(data,"$.address",JSON_EXTRACT(data,'$.address2'))
  55. where JSON_EXTRACT(data,'$.address2') IS NOT NULL AND uid >0;
  56. select JSON_EXTRACT(data,'$.address') from UserJson;
  57. UPDATE UserJson
  58. set data = JSON_REMOVE(data,'$.address2')
  59. where uid>0;
  60. commit;

作者：殷建卫 链接：https://www.yuque.com/yinjianwei/vyrvkf/whwpuf 来源：殷建卫 - 架构笔记 著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。