PostgreSQL：如何排查并移除非英文、非UTF-8编码的登录字段数据？

1. 定位所有包含 login 字段的表 #

先查询数据库中所有存在login字段的表，方便后续逐个检查：

SELECT table_schema, table_name
FROM information_schema.columns
WHERE column_name = 'login';

2. 扫描不符合要求的 login 记录 #

针对每个目标表，执行以下查询找出包含非英文字符（或非ASCII英文相关字符）的记录。正则表达式可根据实际允许的登录字符调整（比如允许@、.、_、-这类常见登录名符号）：

SELECT
    id, -- 替换为表的主键字段，方便后续定位删除
    login,
    char_length(login) AS 字符长度,
    octet_length(login) AS 字节长度 -- 字节长度大于字符长度说明包含多字节非ASCII字符
FROM
    目标表模式.目标表名
WHERE
    -- 匹配仅包含英文字母、数字及常见登录符号的记录，取反即为不符合的
    login !~ '^[a-zA-Z0-9@._-]+$';

3. 获取客户端输入编码信息 #

实时查看当前连接的客户端编码 #

执行以下命令查看当前会话的客户端编码：

SHOW client_encoding;

查看活跃连接的客户端编码 #

查询当前数据库的活跃连接，获取对应客户端编码：

SELECT
    pid AS 进程ID,
    usename AS 用户名,
    client_encoding AS 客户端编码,
    query AS 执行语句
FROM pg_stat_activity
WHERE state = 'active';

记录历史客户端编码（需提前配置） #

如果需要追踪历史输入的客户端编码，需修改PostgreSQL配置文件postgresql.conf，开启连接日志并记录编码信息：

log_connections = on
log_line_prefix = '%t [%p]: [%c-%l] user=%u,db=%d,client=%h,client_encoding=%e '

修改后重启PostgreSQL服务，后续连接日志中将包含每个会话的客户端编码信息。

4. 清理不符合要求的记录 #

确认目标记录后，执行DELETE语句删除（注意先备份数据，或用BEGIN/ROLLBACK测试）：

DELETE FROM 目标表模式.目标表名
WHERE login !~ '^[a-zA-Z0-9@._-]+$';

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