使用pg_escape_string后PostgreSQL查询出现UTF8错误的解决方法

你的问题核心是手动字符串拼接+pg_escape_string的编码不匹配，以及TXT文件中存在的隐性非法UTF-8字符，导致导入后查询触发UTF-8校验错误。以下是具体解决步骤：

1. 强制数据库连接编码与TXT文件编码一致 #

pg_escape_string的转义逻辑依赖当前PostgreSQL连接的client_encoding设置，如果TXT文件编码（如GBK、UTF-8）与连接编码不匹配，会导致转义后的字节序列不符合数据库的UTF-8存储规范。

在建立数据库连接后，立即设置编码：

pg_set_client_encoding($dbConn, 'UTF8'); // 若TXT文件为GBK编码，替换为'GBK'

2. 彻底放弃手动字符串拼接，改用参数化查询 #

pg_escape_string本质是字符串转义，仍存在编码适配风险。参数化查询是PostgreSQL官方推荐的安全无编码问题的方案，会自动处理所有特殊字符和编码转换。

示例代码（使用pg_query_params）： #

// 准备SQL模板，用$1、$2...作为占位符
$sql = 'INSERT INTO "Meters"("AgentCode", "BuildingNr", "MeterNr", "Premises", "SortCode", "PrevReading", "DatePrev", "ConsumptionPrev", "Variance", "MeterName", "CurrOccupant", "MeterType", "MeterNoR","currReading","lastReadingDate","percConsumption","dailyAvg")
        VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17)
        ON CONFLICT ("AgentCode","BuildingNr","MeterNr") DO UPDATE SET 
        "Premises" = $4,
        "SortCode" = $5,
        "PrevReading" = $6,
        "DatePrev" = $7,
        "ConsumptionPrev" = $8,
        "Variance" = $9,
        "MeterName" = $10,
        "CurrOccupant" = $11,
        "MeterType" = $12,
        "MeterNoR" = $13,
        "currReading" = $14,
        "lastReadingDate" = $15,
        "percConsumption" = $16,
        "dailyAvg" = $17';

// 整理参数数组，直接传入原始值，无需pg_escape_string
$params = [
    trim($meterArr[$row][0]),
    trim($meterArr[$row][1]),
    trim($meterArr[$row][2]),
    trim($meterArr[$row][3]),
    trim($meterArr[$row][4]),
    trim($meterArr[$row][5]),
    trim($meterArr[$row][6]),
    trim($meterArr[$row][7]),
    trim($meterArr[$row][8]),
    trim($meterArr[$row][9]),
    trim($meterArr[$row][10]),
    trim($meterArr[$row][11]),
    trim($meterArr[$row][12]),
    trim($meterArr[$row][13]),
    trim($meterArr[$row][14]),
    trim($meterArr[$row][15]),
    trim($meterArr[$row][16])
];

// 执行参数化查询
$result = pg_query_params($dbConn, $sql, $params);

3. 清理TXT文件中的隐性非法字符 #

从你提供的TXT数据行来看，存在"?? "这类字符，大概率是文件中存在的非UTF-8编码字符（如GBK特殊字符转UTF-8失败），即使删除单引号，这些字符也可能在后续查询中触发错误。可在读取数据时过滤或转换：

// 读取每行后，转换为UTF-8并过滤非法字符
$line = mb_convert_encoding($line, 'UTF-8', 'auto');
$line = preg_replace('/[\x00-\x1F\x7F-\xFF]/', '', $line); // 过滤控制字符和非打印字符

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