CrazyAirhead

疯狂的傻瓜，傻瓜也疯狂——傻方能执著，疯狂才专注!

0%

Mysql的JSON类型01

发表于 更新于 分类于
本文字数： 8.4k 阅读时长 ≈ 8 分钟

概述

Mysql从5.7.8版本开始支持JSON字段，可以高效的处理JSON文档。相比字符串字段，JSON字段有下面的几处优势：

  • 自动的校验JSON格式，无效的文档会产生错误
  • 优化的存储格式（结构化的二进制格式），访问数据时不需要额外的解析，可能通过键或者数组索引来直接该问数据，而不是读取整个字段。

JSON字段的存储空间要求与LONGBLOB和LONGTEXT是一样的（L + 4 bytes, where L < 2^32）。同时JSON字段的默认值只能是NULL。

为了操作JSON字段，提供了一系列的JSON和GeoJSON函数。

和其他的二进制类型一样，JSON字段不支持索引，但可以通过提取值的方式生成（虚拟）索引。

创建JSON值

新增JSON类型字段与其他类型没什么不同，插入时可以直接按字符串的方式进行插入，但必须是合法的JSON格式，如果不是语法直接报错。另外需要注意的是JSON字段使用utf8mb4字符集和uft8mb4_bin排序，因此1.其他字符集的字符串会被转换，2.JSON文档旭大小写敏感的，null，true，false只能用小写。

下面是官网的一些例子和自己的补充。

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> CREATE TABLE t1 (jdoc JSON);
Query OK, 0 rows affected (0.20 sec)

mysql> INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO t1 VALUES('[1, 2,');
ERROR 3140 (22032) at line 2: Invalid JSON text:
"Invalid value." at position 6 in value (or column) '[1, 2,'.

--下面语法也是能成功插入
mysql> insert into t1 values('true');
mysql> insert into t1 VALUES('1');

对于不太确定是否是MySql中支持的json内容，可以通过JSON_TYPE()进行测试。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
mysql> SELECT JSON_TYPE('["a", "b", 1]');
+----------------------------+
| JSON_TYPE('["a", "b", 1]') |
+----------------------------+
| ARRAY                      |
+----------------------------+

mysql> SELECT JSON_TYPE('"hello"');
+----------------------+
| JSON_TYPE('"hello"') |
+----------------------+
| STRING               |
+----------------------+

mysql> SELECT JSON_TYPE('hello');
ERROR 3146 (22032): Invalid data type for JSON data in argument 1
to function json_type; a JSON string or JSON type is required.


mysql> select JSON_TYPE('true');
+----------------------+
| JSON_TYPE('true')    |
+----------------------+
| BOOLEAN              |
+----------------------+
mysql> select JSON_TYPE('1');
+----------------------+
| JSON_TYPE('1')       |
+----------------------+
| INTEGER              |
+----------------------+
mysql> select JSON_TYPE('2019-01-01');
ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_type: "The document root must not be followed by other values." at position 4.

通过JSON_ARRAY()可以生成JSON数组。

1
2
3
4
5
6
mysql> SELECT JSON_ARRAY('a', 1, NOW());
+----------------------------------------+
| JSON_ARRAY('a', 1, NOW())              |
+----------------------------------------+
| ["a", 1, "2015-07-27 09:43:47.000000"] |
+----------------------------------------+

通过JSON_OBJECT()可以生成JSON对象。

1
2
3
4
5
6
mysql> SELECT JSON_OBJECT('key1', 1, 'key2', 'abc');
+---------------------------------------+
| JSON_OBJECT('key1', 1, 'key2', 'abc') |
+---------------------------------------+
| {"key1": 1, "key2": "abc"}            |
+---------------------------------------+

如果使用JSON_OBJECT()插入数据需要处理引号(“)。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
mysql> CREATE TABLE facts (sentence JSON);

--官网中说这个会无效，实际测试中是有效的。
mysql> INSERT INTO facts VALUES
     >   (JSON_OBJECT("mascot", "Our mascot is a dolphin named \"Sakila\"."));

--官网中说需要使用这个，实际测试时却报错。
mysql> INSERT INTO facts VALUES
     >   ('{"mascot": "Our mascot is a dolphin named \\"Sakila\\"."}');
Unknown command '\\'.
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\\"."))' at line 1  

