MySQL JSON使用

JeeStudy

    要在MySQL中存储数据，就必须定义数据库和表结构（schema），这是一个主要限制。为了应对这一点，从MySQL 5.7 开始，MySQL 支持了 JSON（JavaScript Object Notation，JSON）数据类型。之前，这类数据不是单独的数据类型，会被存储为字符串。新的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> [b][color=#0000ff]select emp_no,json_pretty(details) from emp_details\G;[/color][/b]
*************************** 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> [b][color=#0000ff]select emp_no,details from emp_details;[/color][/b]
+--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 文档中的元素数。