MySQL JSON列查询问题：JPA自定义查询无法按Genre与Viewed状态筛选电影

问题根源分析 #

你当前的查询存在三个核心问题：

1. 参数绑定错误：原生SQL里的'%genres%'是固定字符串字面量，并非引用传入的方法参数，导致实际查询的是包含"genres"字符串的记录，而非你传入的"комедия"。
2. 缺少状态筛选：没有加入viewed字段的筛选条件，无法按已观看状态过滤数据。
3. JSON匹配精度问题：如果genres字段的$.genres是数组结构，用LIKE匹配会存在精度不足的情况（比如误匹配包含目标关键词的其他分类）。

解决方案 #

方案1：使用JSON_CONTAINS精准匹配JSON数组（推荐）

如果你的genres列JSON结构是类似{"genres": ["комедия", "драма"]}的数组形式，用MySQL的JSON_CONTAINS函数可以精准匹配数组中的元素，同时正确绑定参数并加入viewed筛选：

@Repository
public interface FilmRepository extends JpaRepository<Film, Long> {
    @Query(nativeQuery = true, value = "SELECT * FROM film " +
            "WHERE JSON_CONTAINS(genres, JSON_QUOTE(:genres), '$.genres') " +
            "AND viewed = :viewed")
    List<Film> findFilmsByGenresAndViewed(@Param("genres") String genres, @Param("viewed") Boolean viewed);
}

• JSON_QUOTE(:genres)：将传入的字符串参数转换为JSON格式的字符串（比如把комедия转为"комедия"），确保能被JSON_CONTAINS正确识别。
• JSON_CONTAINS(genres, ..., '$.genres')：检查genres列的$.genres数组中是否包含目标分类。
• AND viewed = :viewed：加入已观看状态的筛选条件。

方案2：使用LIKE模糊匹配（适合非数组或需要模糊场景）

如果$.genres是单个字符串而非数组，或者你需要模糊匹配分类名称，可以用CONCAT拼接通配符，同时修复参数绑定：

@Repository
public interface FilmRepository extends JpaRepository<Film, Long> {
    @Query(nativeQuery = true, value = "SELECT * FROM film " +
            "WHERE JSON_UNQUOTE(JSON_EXTRACT(genres, '$.genres')) LIKE CONCAT('%', :genres, '%') " +
            "AND viewed = :viewed")
    List<Film> findFilmsByGenresAndViewed(@Param("genres") String genres, @Param("viewed") Boolean viewed);
}

• CONCAT('%', :genres, '%')：动态将参数与通配符拼接，实现模糊匹配。
• JSON_UNQUOTE(JSON_EXTRACT(...))：提取JSON字段的值并去除引号，确保LIKE能正确匹配。

额外检查项 #

1. 确认实体类中genres字段的映射正确，比如使用JPA的@Column(columnDefinition = "JSON")：

@Column(columnDefinition = "JSON")
private String genres;

如果使用Hibernate Types库，也可以直接映射为集合类型：

@Type(type = "json")
@Column(columnDefinition = "JSON")
private List<String> genres;

2. 提前验证SQL逻辑：可以先在MySQL客户端执行测试SQL，确认筛选结果符合预期：

SELECT * FROM film 
WHERE JSON_CONTAINS(genres, JSON_QUOTE('комедия'), '$.genres') 
AND viewed = false;

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