mysql> SELECT sentence FROM facts;
+---------------------------------------------------------+
| sentence                                                |
+---------------------------------------------------------+
| {"mascot": "Our mascot is a dolphin named \"Sakila\"."} |
+---------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT col->"$.mascot" FROM qtest;
ERROR 1146 (42S02): Table 'test.qtest' doesn't exist
mysql> SELECT sentence->"$.mascot" FROM facts;
+---------------------------------------------+
| sentence->"$.mascot"                        |
+---------------------------------------------+
| "Our mascot is a dolphin named \"Sakila\"." |
+---------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT sentence->>"$.mascot" FROM facts;
+-----------------------------------------+
| sentence->>"$.mascot"                   |
+-----------------------------------------+
| Our mascot is a dolphin named "Sakila". |
+-----------------------------------------+
1 row in set (0.00 sec)

其实如果要简单点就是用单引号(‘)。

1
2
mysql> INSERT INTO facts VALUES(JSON_OBJECT('mascot', 'Our mascot is a dolphin named "Sakila".'));
Query OK, 1 row affected (0.00 sec)

而通过JSON_MERGE()可以合并两个JSON文档。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
mysql> SELECT JSON_MERGE('["a", 1]', '{"key": "value"}');
+--------------------------------------------+
| JSON_MERGE('["a", 1]', '{"key": "value"}') |
+--------------------------------------------+
| ["a", 1, {"key": "value"}]                 |
+--------------------------------------------+

mysql> SELECT JSON_MERGE('{"key1":"value1"}', '{"key2":"value2"}');
+------------------------------------------------------+
| JSON_MERGE('{"key1":"value1"}', '{"key2":"value2"}') |
+------------------------------------------------------+
| {"key1": "value1", "key2": "value2"}                 |
+------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT JSON_MERGE('1', '{"key2":"value2"}');
+--------------------------------------+
| JSON_MERGE('1', '{"key2":"value2"}') |
+--------------------------------------+
| [1, {"key2": "value2"}]              |
+--------------------------------------+
1 row in set, 1 warning (0.00 sec)

以上例子可以看出JSON_MEGER是根据两个JSON文档的类型来结构返回类型的，类型相同时不转换，类型不同时转为JSON数组。还有些细节可能参看官网Normalization, Merging, and Autowrapping of JSON Values。

查询和修改JSON值

通过JSON_EXTRACT()可以提取JSON文档值，具体语法说明参看JSON Path Syntax

1
2
3
4
5
6
mysql> SELECT JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name');
+---------------------------------------------------------+
| JSON_EXTRACT('{"id": 14, "name": "Aztalan"}', '$.name') |
+---------------------------------------------------------+
| "Aztalan"                                               |
+---------------------------------------------------------+

通过JSON_SET()来修改值。

1
2
3
4
5
6
7
8
mysql> SET @j = '["a", {"b": [true, false]}, [10, 20]]';

mysql> SELECT JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2);
+--------------------------------------------+
| JSON_SET(@j, '$[1].b[0]', 1, '$[2][2]', 2) |
+--------------------------------------------+
| ["a", {"b": [1, false]}, [10, 20, 2]]      |
+--------------------------------------------+

通过JSON_INSERT()来插入新值，但不替换旧值。

1
2
3
4
5
6
mysql> SELECT JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2);
+-----------------------------------------------+
| JSON_INSERT(@j, '$[1].b[0]', 1, '$[2][2]', 2) |
+-----------------------------------------------+
| ["a", {"b": [true, false]}, [10, 20, 2]]      |
+-----------------------------------------------+

通过JSON_REPLACE()来替换旧值而忽略新值。

1
2
3
4
5
6
mysql> SELECT JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2);
+------------------------------------------------+
| JSON_REPLACE(@j, '$[1].b[0]', 1, '$[2][2]', 2) |
+------------------------------------------------+
| ["a", {"b": [1, false]}, [10, 20]]             |
+------------------------------------------------+

通过JSON_REMOVE()来移除文档中的值。

1
2
3
4
5
6
mysql> SELECT JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]');
+---------------------------------------------------+
| JSON_REMOVE(@j, '$[2]', '$[1].b[1]', '$[1].b[1]') |
+---------------------------------------------------+
| ["a", {"b": [true]}]                              |
+---------------------------------------------------+

聚合查询

对于JSON文档的聚合查询（MIN，MAX，GROUP_CONCAT）没有什么实际意义，是不支持的。

欢迎关注我的其它发布渠道