MySQL之JSON类型字段怎么应用

发布时间：2023-06-28 11:30:43 所属栏目：MySql教程来源：未知

导读： 　　给大家分享一下MySQL之JSON类型字段怎么使用的相关知识点，内容详细，逻辑清晰，相信大部分人都还太了解这方面的知识，所以分享这篇文章给大家参考一下，希望大家阅读完这篇文章后有所

　　给大家分享一下MySQL之JSON类型字段怎么使用的相关知识点，内容详细，逻辑清晰，相信大部分人都还太了解这方面的知识，所以分享这篇文章给大家参考一下，希望大家阅读完这篇文章后有所收获，下面我们一起来了解一下吧。

　　测试环境： MySQL8.0.19

　　准备工作

　　CREATE TABLE json_demo (

　　 `id` INT ( 11 ) NOT NULL PRIMARY KEY,

　　 `content` json NOT NULL

　　);

　　INSERT INTO json_demo ( id, content )

　　VALUES

　　 /*这条是数组*/

　　 ( 1, '[{"key": 1, "order": 1, "value": "34252"},{"key": 2, "order": 2, "value": "23423"}]' ),

　　 /*这条是数组*/

　　 ( 2, '[{"key": 4, "order": 4, "value": "234"},{"key": 5, "order": 5, "value": "234324523"}]' ),

　　 /*这条是对象*/

　　 ( 3, '{"key": 3, "order": 3, "value": "43242"}' ),

　　 /*这条是对象*/

　　 ( 4, '{"key": 6, "order": 6, "value": "5423"}' );

　　JSON对象基础操作

　　查询指定字段值

　　/* 基础查询 */

　　SELECT

　　 content -> '$.key' AS 'key',

　　 JSON_EXTRACT(content, '$.key') AS 'key2',

　　 content -> '$.value' AS 'value',

　　 JSON_EXTRACT(content, '$.value') AS 'value2',

　　 content ->> '$.value' AS 'value3',

　　 JSON_UNQUOTE(JSON_EXTRACT(content, '$.value')) AS 'value4'

　　FROM

　　 json_demo

　　WHERE

　　 id > 2;

　　TIPS：

　　->和->>是MySQL设计的语法，其中->在MySQL5.7支持，->>在MySQL8.0中支持。

　　->等效于JSON_EXTRACT()，当查询字段为字符串时，其返回值还会带有""。

　　->>等效于JSON_UNQUOTE(JSON_EXTRACT())，当查询字段为字符串时，其返回值不会带有""。

　　用于条件查询

　　content -> '$.key'可以看成一个字段，一个字段能做的操作基本他都能。

　　SELECT

　　 id,

　　 content -> '$.key' AS 'key',

　　 content ->> '$.value' AS 'value3'

　　FROM

　　 json_demo

　　WHERE

　　 id > 2

　　 AND content -> '$.key' > 1

　　 AND content -> '$.value' like '%2%';

　　修改指定字段值

　　/* 修改 */

　　UPDATE json_demo

　　SET content = JSON_REPLACE(

　　 content,

　　 /* 将content.key值 + 1 */

　　 '$.key', content -> '$.key' + 1,

　　 /* 将content.value值后拼接'abc' */

　　 '$.value', concat(content ->> '$.value', 'abc')

　　) WHERE id = 3;

　　/* JSON_SET也可以 */

　　UPDATE json_demo

　　SET content = JSON_SET(

　　 content,

　　 /* 将content.key值 + 1 */

　　 '$.key', content -> '$.key' + 1,

　　 /* 将content.value值后拼接'abc' */

　　 '$.value', concat(content ->> '$.value', 'abc')

　　) WHERE id = 3;

　　/* 查询修改结果 */

　　SELECT id,content,content -> '$.key' AS 'key',content ->> '$.value' AS 'value3'

　　FROM json_demo WHERE id = 3;

　　/* 重新赋值 */

　　UPDATE json_demo SET

　　content = JSON_REPLACE(content,'$.key',3,'$.value','43242') WHERE id = 3;

　　TIPS：

　　JSON_REPLACE和JSON_SET都可以用来修改某个字段值，区别在于JSON_REPLACE替换不存在的属性时操作无效；而JSON_SET则会将这个不存在的属性插入进去。

　　所以JSON_SET也可以用来追加属性，与JSON_INSERT类似。区别在于JSON_INSERT如果插入一个已存在的属性时操作会失效，而JSON_SET会替换。

　　追加元素

　　UPDATE json_demo

　　SET content = JSON_INSERT(content, '$.key', 234)

　　WHERE id = 3;

　　SELECT id,content,content -> '$.key' AS 'key' FROM json_demo WHERE id = 3;

　　UPDATE json_demo

　　SET content = JSON_INSERT(content, '$.temp', 234)

