MySQL如何比对两个分类ID列表 筛选指定主分类下的地点

前提说明 #

你当前的表设计里main_categories.categories、places.categories都是存储的逗号分隔的数字ID字符串，我们可以基于现有结构直接实现，也可以先优化表结构获得更高查询性能。

方案1：基于现有表结构直接查询 #

核心用FIND_IN_SET函数判断ID是否在逗号分隔列表中，搭配EXISTS子查询实现匹配判断：

SELECT DISTINCT p.*
FROM places p
WHERE EXISTS (
    SELECT 1
    FROM main_categories mc
    -- 拆分主分类绑定的分类ID列表为行数据
    JOIN JSON_TABLE(
        CONCAT('["', REPLACE(mc.categories, ',', '","'), '"]'),
        '$[*]' COLUMNS (cat_id INT PATH '$')
    ) AS mc_cats
    -- 主分类过滤条件，这里替换成你要查的主分类ID/名称
    WHERE mc.id = 1 -- 也可以写 mc.name = 'sport'
    -- 判断地点的分类ID列表是否包含当前主分类绑定的分类ID
    AND FIND_IN_SET(mc_cats.cat_id, p.categories) > 0
);

注：JSON_TABLE函数需要MySQL 8.0及以上版本支持，如果你用的是5.x版本，可以用正则模糊匹配实现（性能稍差）：

SELECT DISTINCT p.*
FROM places p
JOIN main_categories mc ON mc.id = 1 -- 指定主分类条件
WHERE CONCAT(',', p.categories, ',') REGEXP CONCAT(',(', REPLACE(mc.categories, ',', '|'), '),')

方案2：最优表结构改造（推荐） #

当前用逗号分隔列表存储关联ID的设计不符合数据库第一范式，查询性能低、无法加索引、维护成本高，建议改成中间关联表的设计：

• 新增main_category_relations表：存储主分类和子分类的关联关系，字段为main_category_id、category_id
• 新增place_category_relations表：存储地点和分类的关联关系，字段为place_id、category_id

改造后的查询语句性能更高，支持索引优化：

SELECT DISTINCT p.*
FROM places p
JOIN place_category_relations pcr ON p.id = pcr.place_id
JOIN main_category_relations mcr ON pcr.category_id = mcr.category_id
WHERE mcr.main_category_id = 1 -- 替换为你要查询的主分类条件

这种写法可以利用关联字段的索引，数据量越大性能优势越明显。

内容的提问来源于stack exchange，提问作者J4mJeMax