mysql中使用json set_mysql支持原生json使用说明

最新推荐文章于 2024-06-04 11:15:00 发布
最新推荐文章于 2024-06-04 11:15:00 发布
阅读量4.4k 收藏 2
点赞数
文章标签： mysql中使用json set
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42581003/article/details/113601236
版权

正文

MySQL在5.7.8开始对json原生支持，本文将对MySQL中json类型的用法简单说明，希望对你有用。

CREATE TABLE testproject (

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`skill` JSON NOT NULL,

`student` JSON NOT NULL,

PRIMARY KEY (`id`)

);

查看表结构：

AAffA0nNPuCLAAAAAElFTkSuQmCC

这样JSON的字段就被创建好了

**注：**JSON类型不能有默认值。

插入JSON

插入 json 格式的字符串，可以是对象的形式，也可以是数组的形式，

INSERT INTO `testproject` (student, skill) VALUES ('{"id": 1, "name": "ggjg"}', '["java", "go", "vue"]');

INSERT INTO `testproject` (student, skill) VALUES ('{"id": 5, "name": "guogege"}', '[]');

插入json时，数据库会对json做校验，不符合json规范就会报错。

AAffA0nNPuCLAAAAAElFTkSuQmCC

查询JSON：

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

查询testproject表student字段中json对象id为1的记录：

SELECT * FROM testproject WHERE student->'$.id'= 1;

AAffA0nNPuCLAAAAAElFTkSuQmCC

查询testproject表student字段中json对象id为1或者5的记录：

SELECT * FROM testproject WHERE student->'$.id' in (1,5);

SELECT * FROM testproject WHERE student->'$.id' = 1 or student->'$.id' = 5;

AAffA0nNPuCLAAAAAElFTkSuQmCC

也可以用函数json_extract：

AAffA0nNPuCLAAAAAElFTkSuQmCC

column->path方法有限制，数据源必须是表字段，否则就报错：

AAffA0nNPuCLAAAAAElFTkSuQmCC

以下这样查询，查出来student->’$.name’包含双引号：

SELECT id, student->'$.id', student->'$.name', skill->'$[0]', skill->'$[2]' FROM testproject;

AAffA0nNPuCLAAAAAElFTkSuQmCC

这不是我们想要的，可以用 JSON_UNQUOTE 函数将双引号去掉，从 MySQL 5.7.13 起也可以通过这个操作符 ->> 这个和 JSON_UNQUOTE 是等价的。

AAffA0nNPuCLAAAAAElFTkSuQmCC

因为 JSON 不同于字符串，所以如果用字符串和 JSON 字段比较，是不会相等的：

mysql> SELECT * FROM testproject WHERE student = '{"id": 1, "name": "ggjg"}';

Empty set (0.00 sec)

此时可以通过 CAST 将字符串转成 JSON 的形式：

mysql> SELECT * FROM testproject WHERE student = CAST('{"id": 1, "name": "ggjg"}' as JSON);

+----+-----------------------+---------------------------+

| id | skill | student |

+----+-----------------------+---------------------------+

| 10 | ["java", "go", "vue"] | {"id": 1, "name": "ggjg"} |

+----+-----------------------+---------------------------+

1 row in set (0.01 sec)

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

mysql> SELECT * FROM testproject WHERE student->'$.id' = '1';

Empty set (0.00 sec)

mysql>

mysql> SELECT * FROM testproject WHERE student->'$.id' = 1;

+----+-----------------------+---------------------------+

| id | skill | student |

+----+-----------------------+---------------------------+

| 10 | ["java", "go", "vue"] | {"id": 1, "name": "ggjg"} |

+----+-----------------------+---------------------------+

1 row in set (0.00 sec)

可以看到搜索字符串 1 和整型 1 的结果是不一样的。

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

mysql> SELECT * FROM testproject WHERE JSON_CONTAINS(student, 1, '$.id');

ERROR 3146 (22032): Invalid data type for JSON data in argument 2 to function json_contains; a JSON string or JSON type is required.

mysql>

这里必须要使用字符串：

mysql> SELECT * FROM testproject WHERE JSON_CONTAINS(student, '1', '$.id');

+----+-----------------------+---------------------------+

| id | skill | student |

+----+-----------------------+---------------------------+

| 10 | ["java", "go", "vue"] | {"id": 1, "name": "ggjg"} |

+----+-----------------------+---------------------------+

1 row in set (0.00 sec)

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

mysql> SELECT * FROM testproject WHERE JSON_CONTAINS(skill, '"go"');

+----+-----------------------+---------------------------+

| id | skill | student |