　　WHERE id = 3;

　　SELECT id,content,content -> '$.key' AS 'key' FROM json_demo WHERE id = 3;

　　UPDATE json_demo

　　SET content = JSON_SET(content, '$.temp2', 432)

　　WHERE id = 3;

　　SELECT id,content,content -> '$.key' AS 'key' FROM json_demo WHERE id = 3;

　　JSON数组操作

　　查询指定字段值

　　SELECT

　　 id,

　　 content -> '$[*].key' AS 'key',

　　 content ->> '$[*].value' AS 'value',

　　 content -> '$[0].key' AS 'key2',

　　 content ->> '$[0].value' AS 'value2',

　　 /* 查询数组长度 */

　　 JSON_LENGTH(content) AS 'length'

　　FROM

　　 json_demo

　　WHERE

　　 id < 3;

　　用于条件查询

　　SELECT

　　 id,

　　 content -> '$[*].key' AS 'key',

　　 content ->> '$[*].value' AS 'value'

　　FROM

　　 json_demo

　　WHERE

　　 id < 3

　　 /* content.value的值中存在like'%34%'的值 */

　　 AND content ->> '$[*].value' like '%34%'

　　 /* content.key的值中有4 */

　　 AND JSON_OVERLAPS(content ->> '$[*].key', '4' );

　　修改指定字段值

　　基础操作都跟JSON对象差不太多，就是在'$'后面加对应的索引位'$[0]'，指定所有则'$[*]'。如果数组中包含数组，可以通过'$[1][2][3]'这种方式指定深层的数组元素。

　　追加元素

　　JSON_ARRAY_APPEND和JSON_ARRAY_INSERT都可以实现数组元素追加。区别在于JSON_ARRAY_APPEND可以不指定索引位，此时往最后位置追加；JSON_ARRAY_INSERT必须指定索引位，不指定则会报错。

　　JSON_ARRAY_APPEND是追加在指定索引位后面，而JSON_ARRAY_INSERT则是插入到指定索引位前面。

　　更多操作

　　名称描述

　　JSON_ARRAY() 创建JSON数组

　　JSON_ARRAY_APPEND() 将数据附加到JSON文档

　　JSON_ARRAY_INSERT() 插入JSON数组

　　JSON_CONTAINS() JSON文档是否在路径中包含特定对象

　　JSON_CONTAINS_PATH() JSON文档是否在路径中包含任何数据

　　JSON_DEPTH() JSON文档的最大深度

　　JSON_EXTRACT() 从JSON文档返回数据

　　JSON_INSERT() 将数据插入JSON文档

　　JSON_KEYS() JSON文档中的键数组

　　JSON_LENGTH() JSON文档中的元素数

　　JSON_MERGE() （已弃用）合并JSON文档，保留重复的键。JSON_MERGE_PRESERVE（）的已弃用同义词

　　JSON_MERGE_PATCH() 合并JSON文档，替换重复键的值

　　JSON_MERGE_PRESERVE() 合并JSON文档，保留重复的键

　　JSON_OBJECT() 创建JSON对象

　　JSON_OVERLAPS() （8.0.17引入）比较两个JSON文档，如果它们具有共同的任何键值对或数组元素，则返回TRUE（1），否则返回FALSE（0）

　　JSON_PRETTY() 以易于阅读的格式打印JSON文档

　　JSON_QUOTE() 引用JSON文档

　　JSON_REMOVE() 从JSON文档中删除数据

　　JSON_REPLACE() 替换JSON文档中的值

　　JSON_SCHEMA_VALID() （8.0.17引入）根据JSON模式验证JSON文档；如果文档针对架构进行了验证，则返回TRUE / 1；否则，则返回FALSE / 0。

　　JSON_SCHEMA_VALIDATION_REPORT() （8.0.17引入）根据JSON模式验证JSON文档；以JSON格式返回有关验证结果的报告，包括成功或失败以及失败原因

　　JSON_SEARCH() JSON文档中值的路径

　　JSON_SET() 将数据插入JSON文档

　　JSON_STORAGE_FREE() 部分更新后，JSON列值的二进制表示形式中的可用空间

　　JSON_STORAGE_SIZE() 用于存储JSON文档的二进制表示形式的空间

　　JSON_TABLE() 从JSON表达式返回数据作为关系表

　　JSON_TYPE() JSON值类型

　　JSON_UNQUOTE() 取消引用JSON值

　　JSON_VALID() JSON值是否有效

　　JSON_VALUE() （8.0.21引入）在提供的路径所指向的位置从JSON文档中提取值；以VARCHAR（512）或指定的类型返回此值

　　MEMBER OF() （8.0.17引入）如果第一个操作数与作为第二个操作数传递的JSON数组的任何元素匹配，则返回true（1），否则返回false（0）

（编辑：吕梁站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!