要在MySQL中存储数据，必须定义数据库和表结构，但有时做配置后台开关项太多不可能定义几百个字段，用json方法放到一个一个字段里也是必要的。

为了应对这一点，从MySQL 5.7开始，MySQL支恃了 JavaScript对象表示(JavaScriptObject Notation，JSON) 数据类型。

之前，json数据不被支持，只是被存储为字符串。

mysql8JSON数据类型提供了自动验证的JSON文档以及优化的存储格式。

可以通过键或数组索引直接查找子对象或嵌套值，而不需要读取文档中的所有值。

实例测试

创建表

插入JSON

insert into employees.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 -> ‘$.address.pin’ pin from employees.emp_details;

如果返回值不要引号，用->> 运算符(推荐此方式)

select emp_no, details ->> ‘$.address.pin’ pin from employees.emp_details;

常用的JSON数据函数

1. 优雅浏览

JSON_PRETTY()优雅的格式显示JSON值

select emp_no, json_pretty(details)

from employees.emp_detailsG

************** 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”
}

2. 查找

可以在WHERE子句中使用col ->> path运算符来引用JSON的某一列

select emp_no, details

from employees.emp_details

where details ->> ‘$.address.pin’ = “560103”;

也可以用JSON_CONTAINS函数查询数据。

如果找到了数据，则返回1，否则返回0

select json_contains(details ->> ‘$.address.pin’,“560103”)

from employees.emp_details;

返回值：1

如何查询一个key？使用JSON_CONTAINS_PATH函数检查address. line1是否存在

select json_contains_path(details, ‘one’, “$.address.line1”)

from employees.emp_details;

返回值：1

one表示至少应该存在一个键，检查address.line1或者address.line2是否存在

select json_contains_path(details, ‘one’, “. a d d r e s s . l i n e 1 " , " .address.line1", ".address.line1",".address.line2”)

from employees.emp_details;

返回值：1

如果要检查address.line1或者address.line5是否同时存在，可以使用all，而不是one

select json_contains_path(details, ‘all’, “. a d d r e s s . l i n e 1 " , " .address.line1", ".address.line1",".address.line5”)

from employees.emp_details;

返回值：0

有三种函数来修改数据：

在MySQL 8之前的版本中，需要对整个列进行完整的更新，再写回去。

3.1. JSON_SET()

替换现有值并添加不存在的值

update employees.emp_details
set details = json_set(details, “. a d d r e s s . p i n " , " 560100 " , " .address.pin", "560100", ".address.pin","560100",".nickname”,“kai”)
where emp_no = 1;
Rows matched: 1 Changed: 1 Warnings: 0
select emp_no, json_pretty(details)
from employees.emp_detailsG
************ 1. row ****************
emp_no: 1
json_pretty(details): {
“email”: “abc@example.com”,
“phone”: “+11800000000”,
“address”: {
“pin”: “560100”,
“city”: “Bangalore”,
“line1”: “abc”,
“line2”: “xyz street”
},
“location”: “IN”,
“nickname”: “kai”
}