JEE Study|JAVA EE|企业级开发学习网

标题: MySQL JSON使用 [打印本页]
作者: JeeStudy    时间: 2020-7-12 00:35
标题: MySQL JSON使用
    要在MySQL中存储数据，就必须定义数据库和表结构（schema），这是一个主要限制。为了应对这一点，从MySQL 5.7 开始，MySQL 支持了 JSON（JavaScript Object Notation，JSON）数据类型。之前，这类数据不是单独的数据类型，会被存储为字符串。新的JSON数据类型提供了自动验证的JSON文档以及优化的存储格式。
JSON 文档以二进制格式存储，它提供以下功能：

定义表结构：
[SQL] 纯文本查看 复制代码
CREATE TABLE emp_details(

emp_no int primary key,

details json

);


插入数据：
[SQL] 纯文本查看 复制代码
INSERT INTO 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"} }'

);



检索JSON：
可以使用->和->>运算符检索JSON 列的字段：
如果不用引号检索数据， 可以使用->>运算符：


select emp_no,details->>'$.phone',details->'$.address.city' from emp_details;


json_pretty 函数

[SQL] 纯文本查看 复制代码
mysql> select emp_no,json_pretty(details) from 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)





mysql> select emp_no,details from emp_details;

+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| emp_no | details                                                                                                                                                                 |

+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| 1      | { "location": "IN", "phone": "+11800000000",

"email": "abc@example.com", "address": { "line1":

"abc", "line2": "xyz street", "city": "Bangalore",

"pin": "560103"} } |

+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

1 row in set (0.00 sec)



WHERE条件 查找:

可以在WHERE 子句中使用col ->> path 运算符来引用JSON的某一列：
select emp_no,details from emp_details where details->>'$.address.pin'='560103';
也可以使用JSON_CONTAINS 函数查询数据。如果找到了数据，则返回1 ，否则返回0 :
select json_contains_path(details,'one','$.address.line1','$.address.line5') from emp_details;

如何查询一个 key?  假设要检查 address.line1 是否存在：
select json_contains_path(details,'one','$.address.line1') from emp_details;
这里， one 表示至少应该存在一个键。假设要检查 address.line1 或者 .address.line2 是否存在， 代码如下：
select json_contains_path(details,'one','$.address.line1','$.address.line2') from emp_details;

如果要检查 address.line1 或者 .address.line2 同时存在，可以使用all。
select json_contains_path(details,'all','$.address.line1','$.address.line2') from emp_details;

修改：
可以使用三种不同的函数来修改数据： JSON_SET（） 、JSON_INSERT（）和 JSON_REPLACE（）。在MySQL 8 之前的版本中，我们还需要对整个列进行完整的更新，这并不是最佳的方法。
• JSON_SET （ ） ： 替换现有值并添加不存在的值。
假设要替换员工的p i n 码，并添加昵称的详细信息， 代码如下：
UPDATE emp_details SET details = JSON_SET(details, "$.address.pin", "560100", "$.nickname", "kai") WHERE emp_no = 1;


• JSON_INSERT （）：插入值，但不替换现有值。
假设你希望添加新列而不更新现有值，则可以使用JSON INSERT () :
UPDATE emp_details SET details=JSON_INSERT(details,"$.address.pin","560132","$.address.line4","AWing") WHERE emp_no=1;
在这种情况下， pin不会被更新，只会添加一个新的字段address.line4 。

• JSON_REPLACE （） ： 仅替换现有值。
假设只需要替换现有字段，不需要添加新字段，代码如下：
UPDATE emp_details SET details=JSON_REPLACE(details,"$.address.pin","560132","$.address.lines5","Landmark") WHERE emp_no = 1 ;
在这种情况下， line5 不会被添加， 只有pin 会被更新。

删除：
• JSON_REMOVE 能从JSON 文档中删除数据。
假设你不再需要地址中的lines ，删除它的代码如下：
UPDATE emp_details SET details=JSON_REMOVE(details,"$.address.lines") WHERE emp_no = 1 ;

• JSON_KEYS （）： 获取 JSON 文档中的所有键。
• JSON_LENGTH （） ： 给出 JSON 文档中的元素数。












欢迎光临 JEE Study|JAVA EE|企业级开发学习网 (http://jeestudy.com/) Powered by Discuz! X3.2