MySQL文档属于结构化数据吗

在 MySQL 中学习“NoSQL”

MySQL 从5.7版本开始就支持JSON格式的数据类型，该数据类型支持 JSON 文档的自动验证和优化存储和访问。尽管 JSON 数据最好存储在MongoDB等 NoSQL 数据库中，但您仍然可能会不时遇到包含 JSON 数据的表。在本文的第一节中，我们将介绍如何使用简单的语句从 MySQL 中的 JSON 字段中提取数据。在第二部分中，我们将介绍如何将 MySQL 表中的数据聚合成 JSON 数组或对象，然后可以方便地在您的应用程序中使用。

所要搭建的系统与上一篇关于如何在Python中执行SQL查询的文章中介绍的系统类似。如果您已按照该文章中的说明设置了系统，则可以继续下一节。如果没有，您可以按照下面的简化说明来设置您的系统。有关命令和选项的详细解释，请参考上一篇文章。

本质上，我们将在 Docker 容器中启动本地 MySQL 服务器：

# Create a volume to persist the data.$ docker volume create mysql8-data# Create the container for MySQL.$ docker run --name mysql8 -d -e MYSQL_ROOT_PASSWORD=root -p 13306:3306 -v mysql8-data:/var/lib/mysql mysql:8# Connect to the local MySQL server in Docker.$ docker ｅｘｅｃ -it mysql8 mysql -u root -prootmysql> ｓｅｌｅｃｔ VERSION();+-----------+| VERSION() |+-----------+| 8.0.27|+-----------+1 row in set (0.00 sec)

您可以直接在上面启动的控制台中执行 SQL 查询。或者，如果您更喜欢使用图形界面，则可以安装和使用DBeaver，它是适用于所有类型数据库的出色图形数据库管理器。如果您一直在为 MySQL Workbench 苦苦挣扎，那么它真的值得一试。有关如何安装和设置 DBeaver 的更多详细信息，本文有一个简短但有用的摘要。

让我们首先探讨可用于从 JSON 字段中提取数据的常见 MySQL 函数和运算符。

MySQL 中有两种主要类型的JSON 值：

JSON 数组 — 以逗号分隔并括在方括号 ([]) 中的值列表。JSON 对象 — 字典/哈希图/对象（名称在不同的编程语言中不同），具有一组以逗号分隔并括在大括号 ({}) 中的键值对。

JSON 数组和对象可以相互嵌套，我们将在后面看到。

我们可以使用该JSON_EXTRACT函数从 JSON 字段中提取数据。基本语法是：

JSON_EXTRACT(json_doc, 路径)

对于 JSON 数组，路径由 指定$[index]，其中索引从 0 开始：

mysql>选择 JSON_EXTRACT(‘[10, 20, 30, 40]‘, ‘$[0]‘) ; +----------------------------------------+ | JSON_EXTRACT(‘[10, 20, 30, 40]‘, ‘$[0]‘) | +----------------------------------------+ | 10 | +----------------------------------------+

对于 JSON 对象，路径由 指定$.key，其中key是对象的键。

mysql> ｓｅｌｅｃｔ JSON_EXTRACT(‘{"name": "John", "age": 30}‘, ‘$.name‘) ; +------------------------------------------------ ------+ | JSON_EXTRACT(‘{"name": "John", "age": 30}‘, ‘$.name‘) | +------------------------------------------------ ------+ | “约翰” | +------------------------------------------------ ------+

JSON_EXTRACT如果上面使用的只有两个参数，我们可以使用->作为别名的运算符JSON_EXTRACT。为了演示此运算符的用法，我们需要一个包含 JSON 字段的表。请复制以下 SQL 查询并在 MySQL 控制台或 DBeaver 中执行它们：

CREATE DATABASE IF NOT EXISTS `data`;CREATE TABLE `data`.`student_logs` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(50) NOT NULL,`log` json DEFAULT NULL,PRIMARY KEY (`id`),KEY `ix_name` (name)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;ｉｎｓｅｒｔ INTO `data`.student_logs (id, name, `log`)VALUES(1, ‘Juan‘, ‘{"test_name": "IELTS", "test_id": "T1", "scores": [7.5, 8.0, 8.0, 9.0]}‘),(2, ‘Lee‘, ‘{"test_name": "IELTS", "test_id": "T2", "scores": [8.5, 7.5, 7.0, 8.0]}‘),(3, ‘Kim‘, ‘{"test_name": "IELTS", "test_id": "T3", "scores": [7, 8.5, 8.0, 8.0]}‘),(4, ‘Hans‘, ‘{"test_name": "IELTS", "test_id": "T4", "scores": [6.5, 8.0, 7.5, 9.0]}‘);

