MySQL之数据类型JSON的使用方法

从MySQL 5.7之后，MySQL 支持 JavaScript Object Notation (JSON)数据类型

JSON使用

创建表emp_details，并属性details的数据类型为： json

CREATE TABLE emp_details(
emp_no int primary key,
details json
);

插入JSON数据

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数据

操作符：-> (去除的数据带引号) 和 ->> (取出的数据不带引号)

mysql> SELECT emp_no, details->'$.address.pin' pin FROM emp_details;

+--------+----------+
| emp_no | pin |
+--------+----------+
| 1 | "560103" |
+--------+----------+
1 row in set (0.00 sec)

mysql> SELECT emp_no, details->>'$.address.pin' pin FROM emp_details;

+--------+--------+
| emp_no | pin |
+--------+--------+
| 1 | 560103 |
+--------+--------+
1 row in set (0.00 sec)

引用json属性值

mysql> SELECT emp_no FROM emp_details WHERE details->>'$.address.pin'="560103";

+--------+
| emp_no |
+--------+
| 1 |
+--------+
1 row in set (0.00 sec)

JSON函数

#JSON_PRETTY()函数：将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": "+11800000000",
"address": {
"pin": "560103",
"city": "Bangalore",
"line1": "abc",
"line2": "xyz street"
},
"location": "IN"
}1

row in set (0.00 sec)

 

#搜索json中的属性值

#JSON_CONTAINS()函数：搜索数据。 如果找到数据，则返回1，返回0

#details中address.pin是否有560103，有返回 1 ，没有返回 0 

mysql> SELECT JSON_CONTAINS(details->>'$.address.pin', "560103") FROM emp_details;

+----------------------------------------------------+|
JSON_CONTAINS(details->>'$.address.pin', "560103")
| +----------------------------------------------------+|

1

#确定json对象是否包含属性ddress.line1，是返回 1，否则返回 0 

mysql> SELECT JSON_CONTAINS_PATH(details, 'one',"$.address.line1") FROM emp_details;

+-----------------------------------------------------+
| JSON_CONTAINS_PATH(details, 'one',"$.address.line1") |
+---------------------------------------------------+
| 1

#这里的one表示，key：address.line1和address.line5至少存在一个返回 1, 否则返回 0.

SELECT JSON_CONTAINS_PATH(details, 'one',"$.address.line1", "$.address.line5") FROM emp_details;

 

#使用以下函数修改json中的值

#JSON_SET(), JSON_INSERT(), JSON_REPLACE().

#JSON_SET()：替换现有值并添加不存在的值。

假设您要替换员工的密码并添加昵称的详细信息：

mysql> UPDATE emp_details SET details = JSON_SET(details, "$.address.pin","560100", "$.nickname", "kai") WHERE emp_no = 1;

 

JSON_INSERT()：插入值而不替换现有值

假设您要添加新列而不更新现有值; 您可以使用

mysql> UPDATE emp_details SET details=JSON_INSERT(details, "$.address.pin","560132", "$.address.line4", "A Wing") WHERE emp_no = 1;

#pin不会更新; 只会添加一个新的address.line4字段

 

JSON_REPLACE()：仅替换现有值

假设您只想替换字段而不添加新字段：

mysql> UPDATE emp_details SET details=JSON_REPLACE(details, "$.address.pin", "560132", "$.address.line5", "Landmark") WHERE emp_no = 1;

#line5 不会被添加. 只有 pin 的值会更新.

 

#JSON_REMOVE()函数 删除 JSON 文档中的数据.

假设您不再需要address中的line5：

mysql> UPDATE emp_details SET details=JSON_REMOVE(details, "$.address.line5") WHERE emp_no = 1;

 

#其他函数

#JSON_KEYS()函数:获得json文档中全部的key

mysql> SELECT JSON_KEYS(details) FROM emp_details WHERE emp_no = 1;

#JSON_LENGTH()函数：给出一个JSON文档中元素的数量：

mysql> SELECT JSON_LENGTH(details) FROM emp_details WHERE emp_no = 1;

json函数官方文档：

https://dev.mysql.com/doc/refman/8.0/en/json-function-reference.html.