+----+-----------------------+---------------------------+

| 10 | ["java", "go", "vue"] | {"id": 1, "name": "ggjg"} |

+----+-----------------------+---------------------------+

1 row in set (0.00 sec)

mysql> SELECT * FROM testproject WHERE JSON_CONTAINS(skill, '1');

+----+-----------+------------------------------+

| id | skill | student |

+----+-----------+------------------------------+

| 12 | [1, 2, 3] | {"id": 4, "name": "guogege"} |

+----+-----------+------------------------------+

1 row in set (0.00 sec)

更新数据

MySQL 并不支持 column->path 的形式进行更新操作。

如果是整个 json 更新的话，和插入时类似的：

mysql> select * from testproject where id = 10;

+----+-----------------------+---------------------------+

| id | skill | student |

+----+-----------------------+---------------------------+

| 10 | ["java", "go", "vue"] | {"id": 1, "name": "ggjg"} |

+----+-----------------------+---------------------------+

1 row in set (0.00 sec)

mysql> UPDATE testproject SET skill = '["js", "java"]' WHERE id = 10;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from testproject where id = 10;

+----+----------------+---------------------------+

| id | skill | student |

+----+----------------+---------------------------+

| 10 | ["js", "java"] | {"id": 1, "name": "ggjg"} |

+----+----------------+---------------------------+

1 row in set (0.00 sec)

json_array_append和json_array_insert函数使用：

json_array_append是在json后面追加；

json_array_insert是在指定下标插入。

mysql> select * from testproject;

+----+----------------+------------------------------+

| id | skill | student |

+----+----------------+------------------------------+

| 10 | ["js", "java"] | {"id": 1, "name": "ggjg"} |

| 11 | [] | {"id": 5, "name": "guogege"} |

| 12 | [1, 2, 3] | {"id": 4, "name": "guogege"} |

+----+----------------+------------------------------+

3 rows in set (0.00 sec)

mysql> SELECT json_array_append(skill, '$', 'c') from testproject;

+------------------------------------+

| json_array_append(skill, '$', 'c') |

+------------------------------------+

| ["js", "java", "c"] |

| ["c"] |

| [1, 2, 3, "c"] |

+------------------------------------+

3 rows in set (0.00 sec)

mysql> SELECT json_array_insert(skill, '$[1]', 'php') from testproject;

+-----------------------------------------+

| json_array_insert(skill, '$[1]', 'php') |

+-----------------------------------------+

| ["js", "php", "java"] |

| ["php"] |

| [1, "php", 2, 3] |

+-----------------------------------------+

3 rows in set (0.00 sec)

mysql>

json_replace、json_set、json_insert和json_remove函数用法：

json_replace：只替换已经存在的旧值，不存在则忽略；

json_set：替换旧值，并插入不存在的新值；

json_insert：插入新值，但不替换已经存在的旧值；

json_remove() 删除元素。

json_replace：

mysql> select * from testproject;

+----+----------------+--------------------------------+

| id | skill | student |

+----+----------------+--------------------------------+

| 10 | ["js", "java"] | {"id": 1, "name": "smallsoup"} |

| 11 | [] | {"id": 5, "name": "guogege"} |

| 12 | [1, 2, 3] | {"id": 4, "name": "guogege"} |

+----+----------------+--------------------------------+

3 rows in set (0.00 sec)

mysql>

mysql> UPDATE testproject SET student->'$.name' = 'smallsoup' where student->'$.id' = 1;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server versio

n for the right syntax to use near '->'$.name' = 'smallsoup' where student->'$.id' = 1' at line 1

mysql>

mysql> UPDATE testproject SET student = json_replace(student, '$.name', 'soup') WHERE student->'$.id' = 1;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from testproject;

+----+----------------+------------------------------+

| id | skill | student |

+----+----------------+------------------------------+

| 10 | ["js", "java"] | {"id": 1, "name": "soup"} |

| 11 | [] | {"id": 5, "name": "guogege"} |

| 12 | [1, 2, 3] | {"id": 4, "name": "guogege"} |

+----+----------------+------------------------------+

3 rows in set (0.00 sec)

json_set：

mysql> select * from testproject;

+----+----------------+------------------------------+

| id | skill | student |

+----+----------------+------------------------------+

| 10 | ["js", "java"] | {"id": 1, "name": "soup"} |

| 11 | [] | {"id": 5, "name": "guogege"} |

| 12 | [1, 2, 3] | {"id": 4, "name": "guogege"} |

+----+----------------+------------------------------+

3 rows in set (0.00 sec)

