mysql5.7之json数据类型

Json简述

JSON对象

简述:

         数据在键值对中,

         数据由逗号分隔,

         花括号保存对象,

         方括号保存数组.

 

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

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

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

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

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)的有序集合.一个数组以”[”开始,”]”结束,值中间使用”,”分割.

对象数组

表示方法:[{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;

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

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

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

查询语句

select * from json;

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

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

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

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

json条件查询:

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

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

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

 

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

json模糊条件查询:

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

json常用函数

JSON_TYPE

         显示当前JSON字符串的类型

JSON_ARRAY

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

JSON_OBJECT

         将对象转化为json格式数据

         Json_object(key,value,key1,value1);

JSON_MERGE

         作用:合并JSON字符串

JSON_VALID

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

JSON_CONTAINS

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

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

JSON_SEARCH

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

         Tags:要查找的文档

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

         Data%:查找的条件;

JSON PATH

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

JSON_EXTRACT

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

 

JSON_MERGE

         更新数据

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