mysql json培训文档

水深无声 2023-03-01 14:59 16阅读 0赞

### mysql json培训文档 ###

*  前言
 *  一、创建表
 *  二、函数
 *   *  JSON\_ARRAY
     *  JSON\_ARRAY\_INSERT
     *  JSON\_ARRAY\_APEND
     *  JSON\_OBJECT
     *  JSON\_INSERT
     *  JSON\_SET
     *  JSON\_REPLACE
     *  JSON\_REMOVE
     *  JSON\_EXTRACT 和->
     *  JSON\_LENGTH
     *  JSON\_CONTAINS\_PATH
     *  JSON\_CONTAINS
     *  JSON\_UNQUOTE和->>
     *  JSON\_SEARCH
 *  三、数据演示
 *  四、官方文档下载

# 前言 #

mysql自5.7.8版本开始，就支持了json结构的数据存储和查询

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0VsbGVuX1Rhbmd4aWFuZw_size_16_color_FFFFFF_t_70]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0VsbGVuX1Rhbmd4aWFuZw_size_16_color_FFFFFF_t_70 1]  
mysql8新特性: [https://blog.csdn.net/xianjuke008/article/details/86507622?ops\_request\_misc=%257B%2522request%255Fid%2522%253A%2522159592295519725250116614%2522%252C%2522scm%2522%253A%252220140713.130102334…%2522%257D&request\_id=159592295519725250116614&biz\_id=0&utm\_medium=distribute.pc\_search\_result.none-task-blog-2allsobaiduend~default-1-86507622.first\_rank\_ecpm\_v3\_pc\_rank\_v4&utm\_term=mysql8%E6%96%B0%E7%89%B9%E6%80%A7&spm=1018.2118.3001.4187][https_blog.csdn.net_xianjuke008_article_details_86507622_ops_request_misc_257B_2522request_255Fid_2522_253A_2522159592295519725250116614_2522_252C_2522scm_2522_253A_252220140713.130102334_2522_257D_request_id_159592295519725250116614_biz_id_0_utm_medium_distribute.pc_search_result.none-task-blog-2allsobaiduend_default-1-86507622.first_rank_ecpm_v3_pc_rank_v4_utm_term_mysql8_E6_96_B0_E7_89_B9_E6_80_A7_spm_1018.2118.3001.4187]

# 一、创建表 #

create table testjson(
      `id` int(20) NOT NULL COMMENT '编号',
      `content` json COMMENT '内容',
      PRIMARY KEY (`id`) USING BTREE
    )

# 二、函数 #

<table> 
 <thead> 
  <tr> 
   <th>函数名称</th> 
   <th>描述</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>JSON_OBJECT</td> 
   <td>创建一个json对象</td> 
  </tr> 
  <tr> 
   <td>JSON_ARRAY</td> 
   <td>创建一个数组</td> 
  </tr> 
  <tr> 
   <td>JSON_INSERT</td> 
   <td>对json对象增加属性</td> 
  </tr> 
  <tr> 
   <td>JSON_ARRAY_INSERT</td> 
   <td>对json数组增加元素</td> 
  </tr> 
  <tr> 
   <td>JSON_ARRAY_APPEND</td> 
   <td>对json数组或者对象的值变为array</td> 
  </tr> 
  <tr> 
   <td>JSON_SET</td> 
   <td>如果不存在，增加，如果存在，替换现有值</td> 
  </tr> 
  <tr> 
   <td>JSON_REPLACE</td> 
   <td>替换现有值</td> 
  </tr> 
  <tr> 
   <td>JSON_REMOVE</td> 
   <td>删除指定json的属性</td> 
  </tr> 
  <tr> 
   <td>JSON_EXTRACT</td> 
   <td>返回json的指定值</td> 
  </tr> 
  <tr> 
   <td>-&gt;</td> 
   <td>等价于JSON_EXTRACT</td> 
  </tr> 
  <tr> 
   <td>JSON_LENGTH</td> 
   <td>返回json文档里面元素个数</td> 
  </tr> 
  <tr> 
   <td>JSON_CONTAINS_PATH</td> 
   <td>判断路径是否存在，0表示不存在，1表示存在</td> 
  </tr> 
  <tr> 
   <td>JSON_CONTAINS</td> 
   <td>判断文档内容是否存在，0表示不存在，1表示存在</td> 
  </tr> 
  <tr> 
   <td>JSON_UNQUOTE</td> 
   <td>去除json字符串的引号，将值转成string类型</td> 
  </tr> 
  <tr> 
   <td>-&gt;&gt;</td> 
   <td>等价于JSON_UNQUOTE json_unquote(column -&gt; path)的简洁写法</td> 
  </tr> 
  <tr> 
   <td>JSON_SEARCH</td> 
   <td>按给定字符串关键字搜索json，返回匹配的路径</td> 
  </tr> 
 </tbody> 
</table>

## JSON\_ARRAY ##

创建一个json数组

set @a=JSON_ARRAY(1,2,'x',3,'y','scx');

## JSON\_ARRAY\_INSERT ##

插入数组

set @a = JSON_ARRAY_INSERT(@a,'$[1]','a')
    -- select @a 结果[1,'a',2,'x',3,'y','scx']

## JSON\_ARRAY\_APEND ##

如果是数组添加到后面，如果不是数组，先转成数组再添加到后面

set @a = JSON_ARRAY_APEND(@a,'$[0]','z')
    -- select @a 结果[[1,'z'],'a',2,'x',3,'y','scx']
    set @c=JSON_OBJECT('id',1,'content','tx');
    set @c = JSON_ARRAY_APEND(@c,'$.content','z')
    -- select @c 结果{"id":1,"content":["tx","z"]}

## JSON\_OBJECT ##

创建一个json对象（奇数位置是key，偶数位置是value）

set @b=JSON_OBJECT('id',1,'name','tx','age',18);

## JSON\_INSERT ##

插入新的属性，不对旧属性做修改

set @b = JSON_INSERT(@b,'$.content','abc')
    -- select @b 结果 {"id":1, "name":"tx", "age":18 ,"content":"abc"}
    -- set @b = JSON_INSERT(@b,'$.id',2)无效

## JSON\_SET ##

替换原有值，增加新值

set @d = JSON_OBJECT('id',1,'name','tx')
    select JSON_SET(@d,'$.name','xx','$.age',22)
    -- 结果 {"id":1, "name":"xx", "age":22}

## JSON\_REPLACE ##

仅替换原有值

select JSON_REPLACE(@d,'$.name','tangxiang')
    -- 结果 {"id":1, "name":"tangxiang", "age":22}

## JSON\_REMOVE ##

移除属性

select JSON_REMOVE(@d,'$.age')
    -- 结果 {"id":1, "name":"tangxiang"}

## JSON\_EXTRACT 和-> ##

查询json的属性值

select JSON_EXTRACT(@d,'$.name');
    -- 结果"tx"
    select content->'$.name' from json_demo where id=1;
    -- 注意 -> 这种写法必须是列的情况

## JSON\_LENGTH ##

查询json文档里面元素个数

select JSON_LENGTH(@d)
    -- 结果2（id,name）

## JSON\_CONTAINS\_PATH ##

查询json路径，如果有返回1，如果没有返回0

select JSON_CONTAINS_PATH(content,'one','$.name','$.id') from json_demo where id=1;
    select JSON_CONTAINS_PATH(content,'one','$.nasdasme','$.id') from json_demo where id=1;
    select JSON_CONTAINS_PATH(content,'all','$.name','$.id') from json_demo where id=1;
    select JSON_CONTAINS_PATH(content,'all','$.nasdasme','$.id') from json_demo where id=1;

## JSON\_CONTAINS ##

查询包含值，存在返回1，没有返回0（精确匹配）

select JSON_CONTAINS(content,'1') from json_demo where id=1;
    select JSON_CONTAINS(content,'{"attr":"red"}') from json_demo where id=1;

## JSON\_UNQUOTE和->> ##

查询的json指定值，并去掉引号

-- 去掉引号
    SELECT JSON_UNQUOTE(content->"$.name") from json_demo where id=1;
    SELECT content->>'$.name' from json_demo where id=1;
    SELECT content->'$.name' from json_demo where id=1;
    -- 结果 tx tx "tx"

## JSON\_SEARCH ##

按给定字符串关键字搜索json，返回匹配的路径

-- 查询json路径
    update json_demo set content=JSON_INSERT(content, '$.color','red') where id=1;
    SELECT JSON_SEARCH(content, 'one', 'red') from json_demo;
    SELECT JSON_SEARCH(content, 'all', 'red') from json_demo;
    -- 结果 "$.attr" ["$.attr", "$.color"]

# 三、数据演示 #

TRUNCATE table json_demo;
    -- 创建json对象
    insert into json_demo(content) values(JSON_OBJECT('id',1,'name','tangxiang'));
    select * from json_demo;
    
    -- 创建json数组
    insert into json_demo(content) values(JSON_ARRAY(1,2,3,4));
    select * from json_demo;
    
    -- json数组新增元素，下标位置是1的地方插入x，其他元素往后移
    update json_demo set content=JSON_ARRAY_INSERT(content, '$[1]', 'x') where id=2;
    select * from json_demo;
    
    -- 在下标为2的位置上，将元素变为数组，将x添加到后面
    update json_demo set content=JSON_ARRAY_APPEND(content, '$[2]', 'x') where id=2;
    select * from json_demo;
    
    -- 将name属性由字符串变为数组，将x添加到后面
    update json_demo set content=JSON_ARRAY_APPEND(content, '$.name', 'x') where id=1;
    select * from json_demo;
    
    -- 修改name值，增加attrjson属性
    update json_demo set content=JSON_SET(content, '$.name', 'tx','$.attr','red') where id=1;
    select * from json_demo;
    
    -- 新增属性值，尝试更改name，JSON_INSERT不会修改旧的数据
    update json_demo set content=JSON_INSERT(content, '$.descri', 'this is desc', '$.needdel', 'del', 'name','zhangsan') where id=1;
    select * from json_demo;
    
    -- JSON_REPLACE修改属性值
    update json_demo set content=JSON_REPLACE(content, '$.needdel', 'no_del') where id=1;
    select * from json_demo;
    
    -- 移除needdel属性
    update json_demo set content=JSON_REMOVE(content, '$.needdel') where id=1;
    select * from json_demo;
    
    -- 查询json的name属性
    select JSON_EXTRACT(content,'$.name' ) from json_demo where id=1;
    -- 或者这种写法，简洁形式
    select content->'$.name' from json_demo where id=1;
    
    -- 查询数组的下标1号位
    select JSON_EXTRACT(content,'$[1]' ) from json_demo where id=2;
    
    -- jsonobject返回属性个数,数组返回数组个数
    select JSON_LENGTH(content) from json_demo;
    
    -- 判断路径是否存在
    select JSON_CONTAINS_PATH(content,'one','$.name','$.id') from json_demo where id=1;
    select JSON_CONTAINS_PATH(content,'one','$.nasdasme','$.id') from json_demo where id=1;
    select JSON_CONTAINS_PATH(content,'all','$.name','$.id') from json_demo where id=1;
    select JSON_CONTAINS_PATH(content,'all','$.nasdasme','$.id') from json_demo where id=1;
    select JSON_CONTAINS_PATH(content,'one','$[0]') from json_demo where id=2;
    
    -- 判断文档内容是否存在
    select JSON_CONTAINS(content,'1') from json_demo where id=1;
    select JSON_CONTAINS(content,'{"attr":"red"}') from json_demo where id=1;
    
    -- 去掉引号
    SELECT JSON_UNQUOTE(content->"$.name") from json_demo where id=1;
    SELECT content->>'$.name' from json_demo where id=1;
    SELECT content->'$.name' from json_demo where id=1;
    
    -- 查询json路径
    update json_demo set content=JSON_INSERT(content, '$.color','red') where id=1;
    SELECT JSON_SEARCH(content, 'one', 'red') from json_demo;
    SELECT JSON_SEARCH(content, 'all', 'red') from json_demo;

# 四、官方文档下载 #

mysql json文档下载：

[https://download.csdn.net/download/Ellen\_Tangxiang/12670018][https_download.csdn.net_download_Ellen_Tangxiang_12670018]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0VsbGVuX1Rhbmd4aWFuZw_size_16_color_FFFFFF_t_70]: /images/20230208/97969dd644b249d9b6d0d83158e1a0dd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0VsbGVuX1Rhbmd4aWFuZw_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200728160132514.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0VsbGVuX1Rhbmd4aWFuZw==,size_16,color_FFFFFF,t_70
[https_blog.csdn.net_xianjuke008_article_details_86507622_ops_request_misc_257B_2522request_255Fid_2522_253A_2522159592295519725250116614_2522_252C_2522scm_2522_253A_252220140713.130102334_2522_257D_request_id_159592295519725250116614_biz_id_0_utm_medium_distribute.pc_search_result.none-task-blog-2allsobaiduend_default-1-86507622.first_rank_ecpm_v3_pc_rank_v4_utm_term_mysql8_E6_96_B0_E7_89_B9_E6_80_A7_spm_1018.2118.3001.4187]: https://blog.csdn.net/xianjuke008/article/details/86507622?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522159592295519725250116614%2522%252C%2522scm%2522%253A%252220140713.130102334..%2522%257D&request_id=159592295519725250116614&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~all~sobaiduend~default-1-86507622.first_rank_ecpm_v3_pc_rank_v4&utm_term=mysql8%E6%96%B0%E7%89%B9%E6%80%A7&spm=1018.2118.3001.4187
[https_download.csdn.net_download_Ellen_Tangxiang_12670018]: https://download.csdn.net/download/Ellen_Tangxiang/12670018