json 插入数据_Mysql 8新特性之(8):从此真正支持json数据类型

要在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', "$.address.line1", "$.address.line2")

from employees.emp_details;

返回值：1

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

select json_contains_path(details, 'all', "$.address.line1", "$.address.line5")

from employees.emp_details;

返回值：0

3. 修改

有三种函数来修改数据：

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

3.1. JSON_SET()

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

update employees.emp_details

set details = json_set(details, "$.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"

}

3.2. JSON_INSERT()

插入值，但不替换现有值

这种情况：$.address.pin不会被更新，只会添加一个新的字段$.address.line4

update employees.emp_details

set details = json_insert(details, "$.address.pin", "560132", "$.address.line4","A Wing")

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",

"line4": "A Wing"

},

"location": "IN",

"nickname": "kai"

}

3.3. JSON_REPLACE()

仅替换现有值

这种情况：$.address.line5不会被添加， 只有$.address.pin会被更新

update employees.emp_details

set details = json_replace(details, "$.address.pin", "560132", "$.address.line5","Landmark")

where emp_no = 1;

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": "560132",

"city": "Bangalore",

"line1": "abc",

"line2": "xyz street",

"line4": "A Wing"

},

"location": "IN",

"nickname": "kai"

}

4. 删除JSON_REMOVE

从JSON文档中删除数据

update employees.emp_details

set details = json_remove(details, "$.address.line4")

where emp_no = 1;

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": "560132",

"city": "Bangalore",

"line1": "abc",

"line2": "xyz street"

},

"location": "IN",

"nickname": "kai"

}

5. 其他函数

JSON_KEYS()：

获取JSON文档中的所有键

select json_keys(details),json_keys(details ->> "$.address")

from employees.emp_details

where emp_no= 1;

JSON_LENGTH()：给出JSON文档中的元素数

select json_length(details), json_length(details ->> "$.address")

from employees.emp_details

where emp_no= 1;

感谢收看本期Q程序员说，最后别忘点赞加关注哈！我接着继续整，有啥不爽留言。