MySQL5.7 JSON类型使用介绍

JSON是一种轻量级的数据交换格式，采用了独立于语言的文本格式，类似XML，但是比XML简单，易读并且易编写。对机器来说易于解析和生成，并且会减少网络带宽的传输。JSON的格式非常简单：名称/键值。之前MySQL版本里面要实现这样的存储，要么用VARCHAR要么用TEXT大文本。 MySQL自5.7.8版本起开始支持JSON数据类型。JSON是一种轻量级的数据交换格式，它简单、易于阅读并且易于编写和解析。在数据库中存储JSON数据类型可以方便地处理开发中可能遇到的复杂数据结构。 我们先看看MySQL老版本的JSON存取。

JSON的使用场景

在开发过程中经常会遇见下面几种情况：

• 表中仅仅小部分数据需要新添加的字段；
• 当这个新添加的字段很有可能只是临时使用后续会废弃的时候；
• 当后面还不知道要新添加什么字段但大概率要添加的时候。

这些时候，使用一个JSON进行存储比较合适，不用更改表结构，非常方便。

插入操作

CREATE TABLE `json_test` (
  `id` int(11) DEFAULT NULL,
  `person_desc` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

我们来插入一条记录：

INSERT INTO json_test VALUES (1,'{
    "programmers": [{
        "firstName": "Brett",
        "lastName": "McLaughlin",
        "email": "aaaa"
    }, {
        "firstName": "Jason",
        "lastName": "Hunter",
        "email": "bbbb"
    }, {
        "firstName": "Elliotte",
        "lastName": "Harold",
        "email": "cccc"
    }],
    "authors": [{
        "firstName": "Isaac",
        "lastName": "Asimov",
        "genre": "sciencefiction"
    }, {
        "firstName": "Tad",
        "lastName": "Williams",
        "genre": "fantasy"
    }, {
        "firstName": "Frank",
        "lastName": "Peretti",
        "genre": "christianfiction"
    }],
    "musicians": [{
        "firstName": "Eric",
        "lastName": "Clapton",
        "instrument": "guitar"
    }, {
        "firstName": "Sergei",
        "lastName": "Rachmaninoff",
        "instrument": "piano"
    }]
}');

那一般我们遇到这样来存储JSON格式的话，只能把这条记录取出来交个应用程序，有应用程序来解析.

修改下表结构

-- update json_test set person_desc="{}" where person_desc=''
ALTER TABLE json_test MODIFY person_desc json;

 先看看插入的这行JSON数据有哪些KEY：

SELECT id,json_keys(person_desc) as "keys" FROM json_test

我们可以看到，里面有三个KEY，分别为authors，musicians，programmers。那现在找一个KEY把对应的值拿出来：

mysql> SELECT json_extract(AUTHORS,'$.lastName[0]') AS 'name', AUTHORS FROM
    -> (
    -> SELECT id,json_extract(person_desc,'$.authors[0][0]') AS "authors" FROM json_test
    -> UNION ALL
    -> SELECT id,json_extract(person_desc,'$.authors[1][0]') AS "authors" FROM json_test
    -> UNION ALL
    -> SELECT id,json_extract(person_desc,'$.authors[2][0]') AS "authors" FROM json_test
    -> ) AS T1
    -> ORDER BY NAME DESC\G
*************************** 1. row ***************************
   name: "Williams"
AUTHORS: {"genre": "fantasy", "lastName": "Williams", "firstName": "Tad"}
*************************** 2. row ***************************
   name: "Peretti"
AUTHORS: {"genre": "christianfiction", "lastName": "Peretti", "firstName": "Frank"}
*************************** 3. row ***************************
   name: "Asimov"
AUTHORS: {"genre": "sciencefiction", "lastName": "Asimov", "firstName": "Isaac"}

3 rows in set (0.00 sec)

查询操作 

JSON_EXTRACT(json_doc, path[, path] ...)

其中，json_doc 是 JSON 文档，path 是路径。该函数会从 JSON 文档提取指定路径（path）的元素。如果指定 path 不存在，会返回 NULL。可指定多个 path，匹配到的多个值会以数组形式返回。

JSON_SEARCH() 返回匹配的JSONPath

接下来以下面这个JSON文档为例看一下如何进行搜索：

{
	"a": "a_value",
	"b": ["1", "2", "3", "4", "5"],
	"c": true,
	"d": {
		"a": "a_value",
		"b": ["1", "2", "bvalue"]
	}
}

• json_search(@j, 'one', 'a_value')返回"$.a"；
• json_search(@j, 'all', 'a_value')返回["$.a", "$.d.a"]；
• json_search(@j, 'all', '1')返回["$.b[0]", "$.d.b[0]"]；
• json_search(@j, 'all', '%_value')返回["$.a", "$.d.a", "$.d.b[2]"]；
• json_search(@j, 'all', '%\_value')返回["$.a", "$.d.a"]，注意和上一个的区别；
• json_search(@j, 'all', '%|_value', '|')指定转义符，返回["$.a", "$.d.a"]；
• json_search(@j, 'all', '%|_value', '|', '$.a')指定了开始搜索的位置，返回"$.a"，没有匹配$.d.a；

JSON_CONTAINS(target, candidate[, path])

判断 target 文档是否包含 candidate 文档，如果包含，则返回 1，否则是 0。

select json_contains('{"a":1,"b":4}','{"a":1}')

JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)

判断指定的 path 是否存在，存在，则返回 1，否则是 0。

json_contains_path(price_json,'one','$.calledin')

