MySQL 8 存储JSON

深碍√TFBOYSˉ_ 2022-09-12 10:44 97阅读 0赞

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

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

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

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

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

# 实例测试 #

创建表

![249ae9324c032edd98f6c99974dcfb34.png][]

# 插入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”\}\}’
> 
> );

![5cf09ae24e6795849bcda2e7b78e2a8f.png][]

# 检索JSON #

可以使用->和->>运算符检索JSON列的字段：

> select emp\_no, details -> ‘$.address.pin’ pin from employees.emp\_details;

![6b28ec63a8e6fb6533b1eb304c518a26.png][]

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

> select emp\_no, details ->> ‘$.address.pin’ pin from employees.emp\_details;

![1877d855fd1715949bde1e7d3dfd0df2.png][]

# 常用的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”;

![637e1ac20da6f3b2829ca365f6072c21.png][]

也可以用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”

[249ae9324c032edd98f6c99974dcfb34.png]: /images/20220828/89e040ab3de2469d83a5b39db5434e80.png
[5cf09ae24e6795849bcda2e7b78e2a8f.png]: /images/20220828/6d39fcaf72f54879b08efee1b55469c3.png
[6b28ec63a8e6fb6533b1eb304c518a26.png]: /images/20220828/86e98ad9aaf74c4ba8a1764c30324218.png
[1877d855fd1715949bde1e7d3dfd0df2.png]: /images/20220828/f55a2f80546b4205997a38d7075b60db.png
[637e1ac20da6f3b2829ca365f6072c21.png]: /images/20220828/e530e682204942d8945750ae386fcc30.png