MySQL 5.7 新特性JSON数据类型创建，查找，修改和索引简记

71 阅读 0 评论 47 点赞

我是靠谱客的博主缓慢水蜜桃，最近开发中收集的这篇文章主要介绍MySQL 5.7 新特性JSON数据类型创建，查找，修改和索引简记，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

在MySQL中，JSON数据类型在5.7.8的时候正式引进，再此之前保存json格式一般使用varchar或者text。

JSON数据类型特点：

JSON主要将字段的type设JSON，不能具有非NULL得默认值。
MySQL提供操作JSON数据的内置函数
优化存储格式。存储在JSON列中的JSON文档将转换为内部各式，从而可以快速读取文档元素。
修改特定的键值，VARCHAR类型的JSON字符串要修改需要整条数据更新，而JSON数据类型可以只修改需要修改的键值

创建JSON值

数组包含用逗号分隔并包含在[和]字符内的值，例如

["abc", 10, null, true, false]

对象包含一组键值对，以逗号分隔并包含在{和}字符内，例如

{"key1": "value", "key1": 10}

插入数据JSON格式的字符串，可以是对象的形式，也可以是数组的形式，也可以JSON数组元素和JSON对象键值对嵌套，例如

[1, {"id": "100", "price": 23.32}, ["hot", 1]]
{"store_name": "value", "slide": [10, 20]}

查询

查询 json 中的数据用 column->path 的形式，其中对象类型 path 这样表示 $.path, 而数组类型则是 $[index]

SELECT id, cart_info->'$.id', cart_info->'$.name' FROM cart;

cart_info->'$.name' 中还包含着双引号，可以用 JSON_UNQUOTE 函数将双引号去掉，从 MySQL 5.7.13 起也可以通过这个操作符 ->> 这个和 JSON_UNQUOTE 是等价的

JSON作为条件查询

select * from cart where name = '{"id": 1, "name": "product"}';#错误示范

这样是错误的，因为 JSON 不同于字符串，所以如果用字符串和 JSON 字段比较，是不会相等的，可以通过 CAST 将字符串转成 JSON 的形式

select * from cart where name = CAST('{"id": 1, "name": "product"}');#正确

JSON中的元素查询, 对象型的查询同样可以通过 column->path

SELECT * FROM cart where cart_info->'$.name' = '名称' ;

注意：JSON中的元素搜索是严格区分变量类型的，如整型、字符串

修改

更新JSON下的元素时，可能需要一些内置函数，下面列出一部分

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

 UPDATE product SET cart
= JSON_INSERT(cart , '$.name', 'product', '$.email', 'test@test.cn') WHERE id = 1;

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

JSON_REPLACE() 只替换存在的值

JSON_REMOVE() 删除 JSON 元素

索引

需要注意的是，MySQL并没有提供对JSON对象中的字段进行索引的功能，至少没有直接对其字段进行索引的方法。要创建间接引用此类列的索引，您可以定义一个生成的列，该列提取应构建索引的信息，然后在生成的列上创建索引。通俗说就是把要搜索的数据单独列出来一个数据列，然后在这个字段上建索引，或了解一下虚拟列。