PostgreSQL中多模式匹配与排除：LIKE ANY与正则表达式的深度解析

在postgresql中，直接使用`like in`是无效的语法。本文将深入探讨如何在postgresql中正确地进行多模式匹配与排除，介绍`like any`的有效用法，并重点推荐使用正则表达式运算符（`~`和`!~`）结合数组字面量或参数进行高效且灵活的模式匹配与排除，尤其是在处理多个字符串模式时，正则表达式提供了更简洁强大的解决方案，同时也会讨论null值的处理。

1. 理解无效的LIKE IN语法

许多开发者可能会尝试将LIKE操作符与IN操作符结合，以期在一个字段中匹配或排除多个模式，例如：

-- 这是一个无效的语法示例
SELECT * FROM your_table WHERE varchar_field LIKE IN ('%John%', '%Bob%', '%Sean%');

这种写法在PostgreSQL中是语法错误的，因为它混淆了LIKE（用于单个模式匹配）和IN（用于精确值匹配）的语义。IN操作符期望的是一个精确的值列表，而不是模式列表。

2. 使用LIKE ANY进行多模式匹配

PostgreSQL提供了ANY操作符，可以与LIKE结合使用，实现对多个模式的匹配。LIKE ANY的语法是有效的，并且可以满足在给定模式列表中匹配任意一个模式的需求。

-- 示例：匹配包含'John'、'Bob'或'Sean'的记录
SELECT *
FROM your_table
WHERE varchar_field LIKE ANY (ARRAY['%John%', '%Bob%', '%Sean%']);

或者，如果你想从一个数组参数中获取模式：

-- 示例：使用参数化的数组进行LIKE ANY匹配
SELECT *
FROM your_table
WHERE varchar_field LIKE ANY (:listOrArrayOfNames); -- :listOrArrayOfNames 是一个包含模式字符串的数组参数

这里的ARRAY['%John%', '%Bob%', '%Sean%']是一个PostgreSQL的数组字面量。LIKE ANY会检查varchar_field是否匹配数组中的任何一个模式。

3. 利用正则表达式进行多模式匹配与排除

对于更复杂或更高效的多模式匹配和排除场景，PostgreSQL的正则表达式操作符（~用于匹配，!~用于不匹配）通常是更好的选择。它们提供了比LIKE更强大的功能，并且在处理多个模式时，结合数组和ALL操作符可以非常简洁。

3.1 排除包含任何指定字符串的记录

要排除所有包含指定字符串（例如'John'、'Bob'、'Sean'）的记录，可以使用!~ ALL结合正则表达式：

-- 示例：排除包含'John'、'Bob'或'Sean'的记录
SELECT *
FROM your_table
WHERE varchar_field !~ ALL (ARRAY['John', 'Bob', 'Sean']);

或者，如果从参数传入一个字符串数组：

-- 示例：使用参数化的数组排除记录
SELECT *
FROM your_table
WHERE varchar_field !~ ALL (:array_of_strings); -- :array_of_strings 是一个包含字符串的数组参数

关键点：

• !~ 是PostgreSQL的“不匹配正则表达式”操作符。
• ALL 操作符确保varchar_field不匹配数组中的所有正则表达式。
• 与LIKE不同，正则表达式操作符在默认情况下会进行子字符串匹配，因此通常不需要显式添加前导和尾随的%通配符。例如，'John'作为正则表达式会匹配任何包含"John"的字符串。
• PostgreSQL的数组字面量格式为'{item1,item2,item3}'，例如'{John,Bob,Sean}'。

3.2 匹配包含任何指定字符串的记录

如果需要匹配包含任何指定字符串的记录，可以使用~ ANY：

文心大模型

百度飞桨-文心大模型 ERNIE 3.0 文本理解与创作

56

查看详情

-- 示例：匹配包含'John'、'Bob'或'Sean'的记录
SELECT *
FROM your_table
WHERE varchar_field ~ ANY (ARRAY['John', 'Bob', 'Sean']);

或者，更常见且简洁的方式是使用单个正则表达式模式，通过|（或）操作符连接：

-- 示例：使用单个正则表达式匹配包含'John'、'Bob'或'Sean'的记录
SELECT *
FROM your_table
WHERE varchar_field ~ 'John|Bob|Sean';

这种方式通常更推荐，因为它避免了构建数组的开销，并且在正则表达式引擎内部处理效率更高。

4. NULL值的处理

在使用这些操作符进行WHERE条件过滤时，需要注意NULL值的行为。在PostgreSQL中，任何与NULL值进行比较的表达式（除了IS NULL或IS NOT NULL）结果都将是NULL。WHERE子句只通过结果为TRUE的行。

这意味着：

• 如果varchar_field为NULL，则varchar_field !~ ALL (ARRAY['John', 'Bob', 'Sean'])的结果是NULL。
• 由于NULL不等于TRUE，所以包含NULL的行将不会被上述WHERE条件包含在结果集中。

如果你希望包含varchar_field为NULL的行，需要显式地添加条件：

SELECT *
FROM your_table
WHERE varchar_field !~ ALL (ARRAY['John', 'Bob', 'Sean']) OR varchar_field IS NULL;

5. 与Data JPA的集成

对于使用Data JPA（或其他ORM框架）的场景，可以通过原生查询（Native Query）来传递数组或列表参数。例如，在Spring Data JPA中，你可以定义一个原生查询，并使用@Param注解将Java集合类型绑定到PostgreSQL的数组参数：

public interface YourRepository extends JpaRepository<YourEntity, Long> {

    @Query(value = "SELECT * FROM your_table WHERE varchar_field !~ ALL (:excludedNames)", nativeQuery = true)
    List<YourEntity> findByVarcharFieldExcludingNames(@Param("excludedNames") String[] excludedNames);

    // 或者使用List<String>
    @Query(value = "SELECT * FROM your_table WHERE varchar_field !~ ALL (:excludedNames)", nativeQuery = true)
    List<YourEntity> findByVarcharFieldExcludingNames(@Param("excludedNames") List<String> excludedNames);
}

当Java的String[]或List<String>被绑定到原生查询的:参数时，Data JPA通常会将其转换为PostgreSQL可以识别的数组字面量格式（例如'{John,Bob,Sean}'），从而使!~ ALL (:excludedNames)这样的语句能够正常工作。

总结与最佳实践

• 避免使用LIKE IN：这是无效的语法。
• 使用LIKE ANY：如果你需要使用LIKE的通配符语义（例如%），并且匹配多个模式中的任意一个，LIKE ANY (ARRAY[...])是正确的选择。
• 首选正则表达式：对于多模式匹配和排除，尤其是子字符串匹配，PostgreSQL的正则表达式操作符（~和!~）通常更强大、更简洁。
  • 排除多个模式：推荐使用!~ ALL (ARRAY['pattern1', 'pattern2'])。
  • 匹配多个模式：推荐使用~ 'pattern1|pattern2'或~ ANY (ARRAY['pattern1', 'pattern2'])。
• 注意NULL值：WHERE子句默认不包含NULL结果的行，如果需要，请显式处理。
• Data JPA集成：通过原生查询可以将Java集合作为数组参数传递给PostgreSQL的ANY、ALL或正则表达式操作符。

理解并正确运用这些PostgreSQL的强大功能，将使你在处理复杂的字符串模式匹配和排除任务时更加高效和灵活。

以上就是PostgreSQL中多模式匹配与排除：LIKE ANY与正则表达式的深度解析的详细内容，更多请关注php中文网其它相关文章！