在 PostgreSQL 中，下划线 _ 在模式匹配中有特殊含义，如果需要在普通字符串中正确使用它，需要进行转义处理。以下是详细说明：

1. 下划线的特殊含义

在 PostgreSQL 中，下划线 _ 是 LIKE 和 SIMILAR TO 模式匹配中的通配符：

• _：匹配任意单个字符
• %：匹配任意长度的字符串序列

示例问题：

-- 假设我们想查找包含下划线的字符串
SELECT * FROM users WHERE username LIKE 'user_name';
-- 这实际上会匹配：'user1name', 'userXname', 'user_name' 等
-- 因为 _ 被解释为 "任意单个字符"

2. 转义下划线的几种方法

方法1：使用 ESCAPE 子句（推荐）

-- 使用 ESCAPE 指定转义字符（通常是反斜杠 \）
SELECT * FROM users WHERE username LIKE 'user\_name' ESCAPE '\';

-- 或者使用自定义转义字符
SELECT * FROM users WHERE username LIKE 'user#_name' ESCAPE '#';

方法2：使用反斜杠转义（需要设置标准转义）

-- PostgreSQL 默认关闭反斜杠转义
-- 需要设置 standard_conforming_strings 或使用 E''
SELECT * FROM users WHERE username LIKE E'user\_name';
-- 或者
SET standard_conforming_strings = off;
SELECT * FROM users WHERE username LIKE 'user\_name';

方法3：使用 ILIKE 不区分大小写

-- ILIKE 也使用相同的通配符规则
SELECT * FROM users WHERE username ILIKE 'user\_name' ESCAPE '\';

3. 在实际查询中的应用

示例1：查找包含下划线的数据

-- 创建测试表
CREATE TABLE test_strings (
    id SERIAL PRIMARY KEY,
    text_value VARCHAR(100)
);

-- 插入测试数据
INSERT INTO test_strings (text_value) VALUES
    ('user_name'),
    ('user-name'),
    ('username'),
    ('user1name'),
    ('user_name_extra');

-- 正确查询：查找包含下划线的记录
SELECT * FROM test_strings WHERE text_value LIKE '%\_%' ESCAPE '\';
-- 结果：'user_name', 'user_name_extra'

-- 错误查询（没有转义）
SELECT * FROM test_strings WHERE text_value LIKE '%_%';
-- 结果：返回所有记录，因为 _ 匹配任意单个字符

示例2：精确匹配带下划线的字符串

-- 精确匹配 'user_name'
SELECT * FROM test_strings WHERE text_value LIKE 'user\_name' ESCAPE '\';
-- 结果：'user_name'

-- 匹配以 'user_' 开头的字符串
SELECT * FROM test_strings WHERE text_value LIKE 'user\_%' ESCAPE '\';
-- 结果：'user_name', 'user_name_extra'

4. 正则表达式中的下划线处理

在 PostgreSQL 的 ~ 正则表达式匹配中，下划线是普通字符：

-- 正则表达式中，下划线是普通字符
SELECT * FROM test_strings WHERE text_value ~ 'user_name';
-- 正确匹配 'user_name'

-- 如果需要特殊含义，需要转义
SELECT * FROM test_strings WHERE text_value ~ 'user_name.*';
-- 匹配 'user_name' 开头的字符串

-- 使用 POSIX 正则表达式
SELECT * FROM test_strings WHERE text_value ~ 'user_.*';
-- _ 在这里是普通字符，匹配 'user_' 开头的字符串

5. SIMILAR TO 中的处理

-- SIMILAR TO 也使用 LIKE 的通配符规则
SELECT * FROM test_strings WHERE text_value SIMILAR TO 'user\_%' ESCAPE '\';

6. 常用转义场景总结

场景	正确写法	说明
匹配下划线本身	LIKE '%\_%' ESCAPE '\'	查找包含下划线的字符串
匹配百分号	LIKE '%\%%' ESCAPE '\'	查找包含百分号的字符串
匹配反斜杠	LIKE '%\\%' ESCAPE '\'	查找包含反斜杠的字符串
同时匹配多个	LIKE '%\_%\%%' ESCAPE '\'	查找同时包含下划线和百分号的字符串

7. 使用建议和最佳实践

