MySQL 5.7的原生JSON数据类型使用

        新增测试用表

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

        新增数据

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));

        分别是两种不同的方式新增.


        查询:    

        显示json格式内部字段

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

        去除掉默认双引号

SELECT id, category->'$.name', JSON_UNQUOTE(category->'$.name'), category->>'$.name' FROM lnmp;

        条件查询

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

        必须使用cast转换为json类型,如果不转换就相当于查询String,是查询不到数据的。

        

        使用json内属性进行条件查询

SELECT * FROM lnmp WHERE category->'$.name' = 'lnmp.cn';

SELECT * FROM lnmp WHERE category->>'$.name' = 'lnmp.cn';

        两种皆可。

        

        要特别注意的是,JSON 中的元素搜索是严格区分变量类型的,比如说整型和字符串是严格区分的

SELECT * FROM lnmp WHERE category->'$.id' = 1;

        

        除了用 column->path 的形式搜索,还可以用JSON_CONTAINS 函数,但和 column->path 的形式有点相反的是,JSON_CONTAINS 第二个参数是不接受整数的,无论 json 元素是整型还是字符串,否则会出现这个错误

SELECT * FROM lnmp WHERE JSON_CONTAINS(category, '1', '$.id');

        

        对于数组类型的 JSON 的查询,比如说 tags 中包含有 2 的数据,同样要用 JSON_CONTAINS 函数,同样第二个参数也需要是字符串

SELECT * FROM lnmp WHERE JSON_CONTAINS(tags, '2');


        更新JSON:

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

        但如果要更新 JSON 下的元素,MySQL 并不支持 column->path 的形式 

        则可能要用到以下几个函数

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

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

        可以看到 name 没有被修改,但新元素 url 已经添加进去

        

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

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

        可以看到 host 已经插入,url 已经被修改

        

        JSON_REPLACE() 只替换存在的值

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

        可以看到 name 已经被替换,url 不存在被忽略。

        

        JSON_REMOVE() 删除 JSON 元素

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



        官方文档:https://dev.mysql.com/doc/refman/8.0/en/json-function-reference.html 


        转:https://javaforall.cn/111614.html  

冷暖自知一抹茶ck
请先登录后发表评论
  • 最新评论
  • 总共0条评论