MySQL5.7 新特性之JSON类型

随着NOSQL数据库的广泛应用，可扩展的存储方式在关系型数据库中也有了很好的支持，MySQL5.7中就新增加了一个数据类型：JSON

JSON数据类型意义

其实，没有JSON数据类型的支持，我们一样可以通过varchar类型或者text等类型来保存这一格式的数据，但是，为什么还要专门增加这一数据格式的支持呢？其中肯定有较varchar或者text来存储此类型更优越的地方。

• 保证了JSON数据类型的强校验，JSON数据列会自动校验存入此列的内容是否符合JSON格式，非正常格式则报错，而varchar类型和text等类型本身是不存在这种机制的。
• MySQL同时提供了一组操作JSON类型数据的内置函数。
• 更优化的存储格式，存储在JSON列中的JSON数据会被转成内部特定的存储格式，允许快速读取。
• 可以基于JSON格式的特征支持修改特定的键值。（即不需要把整条内容拿出来放到程序中遍历然后寻找替换再塞回去，MySQL内置的函数允许你通过一条SQL语句就能搞定）

1、新增JSON类型

CREATE TABLE `user_copy` (
  `id` int(11) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `lastlogininfo` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SET FOREIGN_KEY_CHECKS=1;

2、创建JSON值

mysql> INSERT INTO t_json VALUES(JSON_ARRAY('json_array'));
Query OK, 1 row affected (0.19 sec)

mysql> INSERT INTO t_json VALUES(JSON_OBJECT('key','hello'));
Query OK, 1 row affected (0.09 sec)

mysql> INSERT INTO t_json VALUES(JSON_MERGE_PRESERVE(JSON_OBJECT('key','hello'),JSON_ARRAY(1,2)));
Query OK, 1 row affected (0.14 sec)

mysql> SELECT * FROM t_json;
+--------------------------------------+
| jdoc                                 |
+--------------------------------------+
| [1, 2]                               |
| {"key1": "value1", "key2": "value2"} |
| "HELLO"                              |
| ["json_array"]                       |
| {"key": "hello"}                     |
| [{"key": "hello"}, 1, 2]             |
+--------------------------------------+
6 rows in set (0.00 sec)

3、搜索和修改JSON值

路径语法

• .keyName：JSON对象中键名为keyName的值；
• 对于不合法的键名（如有空格），在路径引用中必须用双引号"将键名括起来，例."key name"；
• [index]：JSON数组中索引为index的值，JSON数组的索引同样从0开始；
• [index1 to index2]：JSON数组中从index1到index2的值的集合；
• .*: JSON对象中的所有value；
• [*]: JSON数组中的所有值；
• prefix**suffix: 以prefix开头并以suffix结尾的路径；
• **.keyName为多个路径，如对于JSON对象'{"a": {"b": 1}, "c": {"b": 2}}','$**.b'指路径$.a.b和$.c.b；
• 不存在的路径返回结果为NULL；
• 前导$字符表示当前正在使用的JSON文档；
• 例子：对于数组[3, {"a": [5, 6], "b": 10}, [99, 100]]
  • $[1]为{"a": [5, 6], "b": 10}；
  • [1].a为[5, 6]；
  • $[1].a[1]为 6；
  • $[1].b为 10；
  • $[2][0]为 99。

3.1 搜索

• JSON对象

  mysql> SELECT JSON_EXTRACT('{"id": 29, "name": "Taylor"}', '$.name');
  +--------------------------------------------------------+
  | JSON_EXTRACT('{"id": 29, "name": "Taylor"}', '$.name') |
  +--------------------------------------------------------+
  | "Taylor"                                               |
  +--------------------------------------------------------+
  1 row in set (0.00 sec)
  
  mysql> SELECT JSON_EXTRACT('{"id": 29, "name": "Taylor"}', '$.*');
  +-----------------------------------------------------+
  | JSON_EXTRACT('{"id": 29, "name": "Taylor"}', '$.*') |
  +-----------------------------------------------------+
  | [29, "Taylor"]                                      |
  +-----------------------------------------------------+
  1 row in set (0.00 sec)

• JSON数组

  mysql> SELECT JSON_EXTRACT('["a", "b", "c"]', '$[1]');
  +-----------------------------------------+
  | JSON_EXTRACT('["a", "b", "c"]', '$[1]') |
  +-----------------------------------------+
  | "b"                                     |
  +-----------------------------------------+
  1 row in set (0.00 sec)
  
  mysql> SELECT JSON_EXTRACT('["a", "b", "c"]', '$[1 to 2]');
  +----------------------------------------------+
  | JSON_EXTRACT('["a", "b", "c"]', '$[1 to 2]') |
  +----------------------------------------------+
  | ["b", "c"]                                   |
  +----------------------------------------------+
  1 row in set (0.00 sec)
  
  mysql> SELECT JSON_EXTRACT('["a", "b", "c"]', '$[*]');
  +-----------------------------------------+
  | JSON_EXTRACT('["a", "b", "c"]', '$[*]') |
  +-----------------------------------------+
  | ["a", "b", "c"]                         |
  +-----------------------------------------+
  1 row in set (0.00 sec)

3.2 修改

• JSON_REPLACE和JSON_SET的区别

  // 旧值存在
  mysql> SELECT JSON_REPLACE('{"id": 29, "name": "Taylor"}', '$.name', 'Mere');
  +----------------------------------------------------------------+
  | JSON_REPLACE('{"id": 29, "name": "Taylor"}', '$.name', 'Mere') |
  +----------------------------------------------------------------+
  | {"id": 29, "name": "Mere"}                                     |
  +----------------------------------------------------------------+
  1 row in set (0.00 sec)
  
  mysql> SELECT JSON_SET('{"id": 29, "name": "Taylor"}', '$.name', "Mere");
  +------------------------------------------------------------+
  | JSON_SET('{"id": 29, "name": "Taylor"}', '$.name', 'Mere') |
  +------------------------------------------------------------+
  | {"id": 29, "name": "Mere"}                                 |
  +------------------------------------------------------------+
  1 row in set (0.00 sec)
  
  // 旧值不存在
  mysql> SELECT JSON_REPLACE('{"id": 29, "name": "Taylor"}', '$.cat', 'Mere');
  +---------------------------------------------------------------+
  | JSON_REPLACE('{"id": 29, "name": "Taylor"}', '$.cat', 'Mere') |
  +---------------------------------------------------------------+
  | {"id": 29, "name": "Taylor"}                                  |
  +---------------------------------------------------------------+
  1 row in set (0.00 sec)
  
  mysql> SELECT JSON_SET('{"id": 29, "name": "Taylor"}', '$.cat', 'Mere');
  +-----------------------------------------------------------+
  | JSON_SET('{"id": 29, "name": "Taylor"}', '$.cat', 'Mere') |
  +-----------------------------------------------------------+
  | {"id": 29, "cat": "Mere", "name": "Taylor"}               |
  +-----------------------------------------------------------+
  1 row in set (0.00 sec)

• JSON_INSERT与JSON_SET的区别

  // 旧值存在
  mysql> SELECT JSON_INSERT('[1, 2, 3]', '$[1]', 4);
  +-------------------------------------+
  | JSON_INSERT('[1, 2, 3]', '$[1]', 4) |
  +-------------------------------------+
  | [1, 2, 3]                           |
  +-------------------------------------+
  1 row in set (0.00 sec)
  
  mysql> SELECT JSON_SET('[1, 2, 3]', '$[1]', 4);
  +----------------------------------+
  | JSON_SET('[1, 2, 3]', '$[1]', 4) |
  +----------------------------------+
  | [1, 4, 3]                        |
  +----------------------------------+
  1 row in set (0.00 sec)
  
  //旧值不存在
  mysql> SELECT JSON_INSERT('[1, 2, 3]', '$[4]', 4);
  +-------------------------------------+
  | JSON_INSERT('[1, 2, 3]', '$[4]', 4) |
  +-------------------------------------+
  | [1, 2, 3, 4]                        |
  +-------------------------------------+
  1 row in set (0.00 sec)
  
  mysql> SELECT JSON_SET('[1, 2, 3]', '$[4]', 4);
  +----------------------------------+
  | JSON_SET('[1, 2, 3]', '$[4]', 4) |
  +----------------------------------+
  | [1, 2, 3, 4]                     |
  +----------------------------------+
  1 row in set (0.00 sec)