特别是，MySQL 使用utf8mb4字符集和utf8mb4_bin排序规则处理 JSON 上下文中使用的字符串。字符集是一组符号和编码，排序规则是一组用于比较字符集中字符的规则。最好使用相应的字符集和排序规则创建带有 JSON 字段的表。

因为utf8mb4_bin是二进制排序规则，键是区分大小写的，我们需要用正确的大小写来指定它们：

SELECTJSON_EXTRACT(`log`, ‘$.test_name‘) AS test_name,-- Correct case, can be extracted.JSON_EXTRACT(`log`, ‘$.TEST_NAME‘) AS TEST_NAME-- Incorrect case, cannot be extracted.FROM `data`.`student_logs`;test_name|TEST_NAME|---------+---------+"IELTS"| |"IELTS"| |"IELTS"| |"IELTS"| |

现在我们可以使用->运算符从 JSON 字段中提取数据：

SELECTid AS student_id,name,JSON_EXTRACT(`log`, ‘$.test_name‘) AS test_name,`log` -> ‘$.test_id‘ AS test_id,`log` -> ‘$.scores‘ AS scoresFROM `data`.`student_logs`;student_id|name|test_name|test_id|scores|----------+----+---------+-------+--------------------+ 1|Juan|"IELTS"|"T1"|[7.5, 8.0, 8.0, 9.0]| 2| Lee|"IELTS"|"T2"|[8.5, 7.5, 7.0, 8.0]| 3| Kim|"IELTS"|"T3"|[7, 8.5, 8.0, 8.0]| 4|Hans|"IELTS"|"T4"|[6.5, 8.0, 7.5, 9.0]|

如我们所见，->只是 . 的快捷方式或别名JSON_EXTRACT。

test_name有趣的是，对于and的引号仍然存在test_id。这不是我们想要的。我们希望删除引号，类似于该name字段。

要删除提取值的引号，我们需要使用该JSON_UNQUOTE函数。由于JSON_UNQUOTE(JSON_EXTRACT(…))如此常用，因此此组合也有一个快捷运算符，即->>. 让我们在实践中看看它：

SELECTid AS student_id,name,JSON_UNQUOTE(JSON_EXTRACT(`log`, ‘$.test_name‘)) AS test_name,`log` ->> ‘$.test_id‘ AS test_id,`log` -> ‘$.scores‘ AS scoresFROM `data`.`student_logs`;student_id|name|test_name|test_id|scores|----------+----+---------+-------+--------------------+ 1|Juan|IELTS|T1 |[7.5, 8.0, 8.0, 9.0]| 2| Lee|IELTS|T2 |[8.5, 7.5, 7.0, 8.0]| 3| Kim|IELTS|T3 |[7, 8.5, 8.0, 8.0]| 4|Hans|IELTS|T4 |[6.5, 8.0, 7.5, 9.0]|

证明->>和JSON_UNQUOTE(JSON_EXTRACT(...))具有相同的结果。由于->>输入的次数少得多，因此在大多数情况下是首选。

但是，如果要从嵌套的 JSON 数组或 JSON 对象中提取数据，则：不能使用 chained->或->>. 您只能将->and用于->>顶层，而需要用于JSON_EXTRACT嵌套层。让我们提取每个学生的分数：

SELECTid AS student_id,name,JSON_UNQUOTE(JSON_EXTRACT(`log`, ‘$.test_name‘)) AS test_name,`log` ->> ‘$.test_id‘ AS test_id,JSON_EXTRACT(`log` -> ‘$.scores‘, ‘$[0]‘) AS listening,JSON_EXTRACT(`log` -> ‘$.scores‘, ‘$[1]‘) AS reading,JSON_EXTRACT(`log` -> ‘$.scores‘, ‘$[2]‘) AS writting,JSON_EXTRACT(`log` -> ‘$.scores‘, ‘$[3]‘) AS speakingFROM `data`.`student_logs`;student_id|name|test_name|test_id|listening|reading|writting|speaking|----------+----+---------+-------+---------+-------+--------+--------+ 1|Juan|IELTS|T1 |7.5|8.0|8.0 |9.0 | 2| Lee|IELTS|T2 |8.5|7.5|7.0 |8.0 | 3| Kim|IELTS|T3 |7|8.5|8.0 |8.0 | 4|Hans|IELTS|T4 |6.5|8.0|7.5 |9.0 |

干杯! 它按预期工作。

从 MySQL 中的 JSON 字段中提取数据的关键要点：

