MySQL8.0新特性—Json增强
要在MySQL中存储数据，就必须定义数据库和表结构(schema)，这是一个主要的限制。为了应对这一点，MySQL 5.7开始支持了JavaScript对象表示(JavaScript Object Notation，JSON)数据类型。之前，这类数据不是单独的数据类型，会被存储为字符串。新的json数据类型提供了自动验证的json文档以及优化的存储格式。
json文档以二进制格式存储，它提供以下功能：
- 对文档元素的快速读取访问。
- 当服务器再次读取
json文档时，不需要重新解析文本获取该值。 - 通过键或数组索引直接查找子对象或嵌套值，而不需要读取文档中的所有值。
一、创建数据
1.1 创建测试表
mysql> create table employees.emp_details (
-> emp_no int primary key,
-> details json
-> );
Query OK, 0 rows affected (0.17 sec)
mysql> desc employees.emp_details;
+---------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | NULL | |
| details | json | YES | | NULL | |
+---------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
1.2 构建json
1.2.1 json格式
mysql> insert into employees.emp_details (emp_no, details)
-> values ('1',
-> '{
'> "location":"IN",
'> "phone":"+11800000000",
'> "email":"abc@example.com",
'> "address":{
'> "line1":"abc",
'> "line2":"xyz street",
'> "city":"Bangalore",
'> "pin":"560103"
'> }
'> }'
-> );
Query OK, 1 row affected (0.13 sec)
mysql> select emp_no, details from employees.emp_details \G;
*************************** 1. row ***************************
emp_no: 1
details: {
"email": "abc@example.com",
"phone": "+11800000000",
"address": {
"pin": "560132",
"city": "Bangalore",
"line1": "abc",
"line2": "xyz street",
"line4": "A Wing"
},
"location": "IN",
"nickname": "kai"
}
1 row in set (0.00 sec)
1.2.2 json_object
8.0后可以根据json_object和json_array构建sql语句
语法如下：
JSON_OBJECT([key, val[, key, val] ...])
上面插入可修改成：
mysql> insert into employees.emp_details (emp_no, details)
-> values ('2',
-> JSON_OBJECT("location","IN","phone","11800000000","email","abc@example.com","address",
-> JSON_OBJECT("line1","abc","line2","xyz street","city","Bangalore","pin","560103"))
-> );
Query OK, 1 row affected (0.13 sec)
1.2.3 json_array
JSON_ARRAY([val[, val] ...])
mysql> insert into employees.emp_details (emp_no, details)
-> values ('3',
-> json_object("123",
-> json_array(json_object( 'id', 87, 'name', 'carrot' ),json_object( 'id', 88, 'name', 'carrot' ))
-> ));
query ok, 1 row affected (0.13 sec)
mysql> select details from employees.emp_details where emp_no = "3";
+-----------------------------------------------------------------------+
| details |
+-----------------------------------------------------------------------+
| {"123": [{"id": 87, "name": "carrot"}, {"id": 87, "name": "carrot"}]} |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
json_object单引号会自动转成双引号
1.2.4 结果集转json
json_arrayagg()将结果集作为单个json数组json_objectagg()返回，并将结果集作为单个json对象返回。
mysql> select o_id, attribute, value from t3;
+------+-----------+-------+
| o_id | attribute | value |
+------+-----------+-------+
| 2 | color | red |
| 2 | fabric | silk |
| 3 | color | green |
| 3 | shape | square|
+------+-----------+-------+
4 rows in set (0.00 sec)
mysql> select o_id, json_arrayagg(attribute) as attributes
-> from t3 group by o_id;
+------+---------------------+
| o_id | attributes |
+------+---------------------+
| 2 | ["color", "fabric"] |
| 3 | ["color", "shape"] |
+------+---------------------+
2 rows in set (0.00 sec)
mysql> select o_id, json_objectagg(attribute, value)
-> from t3 group by o_id;
+------+---------------------------------------+
| o_id | json_objectagg(attribute, value) |
+------+---------------------------------------+
| 2 | {"color": "red", "fabric": "silk"} |
| 3 | {"color": "green", "shape": "square"} |
+------+---------------------------------------+
2 rows in set (0.00 sec)
json_objectagg还可以配合排序、分区、最大最小等窗口函数。
-- 降序
mysql> select json_objectagg(c, i)
over (order by i desc) as json_object from t;
+-------------+
| json_object |
+-------------+
| {"key": 5} |
| {"key": 4} |
| {"key": 3} |
+-------------+
-- 最大
mysql> select json_objectagg(c, i)
over (order by i desc) as json_object from t limit 1;
+-------------+
| json_object |
+-------------+
| {"key": 5} |
+-------------+
1.3 检索JSON
mysql> select emp_no, json_extract(details, '$.address.pin') pin
-> from employees.emp_details where emp_no = "1";
+--------+----------+
| emp_no | pin |
+--------+----------+
| 1 | "560103" |
+--------+----------+
1 row in set (0.00 sec)
->是json_extract函数的简写。
可以使用->和->>运算符检索JSON列的字段：
mysql> select emp_no, details -> '$.address.pin' pin
-> from employees.emp_details where emp_no = "1";
+--------+----------+
| emp_no | pin |
+--------+----------+
| 1 | "560103" |
+--------+----------+
1 row in set (0.00 sec)
如果不用引号检索数据，可以使用->>运算符（推荐此方式）
mysql> select emp_no, details ->> '$.address.pin' pin
-> from employees.emp_detail swhere emp_no = "1";
+--------+--------+
| emp_no | pin |
+--------+--------+
| 1 | 560103 |
+--------+--------+
1 row in set (0.00 sec)
1.4 转义
JSON_QUOTE(string)通过用双引号字符包装字符串并转义内部引号和其他字符，将字符串引用为JSON值，然后将结果作为utf8mb4字符串返回。NULL如果参数是则返回NULL。
此函数通常用于生成有效的JSON字符串文字以包含在JSON文档中。
mysql> select json_quote('null'), json_quote('"null"');
+--------------------+----------------------+
| json_quote('null') | json_quote('"null"') |
+--------------------+----------------------+
| "null" | "\"null\"" |
+--------------------+----------------------+
mysql> select json_quote('[1, 2, 3]');
+-------------------------+
| json_quote('[1, 2, 3]') |
+-------------------------+
| "[1, 2, 3]" |
+-------------------------+
JSON_UNQUOTE()为取消转义并将结果作为utf8mb4字符串返回。
二、JSON函数
MySQL提供了许多处理json数据的函数，让我们看看最常用的几种函数。
2.1 优雅浏览
想要以优雅的格式显示json值，请使用json_pretty()函数
mysql> select emp_no, json_pretty(details)
-> from employees.emp_details \G;
*************************** 1. row ***************************
emp_no: 1
json_pretty(details): {
"email": "abc@example.com",
"phone": "+11800000000",
"address": {
"pin": "560103",
"city": "Bangalore",
"line1": "abc",
"line2": "xyz street"
},
"location": "IN"
}
1 row in set (0.00 sec)
2.2 查找
2.2.1 ->>
可以在WHERE子句中使用col ->> path运算符来引用JSON的某一列
mysql> select emp_no, details
-> from employees.emp_details
-> where details ->> '$.address.pin' = "560103";
mysql> select emp_no, details from employees.emp_details \G;
*************************** 1. row ***************************
emp_no: 1
details: {
"email": "abc@example.com",
"phone": "+11800000000",
"address": {
"pin": "560103",
"city": "Bangalore",
"line1": "abc",
"line2": "xyz street"
},
"location": "IN"
}
1 row in set (0.00 sec)
2.2.2 json_contains
也可以使用json_contains函数查询数据。如果找到了数据，则返回1，否则返回0
mysql> select json_contains(details ->> '$.address.pin',"560103")
-> from employees.emp_details;
+-----------------------------------------------------+
| json_contains(details ->> '$.address.pin',"560103") |
+-----------------------------------------------------+
| 1 |
+-----------------------------------------------------+
1 row in set (0.00 sec)
json_contains查询json_array
mysql> select *
-> from employees.city
-> where json_contains(city, json_object('city_name',"北京市"));
如何查询一个key？使用json_contains_path函数检查address.line1是否存在
mysql> select json_contains_path(details, 'one', "$.address.line1")
-> from employees.emp_details;
+-------------------------------------------------------+
| json_contains_path(details, 'one', "$.address.line1") |
+-------------------------------------------------------+
| 1 |
+-------------------------------------------------------+
1 row in set (0.00 sec)
one表示至少应该存在一个键，检查address.line1或者address.line2是否存在
mysql> select json_contains_path(details, 'one', "$.address.line1", "$.address.line2")
-> from employees.emp_details;
+--------------------------------------------------------------------------+
| json_contains_path(details, 'one', "$.address.line1", "$.address.line2") |
+--------------------------------------------------------------------------+
| 1 |
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)
如果要检查address.line1或者address.line5是否同时存在，可以使用all，而不是one
mysql> select json_contains_path(details, 'all', "$.address.line1", "$.address.line5")
-> from employees.emp_details;
+--------------------------------------------------------------------------+
| json_contains_path(details, 'all', "$.address.line1", "$.address.line5") |
+--------------------------------------------------------------------------+
| 0 |
+--------------------------------------------------------------------------+
1 row in set (0.00 sec)
2.2.3 json_table
json数据转换为表格数据
mysql> select *
-> from
-> json_table(
-> '[{"a":"3"},{"a":2},{"b":1},{"a":0},{"a":[1,2]}]',
-> "$[*]"
-> columns(
-> rowid for ordinality,
-> ac varchar(100) path "$.a" default '111' on empty default '999' on error,
-> aj json path "$.a" default '{"x": 333}' on empty,
-> bx int exists path "$.b"
-> )
-> ) as tt;
+-------+------+------------+------+
| rowid | ac | aj | bx |
+-------+------+------------+------+
| 1 | 3 | "3" | 0 |
| 2 | 2 | 2 | 0 |
| 3 | 111 | {"x": 333} | 1 |
| 4 | 0 | 0 | 0 |
| 5 | 999 | [1, 2] | 0 |
+-------+------+------------+------+
5 rows in set (0.00 sec)
当然派生表可以执行联查
mysql> select
-> c1, c2, JSON_EXTRACT(c3, '$.*')
-> from
-> t1 as m
-> join
-> JSON_TABLE(
-> m.c3,
-> '$.*'
-> columns(
-> at varchar(10) path '$.a' default '1' on empty,
-> bt varchar(10) path '$.b' default '2' on empty,
-> ct varchar(10) path '$.c' default '3' on empty
-> )
-> ) as tt
-> on
-> m.c1 > tt.at;
2.3 修改
可以使用三种不同的函数来修改数据：json_set()、json_insert()和json_replace()。在MySQL 8之前的版本中，我们还需要对整个列进行完整的更新，这并不是最佳的方法。
2.3.1 json_set
替换现有值并添加不存在的值
mysql> update employees.emp_details
-> set details = json_set(details, "$.address.pin", "560100", "$.nickname","kai")
-> where emp_no = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select emp_no, json_pretty(details)
-> from employees.emp_details \G;
*************************** 1. row ***************************
emp_no: 1
json_pretty(details): {
"email": "abc@example.com",
"phone": "+11800000000",
"address": {
"pin": "560100",
"city": "Bangalore",
"line1": "abc",
"line2": "xyz street"
},
"location": "IN",
"nickname": "kai"
}
1 row in set (0.00 sec)
2.3.2 json_insert
插入值，但不替换现有值
在这种情况下，$.address.pin不会被更新，只会添加一个新的字段$.address.line4
mysql> update employees.emp_details
-> set details = json_insert(details, "$.address.pin", "560132", "$.address.line4","A Wing")
-> where emp_no = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select emp_no, json_pretty(details)
-> from employees.emp_details\G;
*************************** 1. row ***************************
emp_no: 1
json_pretty(details): {
"email": "abc@example.com",
"phone": "+11800000000",
"address": {
"pin": "560100",
"city": "Bangalore",
"line1": "abc",
"line2": "xyz street",
"line4": "A Wing"
},
"location": "IN",
"nickname": "kai"
}
1 row in set (0.01 sec)
2.3.3 json_replace
仅替换现有值
在这种情况下，$.address.line5不会被添加，只有$.address.pin会被更新
mysql> update employees.emp_details
-> set details = json_replace(details, "$.address.pin", "560132", "$.address.line5","Landmark")
-> where emp_no = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select emp_no, json_pretty(details)
-> from employees.emp_details\G
*************************** 1. row ***************************
emp_no: 1
json_pretty(details): {
"email": "abc@example.com",
"phone": "+11800000000",
"address": {
"pin": "560132",
"city": "Bangalore",
"line1": "abc",
"line2": "xyz street",
"line4": "A Wing"
},
"location": "IN",
"nickname": "kai"
}
1 row in set (0.00 sec)
2.3.4 json_array_append
修改json数组中的值
mysql> set @j = '["a", ["b", "c"], "d"]';
mysql> select json_array_append(@j, '$[1]', 1);
+----------------------------------+
| json_array_append(@j, '$[1]', 1) |
+----------------------------------+
| ["a", ["b", "c", 1], "d"] |
+----------------------------------+
mysql> select json_array_append(@j, '$[0]', 2);
+----------------------------------+
| json_array_append(@j, '$[0]', 2) |
+----------------------------------+
| [["a", 2], ["b", "c"], "d"] |
+----------------------------------+
mysql> SELECT json_array_append(@j, '$[1][0]', 3);
+-------------------------------------+
| json_array_append(@j, '$[1][0]', 3) |
+-------------------------------------+
| ["a", [["b", 3], "c"], "d"] |
+-------------------------------------+
mysql> set @j = '{"a": 1, "b": [2, 3], "c": 4}';
mysql> select json_array_append(@j, '$.b', 'x');
+------------------------------------+
| json_array_append(@j, '$.b', 'x') |
+------------------------------------+
| {"a": 1, "b": [2, 3, "x"], "c": 4} |
+------------------------------------+
mysql> select json_array_append(@j, '$.c', 'y');
+--------------------------------------+
| json_array_append(@j, '$.c', 'y') |
+--------------------------------------+
| {"a": 1, "b": [2, 3], "c": [4, "y"]} |
+--------------------------------------+
2.3.5 json_array_insert
插入json的数组
mysql> set @j = '["a", {"b": [1, 2]}, [3, 4]]';
mysql> select json_array_insert(@j, '$[1]', 'x');
+------------------------------------+
| json_array_insert(@j, '$[1]', 'x') |
+------------------------------------+
| ["a", "x", {"b": [1, 2]}, [3, 4]] |
+------------------------------------+
mysql> select json_array_insert(@j, '$[100]', 'x');
+--------------------------------------+
| json_array_insert(@j, '$[100]', 'x') |
+--------------------------------------+
| ["a", {"b": [1, 2]}, [3, 4], "x"] |
+--------------------------------------+
mysql> select json_array_insert(@j, '$[1].b[0]', 'x');
+-----------------------------------------+
| json_array_insert(@j, '$[1].b[0]', 'x') |
+-----------------------------------------+
| ["a", {"b": ["x", 1, 2]}, [3, 4]] |
+-----------------------------------------+
mysql> select json_array_insert(@j, '$[2][1]', 'y');
+---------------------------------------+
| json_array_insert(@j, '$[2][1]', 'y') |
+---------------------------------------+
| ["a", {"b": [1, 2]}, [3, "y", 4]] |
+---------------------------------------+
mysql> select json_array_insert(@j, '$[0]', 'x', '$[2][1]', 'y');
+----------------------------------------------------+
| json_array_insert(@j, '$[0]', 'x', '$[2][1]', 'y') |
+----------------------------------------------------+
| ["x", "a", {"b": [1, 2]}, [3, 4]] |
+----------------------------------------------------+
2.4 删除
2.4.1 json_remove
json_remove能从JSON文档中删除数据
mysql> update employees.emp_details
-> set details = json_remove(details, "$.address.line4")
-> where emp_no = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select emp_no, json_pretty(details)
-> from employees.emp_details\G
*************************** 1. row ***************************
emp_no: 1
json_pretty(details): {
"email": "abc@example.com",
"phone": "+11800000000",
"address": {
"pin": "560132",
"city": "Bangalore",
"line1": "abc",
"line2": "xyz street"
},
"location": "IN",
"nickname": "kai"
}
1 row in set (0.00 sec)
2.5 合并
2.5.1 json_merge_patch
合并json文档，替换重复键的值，相同key的取最后的值
mysql> select json_merge_patch('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }',
> '{ "a": 5, "d":6 }');
+-------------------------------------------------------------------------------+
| json_merge_patch('{ "a": 1, "b":2 }','{ "a": 3, "c":4 }','{ "a": 5, "d":6 }') |
+-------------------------------------------------------------------------------+
| {"a": 5, "b": 2, "c": 4, "d": 6} |
+-------------------------------------------------------------------------------+
json_merge_patch也可以通过参数指定值来删除成员，达到json_remove的效果
mysql> update employees.emp_details
-> set details = json_merge_patch(details, '{"address":{"line4":null}}')
-> where emp_no = 1;
2.5.2 json_merge_preserve
合并JSON文档，保留重复键
mysql> select json_merge_preserve('{ "a": 1, "b": 2 }','{ "a": 3, "c": 4 }',
> '{ "a": 5, "d": 6 }');
+-------------------------------------------------------------------------------------+
| json_merge_preserve('{ "a": 1, "b": 2 }','{ "a": 3, "c": 4 }','{ "a": 5, "d": 6 }') |
+-------------------------------------------------------------------------------------+
| {"a": [1, 3, 5], "b": 2, "c": 4, "d": 6} |
+-------------------------------------------------------------------------------------+
2.6 其他函数
json_keys()：获取json文档中的所有键
mysql> select json_keys(details),json_keys(details ->> "$.address")
-> from employees.emp_details
-> where emp_no= 1;
+-------------------------------------------------------+------------------------------------+
| json_keys(details) | json_keys(details ->> "$.address") |
+-------------------------------------------------------+------------------------------------+
| ["email", "phone", "address", "location", "nickname"] | ["pin", "city", "line1", "line2"] |
+-------------------------------------------------------+------------------------------------+
1 row in set (0.00 sec)
json_length()：给出json文档中的元素数
mysql> select json_length(details), json_length(details ->> "$.address")
-> from employees.emp_details
-> where emp_no= 1;
+----------------------+--------------------------------------+
| json_length(details) | json_length(details ->> "$.address") |
+----------------------+--------------------------------------+
| 5 | 4 |
+----------------------+--------------------------------------+
1 row in set (0.00 sec)
json_type()：json值的类型
mysql> select json_type(json_extract( details, '$.address' ))
-> from employees.emp_details
-> where emp_no= 1;
+-------------------------------------------------+
| json_type(json_extract( details, '$.address' )) |
+-------------------------------------------------+
| OBJECT |
+-------------------------------------------------+
1 row in set (0.00 sec)
