Spring Data多值匹配：如何过滤含字符串列表的表行？

现有字符串：

String students = "Ram,Jack,jim.. and so on";

sports_group表结构及数据如下：

需求：筛选出name列与students字符串至少有一个共同值的行，期望输出：

{
    "Sport_id":"Fb100",
    "name":"Atul,Mani,Ram,Gopal",
    "school":"St. marry",
    "city":"Delhi"
},
{
    "Sport_id":"Cri101",
    "name":"Jack,Marry,Kim",
    "school":"Govt. school",
    "city":"Delhi"
}

当前使用的Spring Data Repository原生查询：

@Query(value = "select * from sports_group  g where FIND_IN_SET(?1, g.name)<>0  order by g.modifiedtime desc", nativeQuery = true)
Slice<SprtsGroup> searchByNames(String students, Pageable pageable);

问题：该查询仅在students为单个值（如"Ram"）时生效，当students为多个值（如"Ram,Kim"）时无法正常工作，需优化查询实现需求。

方法1：正则表达式匹配（MySQL适用） #

将输入的students字符串转换为正则表达式，匹配name列中包含任意目标学生的记录：

@Query(value = "select * from sports_group g where g.name regexp replace(?1, ',', '|') order by g.modifiedtime desc", nativeQuery = true)
Slice<SprtsGroup> searchByNames(String students, Pageable pageable);

原理：replace(?1, ',', '|')会把"Ram,Jack"转换成"Ram|Jack"，正则的|表示“或”逻辑，只要name列包含其中任意一个值就会匹配。

注意：如果学生名字包含正则特殊字符（如.、*），需要提前转义，避免匹配异常。

方法2：递归CTE拆分参数（MySQL 8.0+支持） #

用递归公共表表达式（CTE）拆分逗号分隔的students字符串，再结合FIND_IN_SET匹配：

@Query(value = "with recursive nums(n) as (select 1 union all select n+1 from nums where n <= length(?1) - length(replace(?1, ',', '')) + 1) " +
               "select distinct g.* from sports_group g " +
               "join (select substring_index(substring_index(?1, ',', n), ',', -1) as student from nums) t " +
               "on find_in_set(t.student, g.name) > 0 " +
               "order by g.modifiedtime desc", nativeQuery = true)
Slice<SprtsGroup> searchByNames(String students, Pageable pageable);

原理：递归生成数字序列拆分输入字符串，再逐行匹配name列中的学生值。

方法3：Spring Data Specification动态构建查询 #

如果不想用原生SQL，可先拆分students为集合，再动态生成OR条件：

public Slice<SprtsGroup> searchByNames(String students, Pageable pageable) {
    List<String> studentList = Arrays.asList(students.split(","));
    Specification<SprtsGroup> spec = (root, query, cb) -> {
        List<Predicate> predicates = new ArrayList<>();
        for (String student : studentList) {
            predicates.add(cb.greaterThan(
                cb.function("FIND_IN_SET", Integer.class, cb.literal(student), root.get("name")),
                0
            ));
        }
        return cb.or(predicates.toArray(new Predicate[0]));
    };
    return sprtsGroupRepository.findAll(spec, pageable);
}

长期优化建议 #

当前逗号分隔存储学生名字的设计不符合数据库范式，数据量较大时查询性能会明显下降。建议拆分出关联表（如sport_group_student），用多对多关联存储，查询会更高效且易于维护。

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