用于$.key从 JSON 对象中提取键的值。用于$[index]从 JSON 数组中提取元素的值。如果值不是字符串，则用作->快捷方式。JSON_EXTRACT如果值是一个字符串并且您想要删除提取的字符串的引号，则用作->>快捷方式。JSON_UNQUOTE(JSON_EXTRACT(...))如果要从嵌套的 JSON 数组或 JSON 对象中提取数据，则不能使用 chained->或->>. 您只能将->and用于->>顶层，而需要用于JSON_EXTRACT嵌套层。

在 MySQL 中还有很多其他函数可以处理 JSON 数据。但是，如果您需要使用这些函数来验证/搜索您的 JSON 字段或对其执行 CRUD 操作，您应该认真考虑使用MongoDB来存储 JSON 字段。MongoDB在处理非结构化数据（文档）方面更加专业方便。

上面我们介绍了如何从MySQL中的JSON字段中提取值。现在我们将学习相反的知识，探索如何从 MySQL 表中选择 JSON 数据。要继续本节，我们需要一些虚拟数据。请复制以下 SQL 查询并在 MySQL 控制台或 DBeaver 中运行它们：

CREATE TABLE `data`.`ielts_scores` (`id` int(11) NOT NULL,`name` varchar(50) NOT NULL,`test_name` varchar(50) NOT NULL,`test_id` varchar(50) NOT NULL,`listening` decimal(2,1) NOT NULL,`reading` decimal(2,1) NOT NULL,`writting` decimal(2,1) NOT NULL,`speaking` decimal(2,1) NOT NULL,PRIMARY KEY (`id`),UNIQUE KEY `uq_test_id` (test_id),KEY `ix_name` (name),KEY `ix_test_name` (test_name)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;ｉｎｓｅｒｔ INTO `data`.`ielts_scores`SELECTid AS student_id,name,`log` -> ‘$.test_name‘ AS test_name,`log` ->> ‘$.test_id‘ AS test_id,JSON_EXTRACT(`log` -> ‘$.scores‘, ‘$[0]‘) AS listening,JSON_EXTRACT(`log` -> ‘$.scores‘, ‘$[1]‘) AS reading,JSON_EXTRACT(`log` -> ‘$.scores‘, ‘$[2]‘) AS writting,JSON_EXTRACT(`log` -> ‘$.scores‘, ‘$[3]‘) AS speakingFROM `data`.`student_logs`;

对于此表，使用默认字符和排序规则。通过这两个查询，我们创建了一个表来存储从第一部分中提取的数据。这是数据管道和分析的常见任务，即在数据清洗后进行一些数据分析。实际上，您可能希望将分数存储在单独的表格中，以便表格更加规范化。但是，这里为了演示简单，将数据放在同一个表中。

我们现在可以使用以下函数将数据聚合到 JSON 数组中JSON_ARRARYAGG：

SELECTJSON_ARRAYAGG(listening) AS listening_scores,JSON_ARRAYAGG(reading) AS reading_scores,JSON_ARRAYAGG(writting) AS writting_scores,JSON_ARRAYAGG(speaking) AS speaking_scoresFROM `data`.`ielts_scores`;listening_scores|reading_scores|writting_scores |speaking_scores |--------------------+--------------------+--------------------+--------------------+[7.5, 8.5, 7.0, 6.5]|[8.0, 7.5, 8.5, 8.0]|[8.0, 7.0, 8.0, 7.5]|[9.0, 8.0, 8.0, 9.0]|

我们还可以使用以下函数将数据聚合到 JSON 对象中JSON_OBJECTAGG：

SELECTJSON_OBJECTAGG(name, ROUND((listening+reading+writting+speaking)/4, 1)) AS ielts_scores FROM `data`.`ielts_scores`;ielts_scores|--------------------------------------------------+{"Kim": 7.9, "Lee": 7.8, "Hans": 7.8, "Juan": 8.1}|

然后可以在您的应用程序中直接使用聚合数据。JSON_ARRARYAGG并且JSON_OBJECTAGG可以节省您在应用程序中聚合数据的工作，有时会很方便。例如，您可以使用该json.loads()方法将 JSON 字符串转换为 Python 中的数组或字典。

如果您需要在 Python 中执行纯 SQL 查询JSON_ARRARYAGG，您可以使用本文JSON_OBJECTAGG中演示的 SQLAlchemy 包。

在本文中，我们介绍了如何在 MySQL 中使用 JSON 数据。在第一部分中，通过简单示例讨论了用于从 JSON 字段中提取数据的函数和运算符。在第二部分中，我们做了相反的操作，将规范化数据聚合到 JSON 数组或对象中，然后可以直接在您的程序中使用。通常我们应该避免在 MySQL 中存储非结构化数据（文档）。但是，如果无法避免，本文中的知识应该对您的工作有所帮助。