记一次 MySQL 8.0 JSON 查询

xiaobinqt 收录于类别 Mysql

2023-10-12 2023-10-12 约 862 字预计阅读 4 分钟

有 2 张表，一张 tag 表，主要字段有 id，name。一张 channel 表，有个 tags 字段，存 tag 表中 id，如：[1,5]。

现在的需求是，通过 channel 表可以支持模糊搜索 tag 的 name，比如 tag 表中的数据如下：

channel 表中的某条数据的 tags 字段的值是 [5,6]，那我就可以通过搜索 测试系统 找到这条 channel 数据。

首先，可以使用 JSON 函数来解析 tags 列，然后再连接 tag 表以匹配 id。以下是一个示例 SQL 查询：

sql

SELECT c.*
FROM channel c
         INNER JOIN tag t ON JSON_CONTAINS(c.tags, CAST(t.id AS JSON))
WHERE t.name LIKE '%your_search_query%';

这个查询的步骤是：

FROM channel c：从 channel 表中选择数据。
INNER JOIN tag t ON JSON_CONTAINS(c.tags, CAST(t.id AS JSON))：将 channel 表与 tag 表连接，其中 JSON_CONTAINS 用于检查 tag 是否在 channel 的 JSON 数组中。
WHERE t.name LIKE '%your_search_query%'：在连接后的结果中，使用 LIKE 子句来执行模糊匹配。

在上面的查询中，可以将 %your_search_query% 替换为想搜索的标签名称的部分。这将返回包含匹配的 tag 名称的结果集。

当在 SQL 中处理 JSON 数据时，可能需要将数据转换为 JSON 类型以进行比较或操作。在查询中，JSON_CONTAINS(c.tags, CAST(t.id AS JSON)) 是一个用于检查 JSON 数组中是否包含特定值的 SQL 表达式。

以下是对这个表达式的详细解释：

CAST(t.id AS JSON)：这部分将 t.id 转换为 JSON 数据类型。在 SQL 中，CAST 函数用于将一个数据类型转换为另一个数据类型。在这里，t.id 是整数类型，通过 CAST(t.id AS JSON)，它被显式地转换为 JSON 类型。这是因为 c.tags 是一个 JSON 数据类型，所以需要确保进行比较的值也是 JSON 类型。
JSON_CONTAINS(c.tags, CAST(t.id AS JSON))：这是主要的比较部分。JSON_CONTAINS 函数用于检查一个 JSON 数组（在这里是 c.tags）是否包含特定值（在这里是 t.id 的 JSON 表示）。如果 c.tags 包含 t.id 的 JSON 表示，它将返回 true；否则，返回 false。这就允许查找 channel 表中具有特定 tag 的记录。

再用一个示例来解释这个过程：

假设 channel 表的某一行的 tags 列中包含 JSON 数组 [1, 3, 5]，想查找所有包含 tag 表中 id 为 3 的记录。

这就是如何使用 CAST 函数和 JSON_CONTAINS 函数来进行 JSON 数据的比较和查询。在这个例子中，它允许在 channel 表中查找包含特定 tag 的记录。