MySQL列表比较：如何判断指定分类列表是电影分类列表的子集

核心思路 #

利用数据库原生JSON函数，直接在HAVING子句中校验传入的分类列表是否是JSON_ARRAYAGG生成的电影分类数组的子集，无需在应用层额外处理，高效简洁。

MySQL 实现方案（支持JSON_CONTAINS_ALL） #

MySQL 8.0及以上版本提供JSON_CONTAINS_ALL函数，可直接判断目标数组的所有元素是否都存在于聚合生成的分类数组中。

示例SQL

SELECT 
    m.id, m.name, JSON_ARRAYAGG(DISTINCT c.name) AS categories
FROM movies m
JOIN movie_categories mc ON m.id = mc.movie_id
JOIN categories c ON mc.category_id = c.id
GROUP BY m.id, m.name
HAVING JSON_CONTAINS_ALL(categories, CAST(? AS JSON))

Spring Boot JDBC 传参示例

将目标分类列表转为JSON字符串传入：

List<String> targetCategories = List.of("Action", "Drama");
ObjectMapper objectMapper = new ObjectMapper();
String jsonParam = objectMapper.writeValueAsString(targetCategories);

List<Movie> result = jdbcTemplate.query(
    "SELECT m.id, m.name, JSON_ARRAYAGG(DISTINCT c.name) AS categories " +
    "FROM movies m " +
    "JOIN movie_categories mc ON m.id = mc.movie_id " +
    "JOIN categories c ON mc.category_id = c.id " +
    "GROUP BY m.id, m.name " +
    "HAVING JSON_CONTAINS_ALL(categories, CAST(? AS JSON))",
    new Object[]{jsonParam},
    (rs, rowNum) -> {
        Movie movie = new Movie();
        movie.setId(rs.getLong("id"));
        movie.setName(rs.getString("name"));
        movie.setCategories(objectMapper.readValue(rs.getString("categories"), new TypeReference<List<String>>() {}));
        return movie;
    }
);

PostgreSQL 实现方案（支持@>操作符） #

PostgreSQL中可直接用数组包含操作符@>，判断传入的JSON数组是否被聚合生成的分类数组包含。

示例SQL

SELECT 
    m.id, m.name, JSON_ARRAYAGG(DISTINCT c.name) AS categories
FROM movies m
JOIN movie_categories mc ON m.id = mc.movie_id
JOIN categories c ON mc.category_id = c.id
GROUP BY m.id, m.name
HAVING categories @> CAST(? AS JSON)

Spring Boot JDBC 传参示例

传参逻辑与MySQL一致，只需替换SQL语句即可。

关键优化与注意事项 #

• 去重处理：在JSON_ARRAYAGG中加入DISTINCT，避免同一电影的重复分类导致数组冗余，不影响子集判断但能减少数据传输量。
• 版本要求：确保数据库版本支持对应JSON函数（MySQL 8.0+、PostgreSQL 9.3+）。
• 性能优化：给movie_categories(movie_id, category_id)和categories(id, name)建立联合索引，提升关联查询的效率。

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