在 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 exec -it mysql8 mysql -u root -proot
mysql> SELECT 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> SELECT 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
;
INSERT 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是二进制排序规则，键是区分大小写的，我们需要用正确的大小写来指定它们：
SELECT
JSON_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 字段中提取数据：
SELECT
id AS student_id,
name,
JSON_EXTRACT(`log`, '$.test_name') AS test_name,
`log` -> '$.test_id' AS test_id,
`log` -> '$.scores' AS scores
FROM `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(…))如此常用，因此此组合也有一个快捷运算符，即->>. 让我们在实践中看看它：
SELECT
id AS student_id,
name,
JSON_UNQUOTE(JSON_EXTRACT(`log`, '$.test_name')) AS test_name,
`log` ->> '$.test_id' AS test_id,
`log` -> '$.scores' AS scores
FROM `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嵌套层。让我们提取每个学生的分数：
SELECT
id 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 speaking
FROM `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
;
INSERT INTO `data`.`ielts_scores`
SELECT
id 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 speaking
FROM `data`.`student_logs`
;对于此表，使用默认字符和排序规则。通过这两个查询，我们创建了一个表来存储从第一部分中提取的数据。这是数据管道和分析的常见任务，即在数据清洗后进行一些数据分析。实际上，您可能希望将分数存储在单独的表格中，以便表格更加规范化。但是，这里为了演示简单，将数据放在同一个表中。
我们现在可以使用以下函数将数据聚合到 JSON 数组中JSON_ARRARYAGG：
SELECT
JSON_ARRAYAGG(listening) AS listening_scores,
JSON_ARRAYAGG(reading) AS reading_scores,
JSON_ARRAYAGG(writting) AS writting_scores,
JSON_ARRAYAGG(speaking) AS speaking_scores
FROM `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：
SELECT
JSON_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 中存储非结构化数据（文档）。但是，如果无法避免，本文中的知识应该对您的工作有所帮助。