在 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 中存储非结构化数据（文档）。但是，如果无法避免，本文中的知识应该对您的工作有所帮助。