mysql> UPDATE testproject SET student = json_set(student, '$.name', 'small', '$.age', 22) WHERE student->'$.id'= 1;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from testproject;

+----+----------------+---------------------------------------+

| id | skill | student |

+----+----------------+---------------------------------------+

| 10 | ["js", "java"] | {"id": 1, "age": 22, "name": "small"} |

| 11 | [] | {"id": 5, "name": "guogege"} |

| 12 | [1, 2, 3] | {"id": 4, "name": "guogege"} |

+----+----------------+---------------------------------------+

3 rows in set (0.00 sec)

json_insert：

mysql> select * from testproject;

+----+----------------+---------------------------------------+

| id | skill | student |

+----+----------------+---------------------------------------+

| 10 | ["js", "java"] | {"id": 1, "age": 22, "name": "small"} |

| 11 | [] | {"id": 5, "name": "guogege"} |

| 12 | [1, 2, 3] | {"id": 4, "name": "guogege"} |

+----+----------------+---------------------------------------+

3 rows in set (0.00 sec)

mysql> UPDATE testproject SET student = json_insert(student, '$.name', 'soup', '$.addr', '苏州') WHERE student->'$.id'=

1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from testproject;

+----+----------------+---------------------------------------------------------+

| id | skill | student |

+----+----------------+---------------------------------------------------------+

| 10 | ["js", "java"] | {"id": 1, "age": 22, "addr": "苏州", "name": "small"} |

| 11 | [] | {"id": 5, "name": "guogege"} |

| 12 | [1, 2, 3] | {"id": 4, "name": "guogege"} |

+----+----------------+---------------------------------------------------------+

3 rows in set (0.00 sec)

json_remove() :

mysql> select * from testproject;

+----+----------------+---------------------------------------------------------+

| id | skill | student |

+----+----------------+---------------------------------------------------------+

| 10 | ["js", "java"] | {"id": 1, "age": 22, "addr": "苏州", "name": "small"} |

| 11 | [] | {"id": 5, "name": "guogege"} |

| 12 | [1, 2, 3] | {"id": 4, "name": "guogege"} |

+----+----------------+---------------------------------------------------------+

3 rows in set (0.00 sec)

mysql> UPDATE testproject SET student = json_remove(student, '$.name', '$.age') WHERE student->'$.id' = 1;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from testproject;

+----+----------------+------------------------------+

| id | skill | student |

+----+----------------+------------------------------+

| 10 | ["js", "java"] | {"id": 1, "addr": "苏州"} |

| 11 | [] | {"id": 5, "name": "guogege"} |

| 12 | [1, 2, 3] | {"id": 4, "name": "guogege"} |

+----+----------------+------------------------------+

3 rows in set (0.00 sec)

可以看到name和age就被移除了。

以上只列出了部分函数的说明，mysql官方提供的函数列表如下：

AAffA0nNPuCLAAAAAElFTkSuQmCC

更多用法请查看官方文档：

