【MySQL】之JSON数据类型与操作
⽬录
⼀、JSON 数据类型简介
在 MySQL5.7.8 版本之后，MySQL 新增了⼀个原⽣的 JSON 数据类型：
JSON 值将不再以字符串的形式存储，⽽是采⽤⼀种允许快速读取⽂本元素（document elements）的内部⼆进制（internal binary）格式；
在 JSON 列插⼊或者更新的时候将会⾃动验证 JSON ⽂本，未通过验证的⽂本将产⽣⼀个错误信息；
JSON ⽂本采⽤标准的创建⽅式，可以使⽤⼤多数的⽐较操作符进⾏⽐较操作，例如：=, <, <=, >, >=, <>, != 等。
⼆、JSON 数据操作
1、创建 JSON 字段
CREATE TABLE`demo`(
`id`INT(4)NOT NULL AUTO_INCREMENT,
`info` JSON NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4;
注意： JSON 类型可以为 null 或 not null 但不能有默认值。
2、插⼊ JSON 数据
1）插⼊JSON格式字符串
INSERT INTO`demo`(`info`)
json值的类型有哪些VALUES('{"name": "andy", "age": 18}'),('["andy", 18]');
插⼊的 JSON 字符串可以是 JSON对象 的形式，也可以是数组的形式。
2）使⽤JSON函数⽣成
INSERT INTO`demo`(`info`)
VALUES(JSON_OBJECT("name","andy","age",18)),(JSON_ARRAY(16,18));
3、查询 JSON 数据
1）标准查询
查询 JSON 中的数据⽤ column->path 的形式，如果数据为对象类型的话 path 的表⽰⽅式为 '$.path'，数组类型的表⽰⽅式为 '$[index]'：
SELECT`info`->'$.name'FROM`demo`;
SELECT`info`->'$[1]'FROM`demo`;
2）去除字符串类型的双引号
查询结果中的字符串类型会包含有双引号，可以使⽤ JSON_UNQUOTE 函数将双引号去掉，从 MySQL 5.7.13 开始也可以使⽤操作符 ->>：
SELECT JSON_UNQUOTE(`info`->'$.name')FROM`demo`;
SELECT`info`->>'$.name'FROM`demo`;
3）JSON作为条件查询
JSON 不同于普通字符串，如果直接将字符串和 JSON 字段进⾏⽐较，不会查询到结果：
-- 查询不到数据
SELECT*FROM`demo`WHERE`info`='{"age": 18, "name": "andy"}';
解决办法为使⽤ CAST 函数将字符串转成 JSON 的形式：
SELECT*FROM`demo`WHERE`info`= CAST('{"age": 18, "name": "andy"}'AS JSON);
4）JSON数据中的元素作为条件查询
指定JSON元素的⽅法为：column->'$.path' 或 column->'$[index]'
SELECT*FROM`demo`WHERE`info`->'$.name'='andy';
上⾯提到过 column->path 查询出来的字符串包含双引号，⽽ column->>path 不包含双引号，但作为条件查询时，-> 和 ->> 的查询结果都是⼀样包含双引号的。
注意： 需要注意的是，在 MySQL5.7.17 版本之后，JSON 中的元素搜索是严格区分变量类型的，⽐如整型和字符串是严格区分的（使⽤-> 的进⾏），但如果使⽤ ->> 的形式的话就不区分：
-- 能查到
SELECT*FROM`demo`WHERE`info`->'$.age'=18;
-- 不能查到
SELECT*FROM`demo`WHERE`info`->'$.age'='18';
-- 能查到
SELECT*FROM`demo`WHERE`info`->>'$.age'=18;
-- 能查到
SELECT*FROM`demo`WHERE`info`->>'$.age'='18';
除了使⽤ column->path 形式查询元素外，还可以使⽤ JSON_CONTAINS 函数来查询元素：
-- 第⼀参数为JSON字段，第⼆个为搜索的值，第三个为指定元素
SELECT*FROM`demo`WHERE JSON_CONTAINS(`info`,'18','$.age');
注意： JSON_CONTAINS 函数的第⼆个参数为要搜索的元素值，只能⽤字符串的形式给出，哪怕其值为整型类型，否则会报错。
对于数组类型的JSON查询，JSON_CONTAINS 函数的使⽤⽅式如下：
-- 匹配单个值
SELECT*FROM`demo`WHERE JSON_CONTAINS(`info`,'18');
-- 匹配多个值(完全匹配)
SELECT*FROM`demo`WHERE JSON_CONTAINS(`info`,'["andy",18]');
同样的，JSON_CONTAINS 函数的第⼆个参数必须使⽤字符串的形式给出。
4、更新 JSON 数据
1）更新整个JSON
和插⼊JSON数据时类似：
UPDATE`demo`SET`info`='{"name": "joe", "age": 15}'WHERE`id`=3;
2）JSON_INSERT函数
插⼊新的元素值，但不会覆盖已存在的元素值：
-- 只会新增sex元素，不会覆盖name元素
UPDATE`demo`SET`info`= JSON_INSERT(`info`,'$.name','bob','$.sex','man')WHERE`id`=3;
3）JSON_SET函数
设置值，如果元素不存在则创建，如果已存在则覆盖旧的值：
UPDATE`demo`SET`info`= JSON_SET(`info`,'$.name','bob','$.sex','man')WHERE`id`=1;
4）JSON_REPLACE函数
只会替换已存在的元素值：
UPDATE`demo`SET`info`= JSON_REPLACE(`info`,'$.name','andy')WHERE`id`=1;
5）JSON_REMOVE函数
删除JSON元素：
UPDATE`demo`SET`info`= JSON_REMOVE(`info`,'$.name','$.sex')WHERE`id`=3; UPDATE`demo`SET`info`= JSON_REMOVE(`info`,'$[1]')WHERE`id`=2;
6）JSON_ARRAY_INSERT函数
往数组中添加元素值：
UPDATE`demo`SET`info`= JSON_ARRAY_INSERT(`info`,'$[1]',0)WHERE`id`=2;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。