如何在SQL多列中实现多值匹配查询（含JPA场景）

数据库表结构 #

需求说明 #

• 单字符串搜索：例如搜索ka，需返回所有firstName或lastName包含ka的记录（Kalinda、Karolyn、Michel），当前使用的SQL语句为：

SELECT * FROM table WHERE firstName LIKE '%ka%' OR lastName LIKE '%ka%'

• 多字符串组合搜索：例如搜索ka he，需要返回同时包含所有搜索字符串的记录（Kalinda、Michel），且搜索字符串的数量不固定。此前尝试为每个值单独查询再去重，结果不符合预期且性能较差；试过SIMILAR TO写法但它是OR逻辑，需要替换为AND逻辑的可行方案。

解决方案 #

1. 通用原生SQL实现 #

针对每个搜索关键词，添加(firstName LIKE '%关键词%' OR lastName LIKE '%关键词%')的条件，再用AND连接所有条件。以搜索ka he为例：

SELECT * 
FROM table 
WHERE (firstName LIKE '%ka%' OR lastName LIKE '%ka%')
  AND (firstName LIKE '%he%' OR lastName LIKE '%he%');

该写法兼容性强，支持所有标准SQL数据库，且可根据关键词数量动态拼接条件。

2. PostgreSQL专属正则实现 #

如果使用PostgreSQL，可以借助正则匹配操作符~实现AND逻辑，同时拼接字段时加入空格避免跨字段误匹配：

SELECT * 
FROM table 
WHERE CONCAT(firstName, ' ', lastName) ~ 'ka'
  AND CONCAT(firstName, ' ', lastName) ~ 'he';

3. JPA动态查询实现 #

利用JPA的CriteriaBuilder动态构建查询，适配任意数量的搜索关键词，示例代码如下：

public List<User> searchByKeywords(List<String> keywords) {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<User> query = cb.createQuery(User.class);
    Root<User> root = query.from(User.class);
    
    List<Predicate> predicates = new ArrayList<>();
    for (String keyword : keywords) {
        Predicate keywordPredicate = cb.or(
            cb.like(root.get("firstName"), "%" + keyword + "%"),
            cb.like(root.get("lastName"), "%" + keyword + "%")
        );
        predicates.add(keywordPredicate);
    }
    
    query.where(cb.and(predicates.toArray(new Predicate[0])));
    return entityManager.createQuery(query).getResultList();
}

该方式无需多次查询去重，能精准生成符合需求的SQL，同时保证性能。

内容的提问来源于stack exchange，提问作者Danilo Körber