MySQL json字段类型详解
前言
JSON我相信大家都已经很熟悉了，但在 MySQL中，直至 5.7 版本中，才正式引入 JSON数据类型。在次之前，我们通常使varchar或text数据类型存储JSON格式数据。
如何使用JSON？
在这一章节，不再具体介绍JSON格式以及语法，我们来介绍一下，在MySQL中如何创建JSON数据列。 语法:
create table t_base_data(
id bigint(20) not null primary key auto_increment,
content json)
新增数据:
insert into t_base_data (content)values('{"blog": "https://blog.csdn.net/qq_24549805", "account": "anzy"}');
注意：
1. JSON列存储的必须是JSON格式数据，否则会报错。([SQL]insert into t_base_data (content)values('{blog": "https://blog.csdn.net/qq_24549805", "account": "anzy"}');
[Err] 3140 - Invalid JSON text: "Missing a name for object member." at position 1 in value for column 't_base_data.content'.)
2. JSON数据类型是没有默认值的。
更新数据:
UPDATE t_base_data SET `content` = '{"blog":"测试更新啦","account":"pine"}' WHERE `id` = 1;
查询数据:
SELECT * from t_base_data;
select JSON_EXTRACT(content,'$.blog'),JSON_EXTRACT(content,'$.account') from t_base_data;
返回结果如图：
可以看出JSON被解析拆分，但对于字符串会保留双引号，这种是利用函数方法进行JSON提取的，还可以利用虚列virtual
select JSON_EXTRACT(content,'$.blog'),JSON_EXTRACT(content,'$.account'),JSON_EXTRACT(content,'$.age') from t_base_data;
有了虚列后就可以直接用虚列作为条件查询
1. explain select blog from t_base_data where blog = 'https://blog.csdn.net/qq_24549805';
2. select blog from t_base_data where blog = '"https://blog.csdn.net/qq_24549805"'
通过执行计划可以看出是否走索引和where条件，对于虚列还可以增加索引，就像普通的列一样，不过更新时不需要对虚列再进行更新，直接更新JSON的内容后，虚列会同步更新，因为虚列其实就是个引用，不会冗余存储
增加虚列索引的SQL：
alter table t_base_data add index index_virtual (blog);
再用执行计划查看是否走索引
explain select blog from t_base_data where blog = 'https://blog.csdn.net/qq_24549805'; 
从结果上看已经走索引了，优化效果达到。
大家尽量每一步sql都执行一下看看结果，有助于理解。
案例：
创建表：
(root@node101_master) [test]> create table emp_details(emp_no int primary key,details json);
Query OK, 0 rows affected (0.36 sec)
插入数据：
(root@node101_master) [test]> insert into emp_details(emp_no,details) values('1','{"location":"IN","phone":"+118000000","email":"abc@example.com","address":{"line1":"abc","line2":"xyz street","city":"Bangalore","pin":"560103"}}');
Query OK, 1 row affected (1.02 sec)
检索数据：
可以使用->和->>运算符检索JSON列的字段
(root@node101_master) [test]> select emp_no,details->'$.address.pin' pin from emp_details;
+--------+----------+
| emp_no | pin |
+--------+----------+
| 1 | "560103" |
+--------+----------+
1 row in set (0.00 sec)
(root@node101_master) [test]> select emp_no,details->'$.email' pin from emp_details;
+--------+-------------------+
| emp_no | pin |
+--------+-------------------+
| 1 | "abc@example.com" |
+--------+-------------------+
1 row in set (0.00 sec)
(root@node101_master) [test]> select emp_no,details->'$.address.line2' pin from emp_details;
+--------+--------------+
| emp_no | pin |
+--------+--------------+
| 1 | "xyz street" |
+--------+--------------+
1 row in set (0.00 sec)
JSON函数：
mysql> select emp_no,json_pretty(details) from emp_details\G;
*************************** 1. row ***************************
emp_no: 1
json_pretty(details): {
"email": "abc@example.com",
"phone": "+118000000",
"address": {
"pin": "560103",
"city": "Bangalore",
"line1": "abc",
"line2": "xyz street"
},
"location": "IN"
}
1 row in set (0.01 sec)
正常显示：
(root@node101_master) [test]> select * from emp_details;
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| emp_no | details |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | {"email": "abc@example.com", "phone": "+118000000", "address": {"pin": "560103", "city": "Bangalore", "line1": "abc", "line2": "xyz street"}, "location": "IN"} |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
查找：
可以在where子句中使用col->>path运算符来引用JSON的某一列：
mysql> select emp_no from emp_details where details->>'$.address.pin'="560103";
+--------+
| emp_no |
+--------+
| 1 |
+--------+
1 row in set (0.00 sec)
也可以使用JSON_CONTAINS函数查询数据，如果找到了数据，则返回1，否则返回0
mysql> select json_contains(details->>'$.address.pin',"560103") from emp_details;
+---------------------------------------------------+
| json_contains(details->>'$.address.pin',"560103") |
+---------------------------------------------------+
| 1 |
+---------------------------------------------------+
1 row in set (0.00 sec)
检查address.line1这个KEY是否存在：
mysql> select json_contains_path(details,'one',"$.address.line1") from emp_details;
+-----------------------------------------------------+
| json_contains_path(details,'one',"$.address.line1") |
+-----------------------------------------------------+
| 1 |
+-----------------------------------------------------+
1 row in set (0.00 sec)
mysql> select json_contains_path(details,'one',"$.address.line1","$.address.line5") from emp_details;
+-----------------------------------------------------------------------+
| json_contains_path(details,'one',"$.address.line1","$.address.line5") |
+-----------------------------------------------------------------------+
| 1 |
+-----------------------------------------------------------------------+
1 row in set (0.01 sec)
以上的one表示至少应该存在一个键
如果要检查address.line1和address.line5是否同时存在，可以使用all,而不是one：
mysql> select * from emp_details;
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| emp_no | details |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | {"email": "abc@example.com", "phone": "+118000000", "address": {"pin": "560103", "city": "Bangalore", "line1": "abc", "line2": "xyz street"}, "location": "IN"} |
+--------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select json_contains_path(details,'all',"$.address.line1","$.address.line5") from emp_details;
+-----------------------------------------------------------------------+
| json_contains_path(details,'all',"$.address.line1","$.address.line5") |
+-----------------------------------------------------------------------+
| 0 |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
返回0，表示line1和line5不同时存在
修改：
可以使用三种不同的函数来修改数据：
JSON_SET() #替换现有值并添加不存在的值
JSON_INSERT() #插入值，但不替换现在值
JSON_REPLACE() #仅替换现在值，不需要添加新字段
JSON_SET() 替换员工的PIN码，并添加昵称详细信息：
mysql> update 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 * from emp_details;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| emp_no | details |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | {"email": "abc@example.com", "phone": "+118000000", "address": {"pin": "560100", "city": "Bangalore", "line1": "abc", "line2": "xyz street"}, "location": "IN", "nickname": "kai"} |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
JSON_INSERT
mysql> update 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 * from emp_details;
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| emp_no | details |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | {"email": "abc@example.com", "phone": "+118000000", "address": {"pin": "560100", "city": "Bangalore", "line1": "abc", "line2": "xyz street", "line4": "A Wing"}, "location": "IN", "nickname": "kai"} |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
JSON_REPLACE()
mysql> select * from emp_details;
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| emp_no | details |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | {"email": "abc@example.com", "phone": "+118000000", "address": {"pin": "560100", "city": "Bangalore", "line1": "abc", "line2": "xyz street", "line4": "A Wing"}, "location": "IN", "nickname": "kai"} |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> update emp_details set details=json_replace(details,"$.address.pin","560132","$.address.line5","Landmark") where emp_no=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from emp_details;
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| emp_no | details |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | {"email": "abc@example.com", "phone": "+118000000", "address": {"pin": "560132", "city": "Bangalore", "line1": "abc", "line2": "xyz street", "line4": "A Wing"}, "location": "IN", "nickname": "kai"} |
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
因为没有line5，所有line5不会被添加，只有pin值被更新
删除：
JSON_REMOVE 能从JSON文档中删除数据
mysql> select * from emp_details;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| emp_no | details |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | {"email": "abc@example.com", "phone": "+118000000", "address": {"pin": "560132", "city": "Bangalore", "line1" "abc", "line2": "xyz street", "line4": "A Wing"}, "location": "IN", "nickname": "kai"} |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> update 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 * from emp_details;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| emp_no | details |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | {"email": "abc@example.com", "phone": "+118000000", "address": {"pin": "560132", "city": "Bangalore", "line1": "abc", "line2": "xyz street"}, "location": "IN", "nickname": "kai"} |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
以上删除line4的KEY以及值
其它函数：
JSON_KEYS()获取JSON文档中的所有键
mysql> select * from emp_details;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| emp_no | details |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1 | {"email": "abc@example.com", "phone": "+118000000", "address": {"pin": "560132", "city": "Bangalore", "line1": "abc", "line2": "xyz street"}, "location": "IN", "nickname": "kai"} |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select json_keys(details) from emp_details where emp_no=1;
+-------------------------------------------------------+
| json_keys(details) |
+-------------------------------------------------------+
| ["email", "phone", "address", "location", "nickname"] |
+-------------------------------------------------------+
1 row in set (0.00 sec)
JSON_LENGTH获取JSON文档中的元素的个数(key的个数)
mysql> select json_length(details) from emp_details where emp_no=1;
+----------------------+
| json_length(details) |
+----------------------+
| 5 |
+----------------------+
1 row in set (0.00 sec)
