阮胜昌的技术记录站-LinuxMySQL数据库运维

前言
     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)