建议1：始终显式使用 ESCAPE 子句

-- 好习惯：明确指定转义字符
SELECT * FROM table WHERE column LIKE 'pattern\_with\_underscore' ESCAPE '\';

建议2：创建辅助函数处理转义

-- 创建转义函数
CREATE OR REPLACE FUNCTION escape_like_pattern(text)
RETURNS text AS $$
BEGIN
    -- 转义 LIKE 模式中的特殊字符: %, _, \
    RETURN replace(replace(replace($1, '\', '\\'), '%', '\%'), '_', '\_');
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 使用函数
SELECT * FROM test_strings 
WHERE text_value LIKE '%' || escape_like_pattern('user_name') || '%';

建议3：在程序代码中预处理

// Java 示例：预处理 LIKE 模式
public static String escapeLikePattern(String pattern) {
    return pattern.replace("\\", "\\\\")
                  .replace("%", "\\%")
                  .replace("_", "\\_");
}

// 使用
String searchTerm = "user_name";
String escaped = escapeLikePattern(searchTerm);
String sql = "SELECT * FROM table WHERE column LIKE '%" + escaped + "%' ESCAPE '\\'";

建议4：使用参数化查询（防止 SQL 注入）

// 使用 PreparedStatement
String sql = "SELECT * FROM users WHERE username LIKE ? ESCAPE '\\'";
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setString(1, "%" + escapeLikePattern(searchTerm) + "%");

8. 特殊字符转义对照表

字符	在 LIKE 中的含义	转义写法	示例匹配
%	任意长度字符串	\%	匹配百分号字符
_	任意单个字符	\_	匹配下划线字符
\	转义字符本身	\\	匹配反斜杠字符
ESCAPE 字符	自定义转义符	双写该字符	如 ESCAPE '#' 时用 ## 匹配 #

9. 完整示例：实际应用

-- 实际应用：查找日志表中的特定格式
CREATE TABLE logs (
    id SERIAL PRIMARY KEY,
    log_message TEXT,
    created_at TIMESTAMP
);

INSERT INTO logs (log_message, created_at) VALUES
    ('user_123_login', NOW()),
    ('user-456-logout', NOW()),
    ('system_error_500', NOW()),
    ('user_789_action', NOW());

-- 查询所有包含下划线的日志
SELECT * FROM logs WHERE log_message LIKE '%\_%' ESCAPE '\';

-- 查询特定格式：user_数字_动作
SELECT * FROM logs WHERE log_message LIKE 'user\_%\_%' ESCAPE '\';

-- 使用正则表达式（更灵活）
SELECT * FROM logs WHERE log_message ~ '^user_\d+_\w+$';
-- 匹配：user_数字_字母

10. 常见错误和解决方法

错误1：忘记 ESCAPE 子句

-- 错误
SELECT * FROM users WHERE username LIKE 'john_doe';
-- 可能匹配：'john doe', 'johnXdoe', 'john_doe'

-- 正确
SELECT * FROM users WHERE username LIKE 'john\_doe' ESCAPE '\';

错误2：错误使用反斜杠

-- 错误（如果 standard_conforming_strings = on）
SELECT * FROM users WHERE username LIKE 'john\_doe';

-- 正确方式之一
SELECT * FROM users WHERE username LIKE E'john\_doe';

-- 正确方式之二
SELECT * FROM users WHERE username LIKE 'john\_doe' ESCAPE '\';

错误3：混淆 LIKE 和正则表达式

-- LIKE 模式（使用 ESCAPE）
SELECT * FROM logs WHERE log_message LIKE 'error\_%' ESCAPE '\';

-- 正则表达式（直接使用）
SELECT * FROM logs WHERE log_message ~ '^error_.*';

总结

在 PostgreSQL 中处理下划线的关键点：

1. LIKE/ILIKE/SIMILAR TO：必须使用 ESCAPE '\' 转义下划线
2. 正则表达式（~）：下划线是普通字符，无需特殊处理
3. 最佳实践：始终显式指定转义字符，考虑使用辅助函数
4. 安全考虑：使用参数化查询防止 SQL 注入

记住这个简单规则：在 LIKE 模式中，要匹配字面意义的下划线，使用 \_ 并指定 ESCAPE '\'。