薄辉
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql5.0使用json_MySQL 5.7的原生JSON数据类型使用
weixin_39839018的博客
02-02 126
新增测试用表：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"}', ...
mysql json invalid json text_MySQL 中 JSON 类型应用
weixin_39632728的博客
01-27 8234
MySQL 从 5.7.8 开始，支持原生的 JSON 数据类型。可以高效的访问 JSON 文档中的数据。与在字符串列中存储 JSON 格式字符串相比，JSON 数据类型有以下优势：可自动验证存储的 JSON 数据格式是否正确。优化的存储格式。存储在 JSON 列中的 JSON 文档将转化为内部格式，以允许对文档元素进行快速访问。当服务器读取以二进制存储的 JSON 值时，不需要再对文本解析后取值...
mysql json remove_mysql支持原生json使用说明
weixin_32306175的博客
02-07 606
MySQL在5.7.8开始对json原生支持，本文将对MySQL中json类型的用法简单说明，希望对你有用。CREATETABLEtestproject(`id`int(10)unsignedNOTNULLAUTO_INCREMENT,`skill`JSONNOTNULL,`student`JSONNOTNULL,PRIMARYKEY(`id`))...
MySQL入门学习-内置函数.JSON函数
weixin_69763181的博客
06-04 869
接下来，我们使用 JSON_REPLACE 函数更新人员年龄，使用 JSON_INSERT 函数插入新的地址信息。MySQL 提供了一些内置函数来处理 JSON 数据，例如 JSON_ARRAY、JSON_OBJECT、JSON_EXTRACT、JSON_INSERT、JSON_REPLACE、JSON_SET 等。这些函数可以用于创建、操作和查询 JSON 数据。(文章为作者在学习MySQL过程中的一些个人体会总结和借鉴，如有不当、错误的地方，请各位大佬批评指正，定当努力改正，如有侵权请联系作者删帖。
Mysql使用函数json_extract处理Json类型数据
热门推荐
靖节先生的博客
08-24 2万+
Mysql使用函数json_extract处理Json类型数据1. 需求概述2. json_extract简介2.1 函数简介2.2 使用方式2.3 注意事项3. 实现验证3.1 建表查询3.2 查询结果 1. 需求概述 业务开发中通常mysql数据库中某个字段会需要存储json格式字符串，查询的时候有时json数据较大，每次全部取出再去解析查询效率较低，也比较麻烦，则Mysql5.7版本提供提供函数json_extract，可以通过key查询value值，比较方便。 2. json_extract简介 2
mysql支持原生json使用说明
技术宅，专注云原生、Go、Linux、Java等技术分享，原创文章、效率工具、实战解决方案！关注我，了解互联网动态，学 IT 不迷路
06-25 640
mysql支持原生json使用说明详细说明
mysql json格式在C 中不支持_MySQL5.7中对JSON的支持
weixin_28365523的博客
01-18 353
概述从MySQL5.7.8开始，Mysql提供了一个原生的Json类型，Json值将不再以字符串的形式存储，而是采用一种允许快速读取文本元素(document elements)的内部二进制(internal binary)格式。在Json列插入或者更新的时候将会自动验证Json文本，未通过验证的文本将产生一个错误信息。Json文本采用标准的创建方式，可以使用大多数的比较操作符进行比较操作，例如：...
mysql5.7存储json_MySQL 5.7原生JSON格式支持
weixin_36140358的博客
01-17 519
介绍本文将介绍 MySQL 5.7 中如何实现非结构化(JSON)数据的存储，在介绍 MySQL 5.7 的非结构化数据存储之前，首先介绍在之前的 MySQL 的版本中，用户如何通过 BLOB 实现 JSON 对象的存储，以及这样处理的缺点是什么，这些缺点也就是 MySQL 5.7 支持 JSON 的理由；然后我们介绍了 MySQL 5.7 如何支持 JSON 格式，本文将重点关注MySQL 5....
MySQL遇上JSON 一文处理Mysql中的JSON数据 Mysql如何处理嵌套的JSON结构
赵KK的博客
04-26 1052
在这篇文章中，我们探讨了MySQL中JSON数据类型的增删改查操作，以及它们的运行原理和应用场景。JSON数据类型为数据库设计带来了新的灵活性和可能性，作为一名Java架构师，掌握这些技能将极大地提升你的技术实力。
mysql中的json类型_MySQL json类型
weixin_33304896的博客
02-27 2643
在本教程中，您将学习如何使用MySQL JSON数据类型将JSON文档存储在数据库中。MySQL JSON数据类型简介自MySQL5.7.8版本以来，MySQL支持原生JSON数据类型。允许使用原生JSON数据类型比以前MySQL版本中所使用JSON文本格式更能有效地存储JSON文档。MySQL以内部格式存储JSON文档，允许对文档元素的快速读取访问。JSON二进制格式的结构是允许服务器通过键或数...
小码哥MySQL5.7使用原生JSON类型的例子共3页.p
10-31
在“小码哥MySQL5.7使用原生JSON类型的例子共3页.pdf.zip”这个压缩包中，可能包含了多个示例，展示了如何在实际应用中充分利用这一特性。 1. JSON数据类型： MySQL 5.7中的JSON类型可以存储任何有效的JSON格式的...
MySQL操作之JSON数据类型操作详解
09-09
在MySQL中，更新JSON字段可以使用特定的函数，例如`JSON_ARRAY_APPEND`。假设要向`variations`数组中添加元素，可以执行： ```sql UPDATE myjson SET dict = JSON_ARRAY_APPEND(dict, '$.variations', '...
Web开发-实现MySQL中存储JSON数据-Python源码示例.zip
12-13
UPDATE mytable SET json_column = JSON_SET(json_column, '$.key1', 'new_value'); ``` 6. **Python处理查询结果**： 当查询包含JSON字段的结果时，Python连接库会返回字符串形式的JSON数据。我们需要使用`json...
CentOS　MySQL 5.7编译安装步骤详细说明
09-09
5. **原生JSON支持**：MySQL 5.7开始支持JSON数据类型，方便处理非结构化数据。 6. **地理信息服务支持**：InnoDB原生支持地理位置类型，支持GeoJSON和GeoHash。 7. **sys库**：为DBA提供了更方便的监控和管理工具。...
pillow-10.4.0-pp39-pypy39_pp73-macosx_11_0_arm64.whl
07-27
基本介绍 名称与起源：Pillow，原名为PIL（Python Imaging Library），但PIL只支持Python 2版本。随着Python 3的普及，Pillow作为PIL的一个分支出现，兼容Python 3并提供更多的功能和改进。 主要功能：Pillow支持多种图像格式的打开、保存、显示以及基本的图像操作和处理，如裁剪、缩放、旋转、翻转、滤镜应用等。 跨平台性：Pillow库可以在不同的操作系统上运行，包括Windows、Linux和MacOS等。 主要功能模块 Pillow库包含多个功能模块，每个模块都提供了特定的图像处理功能。以下是一些常用的模块： Image：用于处理图像文件，提供打开、保存、调整大小、旋转、裁剪、滤镜等功能。 ImageDraw：提供在图像上绘制各种形状（如线条、矩形、圆形）和文本的功能。 ImageFont：用于加载和使用TrueType字体文件，以便在图像上绘制文本时设置字体样式、大小和颜色。 ImageFilter：提供各种滤镜效果，如模糊、锐化、边缘增强等，用于图像增强、特效处理和图像识别等应用。 ImageEnhance：用于调整图像的亮度、对比度、颜色饱和度等参数，使图像更加清晰、明亮或具有特定的调色效果。 高级功能 除了基本的图像处理功能外，Pillow还支持一些高级功能，如色彩空间转换、直方图均衡化等。这些功能可以帮助用户进行更复杂的图像处理和分析。
avif-0.5.0-cp35-cp35m-macosx_10_9_x86_64.whl
07-27
python库，可以直接安装使用，再也不怕自己pip下载，出现等待超时的现象。欢迎下载使用，使用过程中如有使用问题，请及时与我沟通交流，帮你解决！ python库，可以直接安装使用，再也不怕自己pip下载，出现等待超时的现象。欢迎下载使用，使用过程中如有使用问题，请及时与我沟通交流，帮你解决！ python库，可以直接安装使用，再也不怕自己pip下载，出现等待超时的现象。欢迎下载使用，使用过程中如有使用问题，请及时与我沟通交流，帮你解决！ python库，可以直接安装使用，再也不怕自己pip下载，出现等待超时的现象。欢迎下载使用，使用过程中如有使用问题，请及时与我沟通交流，帮你解决！ python库，可以直接安装使用，再也不怕自己pip下载，出现等待超时的现象。欢迎下载使用，使用过程中如有使用问题，请及时与我沟通交流，帮你解决！
pillow-10.4.0-cp39-cp39-musllinux_1_2_x86_64.whl
07-27
基本介绍 名称与起源：Pillow，原名为PIL（Python Imaging Library），但PIL只支持Python 2版本。随着Python 3的普及，Pillow作为PIL的一个分支出现，兼容Python 3并提供更多的功能和改进。 主要功能：Pillow支持多种图像格式的打开、保存、显示以及基本的图像操作和处理，如裁剪、缩放、旋转、翻转、滤镜应用等。 跨平台性：Pillow库可以在不同的操作系统上运行，包括Windows、Linux和MacOS等。 主要功能模块 Pillow库包含多个功能模块，每个模块都提供了特定的图像处理功能。以下是一些常用的模块： Image：用于处理图像文件，提供打开、保存、调整大小、旋转、裁剪、滤镜等功能。 ImageDraw：提供在图像上绘制各种形状（如线条、矩形、圆形）和文本的功能。 ImageFont：用于加载和使用TrueType字体文件，以便在图像上绘制文本时设置字体样式、大小和颜色。 ImageFilter：提供各种滤镜效果，如模糊、锐化、边缘增强等，用于图像增强、特效处理和图像识别等应用。 ImageEnhance：用于调整图像的亮度、对比度、颜色饱和度等参数，使图像更加清晰、明亮或具有特定的调色效果。 高级功能 除了基本的图像处理功能外，Pillow还支持一些高级功能，如色彩空间转换、直方图均衡化等。这些功能可以帮助用户进行更复杂的图像处理和分析。
1250KVA本体基础图11
最新发布
07-27
1250KVA本体基础图11
mysql json_object排序_mysql中json_object函数的使用?
06-07
MySQL 中的 JSON_OBJECT 函数可以用于将多个键值对转换为 JSON 对象。它的语法如下： ``` JSON_OBJECT(key1, val1, key2, val2, ...) ``` 其中，key1, key2, ... 是键名，val1, val2, ... 是对应的键值。 如果要...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值