函数中的 one_or_all 可指定 one 或 all，one 是任意一个路径存在就返回 1，all 是所有路径都存在才返回 1。

现在来把详细的值罗列出来：

mysql> SELECT
    -> json_extract(AUTHORS,'$.firstName[0]') AS "firstname",
    -> json_extract(AUTHORS,'$.lastName[0]') AS "lastname",
    -> json_extract(AUTHORS,'$.genre[0]') AS "genre"
    -> FROM
    -> (
    -> SELECT id,json_extract(person_desc,'$.authors[0]') AS "authors" FROM json
_test
    -> ) AS T\G
*************************** 1. row ***************************
firstname: "Isaac"
 lastname: "Asimov"
    genre: "sciencefiction"
1 row in set (0.00 sec)

MySQL中支持使用JSON_EXTRACT()函数以及->，->>操作符来获取JSON内部的元素

mysql> select * from videos;
+----+-----------------------------------------------------------------------------+
| id | ext                                                                         |
+----+-----------------------------------------------------------------------------+
|  1 | {"vid": "vid1", "logo": true, "tags": ["news", "china"], "title": "title1"} |
|  2 | {"vid": "vid2", "logo": false, "tags": [], "title": "title2"}               |
|  3 | {"vid": "vid3", "logo": false, "tags": ["food"], "title": "title3"}         |
+----+-----------------------------------------------------------------------------+
3 rows in set (0.04 sec)

mysql> select ext->'$.title' from videos;
+----------------+
| ext->'$.title' |
+----------------+
| "title1"       |
| "title2"       |
| "title3"       |
+----------------+
3 rows in set (0.04 sec)

 使用JSON_UNQUOTE()函数去掉引号

mysql> select json_unquote(ext->'$.title') from videos;
+------------------------------+
| json_unquote(ext->'$.title') |
+------------------------------+
| title1                       |
| title2                       |
| title3                       |
+------------------------------+
3 rows in set (0.04 sec)

还可以使用->>达到同样的效果（->>就是JSON_UNQUOTE(JSON_EXTRACT(...))的别名 

mysql> select ext->>'$.title' from videos;
+-----------------+
| ext->>'$.title' |
+-----------------+
| title1          |
| title2          |
| title3          |
+-----------------+
3 rows in set (0.04 sec)

删除操作

JSON_REMOVE

我们进一步来演示把authors 这个KEY对应的所有对象删掉。

mysql> UPDATE json_test
    -> SET person_desc = json_remove(person_desc,'$.authors')\G
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

查找下对应的KEY，发现已经被删除掉了。

mysql> SELECT json_contains_path(person_desc,'all','$.authors') as authors_exist
s FROM json_test\G
*************************** 1. row ***************************
authors_exists: 0
1 row in set (0.00 sec)

修改操作

JSON_INSERT

JSON_SET 除了可以更新元素的值之外，如果指定的元素不存在，还可以添加

update videos set ext = json_replace(ext, '$.protected', false) where ext->'$.vid' = 'vid1';

JSON_REPLACE 不过如果JSON中没有要更新的key，那么就什么也不做

update videos set ext = json_set(ext, '$.size', 100) where ext->'$.vid' = 'vid1';

JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...)

向数组指定位置追加元素。如果指定 path 不存在，则不添加。

JSON_ARRAY_INSERT(json_doc, path, val[, path, val] ...)

向数组指定位置插入元素。

JSON_MERGE_PATCH(json_doc, json_doc[, json_doc] ...)

MySQL 8.0.3 引入的，用来合并多个 JSON 文档。其合并规则如下：

1. 如果两个文档不全是 JSON 对象，则合并后的结果是第二个文档。

2. 如果两个文档都是 JSON 对象，且不存在着同名 KEY，则合并后的文档包括两个文档的所有元素，如果存在着同名 KEY，则第二个文档的值会覆盖第一个。

JSON_MERGE_PRESERVE(json_doc, json_doc[, json_doc] ...)

MySQL 8.0.3 引入的，用来代替 JSON_MERGE。也是用来合并文档，但合并规则与 JSON_MERGE_PATCH 有所不同。

1. 两个文档中，只要有一个文档是数组，则另外一个文档会合并到该数组中。

2. 两个文档都是 JSON 对象，若存在着同名 KEY ，第二个文档并不会覆盖第一个，而是会将值 append 到第一个文档中。

JSON的格式化

mysql> select json_pretty(ext) from videos\G
*************************** 1. row ***************************
json_pretty(ext): {
  "vid": "vid1",
  "logo": true,
  "tags": [
    "news",
    "china"
  ],
  "title": "title1"
}

JSON合法性校验。

select * from t where json_valid(c1) = 0;

使用 JSON 时的注意事项

• MySQL引入了JSON类型，可以方便地存储和查询JSON数据。然而，当我们使用JSON类型存储有序数据时，很容易出现顺序错乱的问题。为了解决MySQL JSON数据顺序错乱的问题，我们可以使用MySQL的JSON_ARRAY和JSON_OBJECT函数来保证顺序
• 在 MySQL 8.0.13 之前，不允许对 BLOB，TEXT，GEOMETRY，JSON 字段设置默认值。从 MySQL 8.0.13 开始，取消了这个限制
•  不允许直接创建索引，可创建函数索引。
• JSON 列的最大大小和 LONGBLOB（LONGTEXT）一样，都是 4G。
• 插入时，单个文档的大小受到 max_allowed_packet 的限制，该参数最大是 1G。
• mysql8.0.13之后，json允许默认值为null
• json列不能设置索引，可通过json中的键值设置索引来提高查询效率；
• json中null、true、false必须使用小写，