mysql操作--json/数组的增删该查

我不是女神ヾ 2022-12-05 00:40 545阅读 0赞

> 前言，类型必须是json，虽然text也可以，但是很多操作没法使用，比如查询，当然了，这种类型还可以存储数组  
> 类似 varchar，设置 JSON 主要将字段的 type 是 json, 不能设置长度，可以是 NULL 但不能有默认值。

### 总结 ###

分类 函数 描述  
创建json  
json\_array 创建json数组  
json\_object 创建json对象  
查询json  
json\_contains 判断是否包含某个json值  
column->>path json\_unquote(column -> path)的简洁写法  
json\_keys 提取json中的键值为json数组  
json\_search 按给定字符串关键字搜索json，返回匹配的路径  
修改json  
json\_array\_append 末尾添加数组元素，如果原有值是数值或json对 象，则转成数组后，再添加元素  
json\_array\_insert 插入数组元素  
json\_insert 插入值（插入新值，但不替换已经存在的旧值）  
json\_merge 合并json数组或对象  
json\_remove 删除json数据  
json\_replace 替换值（只替换已经存在的旧值）  
json\_set 设置值（替换旧值，并插入不存在的新值）  
json\_unquote 去除json字符串的引号，将值转成string类型  
返回json属性  
json\_depth 返回json文档的最大深度  
json\_length 返回json文档的长度  
json\_type 返回json值得类型  
json\_valid 判断是否为合法json文档  
[参考][Link 1]

### 增 ###

就是插入 json 格式的字符串，可以是对象的形式，也可以是数组的形式

INSERT INTO `a_goods` (`type`, `attrs`) 
    VALUES ('["新品","热销",1,"0"]','{"name":"张三","age":20,"strage":"20","data":[1,"0"]}')

MySQL 也有专门的函数 `JSON_OBJECT`，`JSON_ARRAY` 生成 json 格式的数据,但是吧，使用的时候有注意点，尽量之间写进去吧

//JSON_OBJECT必须是偶数，嵌套时必须搭配JSON_ARRAY
     INSERT INTO `a_goods` (`attrs`, `type`) 
     VALUES (
     JSON_OBJECT("name","张三","age",20,"strage","20","data",JSON_ARRAY("新品","热销",1,"0")), 
     JSON_ARRAY("新品","热销",1,"0")
     )

### 查询 ###

一般对应字符串类型的 category->’$.name’ 中还包含着双引号，这其实并不是想要的结果，可以用 JSON\_UNQUOTE 函数将双引号去掉，从 MySQL 5.7.13 起也可以通过这个操作符 `* ->> *这个和 JSON_UNQUOTE 是等价的`

查询json的值,即键的值

SELECT attrs->'$.name' as name, JSON_UNQUOTE(attrs->'$.name'), attrs->>'$.name' FROM a_goods
    /* SELECT attrs->'$.name' as name, 查询json数据中的name的值并赋值给name JSON_UNQUOTE(attrs->'$.name'), attrs->>'$.name' FROM a_goods */

json中的where 语句

SELECT * FROM a_goods WHERE type = CAST('["新品","热销",1,"0"]' as JSON)
    //查询json是否在数据库的存在,其实就相当于 `where a=1` 只不过这个a得转换一下
    //CAST函数用于将某种数据类型的表达式显式转换为另一种数据类型

SELECT * FROM a_goods WHERE attrs->>'$.age' = '20' 
    //要特别注意的是，JSON 中的元素搜索是严格区分变量类型的，比如说整型和字符串是严格区分的,即 “20”和20

SELECT * FROM a_goods WHERE JSON_CONTAINS(attrs, '20', '$.age')
    SELECT * FROM a_goods WHERE JSON_CONTAINS(type, '"新品"')
    //用JSON_CONTAINS 函数，但和 *column->path *的形式有点相反的是，JSON_CONTAINS 第二个参数是不接受整数的，无论 json 元素是整型还是字符串，否则会出现错误

### 更新 ###

更新数组按以往的更新就行

UPDATE a_goods SET type = '["demo","0",1]'

但如果要更新 JSON 下的元素，MySQL 并不支持 \*column->path \*的形式，则可能要用到以下几个函数

`JSON_INSERT()` 插入新值，但不会覆盖已经存在的值

UPDATE a_goods SET category = JSON_INSERT(category, '$.name', 'lnmp', '$.url', 'www.lnmp.cn') WHERE id = 1;

`JSON_SET()` 插入新值，并覆盖已经存在的值

UPDATE a_goods SET category = JSON_SET(category, '$.host', 'www.lnmp.cn', '$.url', 'http://www.lnmp.cn') WHERE id = 1;

`JSON_REPLACE()` 只替换存在的值

UPDATE a_goods SET category = JSON_REPLACE(category, '$.name', 'php', '$.url', 'http://www.php.net') WHERE i

`JSON_REMOVE()` 删除 JSON 元素

UPDATE a_goods SET category = JSON_REMOVE(category, '$.url', '$.host') WHERE id = 1;