mysql5.7之json数据类型

深碍√TFBOYSˉ_ 2022-05-31 11:45 405阅读 0赞

# Json简述 #

JSON对象

简述:

数据在键值对中,

数据由逗号分隔,

花括号保存对象,

方括号保存数组.

表示方法:\{key1:value1,key2:value2,………\}

{"UserID":11, "Name":"Truly", "Email":"zhuleipro◎hotmail.com"};

对象是一个无序的”’名称/值’对”集合.一个对象以”\{”开始,”\}”结束.

每个”名称”后跟一个”:”;”’名称/值’对”之间使用”,”分割.

![70][]

JSON数组:

表示方法:\[value1,value2,value3…..\]

用户对象:

\[   
\{"UserID":11,"Name":\{"FirstName":"Truly","LastName":"Zhu"\},

"Email":"zhuleipro◎hotmail.com"\},   
\{"UserID":12,"Name":\{"FirstName":"Jeffrey","LastName":"Richter"\},"Email":"xxx◎xxx.com"\},   
\{"UserID":13,"Name":\{"FirstName":"Scott","LastName":"Gu"\},"Email":"xxx2◎xxx2.com"\}   
\]

数组是值(value)的有序集合.一个数组以”\[”开始,”\]”结束,值中间使用”,”分割.

![70 1][]

对象数组

表示方法:\[\{key1:value1,key2:value2\},\{key3:value3,key4:value4\},…….\]

即对象的数组.数组中放的是对象而已.

Json中字符串表示方法:

字符串(string)是由双引号包围的任意数量Unicode字符的集合,使用反斜线转义.一个字符(character)即一个单独的字符串(character string);

# Mysql5.7 #

Mysql5.7+最新支持json类型数据存储,不能有默认值;

简易json表设计,字段content作为json 类型的数据;

建表语句:

CREATE TABLE \`json\` (

\`id\` int(11) DEFAULT NULL,

\`content\` json DEFAULT NULL

)

CREATE TABLE lnmp (  
    \`id\` int(10) unsigned NOT NULL AUTO\_INCREMENT,  
    \`category\` JSON,  
    \`tags\` JSON,  
    PRIMARY KEY (\`id\`)  
);

## 插入语句 ##

insert into jsonvalues(3,'\{"name":"测试"\}');

INSERT INTO \`lnmp\` (category, tags) VALUES('\{"id": 1, "name": "lnmp.cn"\}', '\[1, 2, 3\]');

INSERT INTO \`lnmp\` (category, tags) VALUES(JSON\_OBJECT("id", 2, "name", "php.net"),JSON\_ARRAY(1, 3, 5));

## 更新语句 ##

UPDATE lnmp SET tags = '\[1, 3, 4\]' WHERE id= 1;

跟新语句的四种不同状态:

JSON\_INSERT():插入新值,不会覆盖已经存在的值

JSON\_SET()       :插入新值,并覆盖已经存在的值

JSON\_REPLACE();只替换存在的值;

JSON\_REMOVE();删除JSON元素,已经存在就删除,没有不删除,不报错;

UPDATE lnmp SET category = JSON\_INSERT(category, '$.name', 'lnmp','$.url', 'www.lnmp.cn') WHERE id = 1;

![70 2][]

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

![70 3][]

UPDATE lnmp SET category =JSON\_REPLACE(category, '$.name', 'php', '$.url', 'http://www.php.net') WHERE id= 1;

![70 4][]

UPDATE lnmp SET category = JSON\_REMOVE(category, '$.url','$.host') WHERE id = 1;

![70 5][]

## 查询语句 ##

select \* from json;

![70 6][]

json中的数据使用column ->path的形式,其中对象类型path就是$.path,而数组类型则是$\[index\]

SELECT id, category->'$.id', category->'$.name', tags->'$[0]', tags->'$[2]' FROM lnmp;

![70 7][]

使用$.name时,category ->’$.name’获取到的String类型的数据是带有双引号的,使用JSON_UNQUOTE函数将双引号去掉,从mysql5.7.13中JSON_UNQUOTE等价于 ->>;

select id ,category->'$.name',JSON_UNQUOTE(category->'$.name'),category->>'$.name' from lnmp;

![70 8][]

### json条件查询: ###

select \* from json wherecontent->'$.title' = '标题';

![70 9][]

json作为条件进行搜索时,因为json不同于字符串,所以用字符串和json字段比较,是不会相等的.

![70 10][]

此时需要通过CAST将字符串转成JSON的形式.

SELECT * FROM lnmp WHERE category = CAST('{"id": 1, "name": "lnmp.cn"}' as JSON);

![70 11][]

json模糊条件查询:

select \* from json wherecontent->'$.title' like '%新%';

## json常用函数 ##

### JSON\_TYPE ###

显示当前JSON字符串的类型

![70 12][]

![70 13][]

### JSON\_ARRAY ###

将数组对象转换为json数组.

![70 14][]

### JSON\_OBJECT ###

将对象转化为json格式数据

Json\_object(key,value,key1,value1);

![70 15][]

### JSON\_MERGE ###

作用:合并JSON字符串

![70 16][]

### JSON\_VALID ###

判断JSON格式是否有效(null表示参数为null)

![70 17][]

### JSON\_CONTAINS ###

查询带有特定字段对应特定值的对象

![70 18][]

注意:使用JSON\_CONTAINS函数,JSON\_CONTAINS第二个参数是不接受整数的,无论json元素是整数还是字符串,都会出现错误. 必须使用字符串.

![70 19][]

![70 20][]

### JSON\_SEARCH ###

查询json对象中以Data开头的第一个符合条件的文章

Tags:要查找的文档

One:查找的范围.两个选项,one或者all.one是第一个符合条件的.”all”是所有符合条件的

Data%:查找的条件;

![70 21][]

### JSON PATH ###

是用来定位文档中的目标字段.

![70 22][]

### JSON\_EXTRACT ###

是JSON提取函数,$.name就是一个JSONpath,表示定位文档的name字段.

JSON\_MERGE

更新数据

如:给文章添加一个”dev”tag,更新的条件是已经包含”Mysql”标签,并且还没有”dev”标签的数据;

![70 23][]

[70]: /images/20220531/7052ec23e0f7415699a5b063b83b2c62.png
[70 1]: /images/20220531/438cf1da7e3a46cc9423cfcd1a446828.png
[70 2]: /images/20220531/d0476e89a5fc44a09a1ce9e56f200500.png
[70 3]: /images/20220531/d58e343a30e745389a77fde8fb6a63cc.png
[70 4]: /images/20220531/e7e44797e0754458b4133209c1b01755.png
[70 5]: /images/20220531/a4707418b4934b75b2b073b0e2556b3c.png
[70 6]: /images/20220531/180b335e6cf14ff695cc217756da2c09.png
[70 7]: /images/20220531/2d60c53e526a4431b00b23a53370f79d.png
[70 8]: /images/20220531/7a56f16486874ed899ffa579f58ad9c3.png
[70 9]: /images/20220531/a188d8321b594200a5b0698a95a69412.png
[70 10]: /images/20220531/e4f40812cdf44c31bfa2cfa5eaaa9bdc.png
[70 11]: /images/20220531/7d767be8e98e4f92ad28a27932647c9e.png
[70 12]: /images/20220531/82ecbd1fbf5346ca81c03391c6480f7a.png
[70 13]: /images/20220531/4ee2fa216cbb4172bc119c1695f5cbb2.png
[70 14]: /images/20220531/5ea79e054a3b48d4b6f7ffd022d56d2e.png
[70 15]: /images/20220531/b4d68a4db0cd45b8aae2806ecd616721.png
[70 16]: /images/20220531/43e54accb21c4d938eabeedb87c30a0d.png
[70 17]: /images/20220531/64b79cae25a44ccd8b055ffc52da036c.png
[70 18]: /images/20220531/a2fb1037729546cf94c67593f43287db.png
[70 19]: /images/20220531/dcfed869f01c4080aed12cc71233bd40.png
[70 20]: /images/20220531/90913e76b4da4654a4a3f36f0598c29a.png
[70 21]: /images/20220531/a14f9da4252e4e2e9ce4ac0834a97c92.png
[70 22]: /images/20220531/7c13e42ed2e645d58d5b63326b1b56c0.png
[70 23]: /images/20220531/1b9382d4155641959d1bf409555be682.png