MySQL JSON数据类型

MySQL 5.7.8开始对JSON数据类型的支持，JSON不再以字符串形式存储，而是转换为内部二进制存储格式，能够自动验证JSON内容，无效JSON文档会产生错误。二进制结构能够直接通过键或数组索引查找子对象或嵌套值，而无需遍历整个列。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


root@test 10:57:  create table tab_json(address json);
Query OK, 0 rows affected (0.04 sec)

root@test 11:02:  insert into tab_json values('{"country":"china","city":"shenzhen"}');
Query OK, 1 row affected (0.01 sec)

root@test 11:03:  insert into tab_json values('{"country":"china","city":"zhejiang","people":1000000}');
Query OK, 1 row affected (0.00 sec)

root@test 11:05:  select * from tab_json;
+-------------------------------------------------------------+
| address                                                     |
+-------------------------------------------------------------+
| {"city": "shenzhen", "country": "china"}                    |
| {"city": "zhejiang", "people": 1000000, "country": "china"} |
+-------------------------------------------------------------+

JSON_TYPE

JSON_TYPE函数需要传入一个JSON参数，并尝试将其解析为JSON值。如果有效，则返回JSON类型，否则返回错误：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23


root@test 11:21:  SELECT JSON_TYPE('["a", "b", 1]');
+----------------------------+
| JSON_TYPE('["a", "b", 1]') |
+----------------------------+
| ARRAY                      |
+----------------------------+

root@test 11:23:  SELECT JSON_TYPE('1');
+----------------+
| JSON_TYPE('1') |
+----------------+
| INTEGER        |
+----------------+

root@test 11:23:  SELECT JSON_TYPE('"ab"');
+-------------------+
| JSON_TYPE('"ab"') |
+-------------------+
| STRING            |
+-------------------+

root@test 11:23:  SELECT JSON_TYPE("ab");
ERROR 3141 (22032): Invalid JSON text in argument 1 to function json_type: "Invalid value." at position 0.

JSON_ARRAY

JSON_ARRARY函数接收值列表，并返回这些值的JSON数组

1
2
3
4
5
6


root@test 11:24:  SELECT JSON_ARRAY("shenzhen","shanghai","beijing");
+---------------------------------------------+
| JSON_ARRAY("shenzhen","shanghai","beijing") |
+---------------------------------------------+
| ["shenzhen", "shanghai", "beijing"]         |
+---------------------------------------------+

JSON_OBJECT

JSON_OBJECT函数接收键值对，并返回包含JSON对象

1
2
3
4
5
6


root@test 11:29:  SELECT JSON_OBJECT('NAME','LU','AGE',25,'ADDR','SHENZHEN');
+-----------------------------------------------------+
| JSON_OBJECT('NAME','LU','AGE',25,'ADDR','SHENZHEN') |
+-----------------------------------------------------+
| {"AGE": 25, "ADDR": "SHENZHEN", "NAME": "LU"}       |
+-----------------------------------------------------+

JSON_MERGE

JSON_MERGE函数接收两个或多个JSON文档并返回合并的结果

1
2
3
4
5
6
7


root@test 11:34:  SELECT JSON_MERGE('[1,2]','3','4');
+-----------------------------+
| JSON_MERGE('[1,2]','3','4') |
+-----------------------------+
| [1, 2, 3, 4]                |
+-----------------------------+
1 row in set, 1 warning (0.00 sec)

JSON_EXTRACT

JSON_EXTRACT函数能从JSON文档中查询指定键的值

1
2
3
4
5
6
7


root@test 13:42:  select json_extract(address,'$.city') from test.tab_json;
+--------------------------------+
| json_extract(address,'$.city') |
+--------------------------------+
| "shenzhen"                     |
| "zhejiang"                     |
+--------------------------------+

也可以使用column-»path的方式获取

1
2
3
4
5
6
7


root@test 13:37:  select address->>"$.city" from test.tab_json;
+--------------------+
| address->>"$.city" |
+--------------------+
| shenzhen           |
| zhejiang           |
+--------------------+

JSON_SET

JSON_SET函数替换现有的值，并添加不存在的值

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


root@test 13:56:  SET @j = '["a", {"b": [true, false]}, [10, 20]]';
Query OK, 0 rows affected (0.00 sec)

root@test 13:57:  select @j;
+---------------------------------------+
| @j                                    |
+---------------------------------------+
| ["a", {"b": [true, false]}, [10, 20]] |
+---------------------------------------+
1 row in set (0.00 sec)

root@test 13:57:  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

JSON_INSERT函数用于添加新值，但不替换现有值

1
2
3
4
5
6


root@test 13:57:  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

JSON_REPLACE函数替换现有值，并忽略新值

1
2
3
4
5
6


root@test 14:13:  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

JSON_REMOVE函数删除匹配的值

1
2
3
4
5
6


root@test 14:13:  SELECT JSON_REMOVE(@j, '$[2]', '$[1].b[1]');
+--------------------------------------+
| JSON_REMOVE(@j, '$[2]', '$[1].b[1]') |
+--------------------------------------+
| ["a", {"b": [true]}]                 |
+--------------------------------------+

数据大小写

通过转换JSON值产生的字符串的字符集为utf8mb4，排序规则为utf8mb4_bin，所以JSON值

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16


root@test 11:42:  SET @j = JSON_OBJECT('key', 'value');
Query OK, 0 rows affected (0.00 sec)

root@test 11:43:  SELECT CHARSET(@j), COLLATION(@j);
+-------------+---------------+
| CHARSET(@j) | COLLATION(@j) |
+-------------+---------------+
| utf8mb4     | utf8mb4_bin   |
+-------------+---------------+

root@test 11:39:  SELECT JSON_ARRAY('x') = JSON_ARRAY('X');
+-----------------------------------+
| JSON_ARRAY('x') = JSON_ARRAY('X') |
+-----------------------------------+
|                                 0 |
+-----------------------------------+

针对true、false或null也同样区分大小写，它们必须始终以小写形式编写

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17


root@test 13:33:  SELECT CAST('null' AS JSON);
+----------------------+
| CAST('null' AS JSON) |
+----------------------+
| null                 |
+----------------------+
1 row in set (0.00 sec)

root@test 13:33:  SELECT CAST('NULL' AS JSON);
ERROR 3141 (22032): Invalid JSON text in argument 1 to function cast_as_json: "Invalid value." at position 0

root@test 13:33:  SELECT ISNULL(null), ISNULL(Null), ISNULL(NULL);
+--------------+--------------+--------------+
| ISNULL(null) | ISNULL(Null) | ISNULL(NULL) |
+--------------+--------------+--------------+
|            1 |            1 |            1 |
+--------------+--------------+--------------+

JSON索引

不能直接对JSON列直接进行索引，只能通过虚拟列来引用JSON中的键值，并创建索引。

 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


root@test 14:16:  alter table tab_json add column city varchar(20) GENERATED ALWAYS AS (address->"$.city");
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@test 14:25:  show create table tab_json;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                                       |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tab_json | CREATE TABLE `tab_json` (
  `address` json DEFAULT NULL,
  `city` varchar(20) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (json_extract(`address`,_utf8mb4'$.city')) VIRTUAL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

root@test 14:25:  create index idx_city on tab_json(city);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@test 14:25:  explain select address->"$.city" as city from tab_json where city ='shenzhen';
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | tab_json | NULL       | ref  | idx_city      | idx_city | 83      | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+----------+-------+

更多关于JSON数据类型的内容参考：JSON Data Type