TypeORM查询PostgreSQL时出现UTF8无效字节序列错误求助

• 技术栈：PostgreSQL 15.7、NestJS 后端、PM2 进程管理器
• 触发场景：调用getAllLicense方法查询数据时偶尔出现编码错误
• 错误内容：

invalid byte sequence for encoding "UTF8": 0xa0

• 临时处理：重启PM2服务可暂时消除错误，但无法根治；已检查数据库数据，未发现肉眼可见异常

相关代码：

async getAllLicense(): Promise<any> {
    try {
      const items = await this.find({
        relations: { wfloType: true, lookupStatus: true , licensesClassfication:true  },
        order: { license_id: 'ASC' },
      });
      return items;
    } catch (error) {
      return {
        status: HttpStatus.INTERNAL_SERVER_ERROR,
        messageEn: 'Error retrieving items',
        messageAr: 'خطأ في جلب العناصر',
        error: error.message,
      };
    }
  }

完整错误日志：

query: 'SELECT "License"."licenses_id" AS "License_licenses_id", "License"."license_ar" AS "License_license_ar", "License"."license_en" AS "License_license_en", "License"."wflo_type_id" AS "License_wflo_type_id", "License"."licenses_classfication_id" AS "License_licenses_classfication_id", "License"."created_by" AS "License_created_by", "License"."last_updated_by" AS "License_last_updated_by", "License"."created_date" AS "License_created_date", "License"."last_updated_date" AS "License_last_updated_date", "License"."status" AS "License_status", "License__License_wfloType"."created_by" AS "License__License_wfloType_created_by", "License__License_wfloType"."last_updated_by" AS "License__License_wfloType_last_updated_by", "License__License_wfloType"."created_date" AS "License__License_wfloType_created_date", "License__License_wfloType"."last_updated_date" AS "License__License_wfloType_last_updated_date", "License__License_wfloType"."status" AS "License__License_wfloType_status", "License__License_wfloType"."wflo_type_id" AS "License__License_wfloType_wflo_type_id", "License__License_wfloType"."name_en" AS "License__License_wfloType_name_en", "License__License_wfloType"."name_ar" AS "License__License_wfloType_name_ar", "License__License_wfloType"."final_email_notify" AS "License__License_wfloType_final_email_notify", "License__License_wfloType"."final_sms_notify" AS "License__License_wfloType_final_sms_notify", "License__License_wfloType"."valid " AS "License__License_wfloType_valid ", "License__License_lookupStatus"."lookup_detail_id" AS "License__License_lookupStatus_lookup_detail_id", "License__License_lookupStatus"."lookup_detail_ar_name" AS "License__License_lookupStatus_lookup_detail_ar_name", "License__License_lookupStatus"."lookup_detail_en_name" AS "License__License_lookupStatus_lookup_detail_en_name", "License__License_lookupStatus"."status" AS "License__License_lookupStatus_status", "License__License_lookupStatus"."created_by" AS "License__License_lookupStatus_created_by", "License__License_lookupStatus"."created_date" AS "License__License_lookupStatus_created_date", "License__License_lookupStatus"."last_updated_by" AS "License__License_lookupStatus_last_updated_by", "License__License_lookupStatus"."last_updated_date" AS "License__License_lookupStatus_last_updated_date", "License__License_lookupStatus"."lookup_id" AS "License__License_lookupStatus_lookup_id", "License__License_licensesClassfication"."licenses_classfication_id" AS "8e46c0678ea314f98067cf187c049d051caa0c39", "License__License_licensesClassfication"."licenses_classfication_ar" AS "fb5dde8942ccb4d0ad04254537a2a7fba0ad2d23", "License__License_licensesClassfication"."licenses_classfication_en" AS "0627108520845c530702b5184949cc19b9d1d5bc", "License__License_licensesClassfication"."created_by" AS "License__License_licensesClassfication_created_by", "License__License_licensesClassfication"."last_updated_by" AS "License__License_licensesClassfication_last_updated_by", "License__License_licensesClassfication"."created_date" AS "License__License_licensesClassfication_created_date", "License__License_licensesClassfication"."last_updated_date" AS "License__License_licensesClassfication_last_updated_date", "License__License_licensesClassfication"."status" AS "License__License_licensesClassfication_status" FROM "license"."license" "License" LEFT JOIN "workflow"."wflo_type" "License__License_wfloType" ON "License__License_wfloType"."wflo_type_id"="License"."wflo_type_id"  LEFT JOIN "admin"."lookup_details" "License__License_lookupStatus" ON "License__License_lookupStatus"."lookup_detail_id"="License"."status"  LEFT JOIN "license"."licenses-classfication" "License__License_licensesClassfication" ON "License__License_licensesClassfication"."licenses_classfication_id"="License"."licenses_classfication_id" ORDER BY "License"."licenses_id" ASC',
1|jhdms  |   parameters: [],
1|jhdms  |   driverError: error: invalid byte sequence for encoding "UTF8": 0xa0
1|jhdms  |       at /jhdms/backend/node_modules/pg/lib/client.js:526:17
1|jhdms  |       at processTicksAndRejections (node:internal/process/task_queues:105:5)
1|jhdms  |       at PostgresQueryRunner.query (/jhdms/backend/src/driver/postgres/PostgresQueryRunner.ts:260:25)
1|jhdms  |       at SelectQueryBuilder.loadRawResults (/jhdms/backend/src/query-builder/SelectQueryBuilder.ts:3805:25)
1|jhdms  |       at SelectQueryBuilder.executeEntitiesAndRawResults (/jhdms/backend/src/query-builder/SelectQueryBuilder.ts:3551:26)
1|jhdms  |       at SelectQueryBuilder.getRawAndEntities (/jhdms/backend/src/query-builder/SelectQueryBuilder.ts:1670:29)
1|jhdms  |       at SelectQueryBuilder.getMany (/jhdms/backend/src/query-builder/SelectQueryBuilder.ts:1760:25)
1|jhdms  |       at LicenseRepo.getAllLicense (/jhdms/backend/src/licenses/license/license.repo.ts:119:21)
1|jhdms  |       at /jhdms/backend/node_modules/@nestjs/core/router/router-execution-context.js:46:28
1|jhdms  |       at /jhdms/backend/node_modules/@nestjs/core/router/router-proxy.js:9:17 {
1|jhdms  |     length: 117,
1|jhdms  |     severity: 'ERROR',
1|jhdms  |     code: '22021',
1|jhdms  |     detail: undefined,
1|jhdms  |     hint: undefined,
1|jhdms  |     position: undefined,
1|jhdms  |     internalPosition: undefined,
1|jhdms  |     internalQuery: undefined,
1|jhdms  |     where: undefined,
1|jhdms  |     schema: undefined,
1|jhdms  |     table: undefined,
1|jhdms  |     column: undefined,
1|jhdms  |     dataType: undefined,
1|jhdms  |     constraint: undefined,
1|jhdms  |     file: 'mbutils.c',
1|jhdms  |     line: '1665',
1|jhdms  |     routine: 'report_invalid_encoding'
1|jhdms  |   },
1|jhdms  |   length: 117,
1|jhdms  |   severity: 'ERROR',
1|jhdms  |   code: '22021',
1|jhdms  |   detail: undefined,
1|jhdms  |   hint: undefined,
1|jhdms  |   position: undefined,
1|jhdms  |   internalPosition: undefined,
1|jhdms  |   internalQuery: undefined,
1|jhdms  |   where: undefined,
1|jhdms  |   schema: undefined,
1|jhdms  |   table: undefined,
1|jhdms  |   column: undefined,
1|jhdms  |   dataType: undefined,
1|jhdms  |   constraint: undefined,
1|jhdms  |   file: 'mbutils.c',
1|jhdms  |   line: '1665',
1|jhdms  |   routine: 'report_invalid_encoding'
1|jhdms  | }

0xa0是非-breaking空格（NBSP），属于Latin-1编码字符，UTF-8中需用C2 A0表示，单独的0xa0不符合UTF-8编码规则，以下是具体排查和修复步骤：

1. 修正实体类字段名的隐性错误 #

从错误日志的查询语句中可以看到"valid "字段名带有NBSP（隐性空格），这是核心问题之一：

• 检查WfloType实体的字段定义，将带NBSP的字段名修正为正常拼写：

  // 原错误定义
  @Column({ name: 'valid ' }) // 字段名包含NBSP
  valid: boolean;
  // 修正为
  @Column({ name: 'valid' })
  valid: boolean;
  

• 同步修正数据库表的字段名（如果表结构确实存在该问题）：

  ALTER TABLE workflow.wflo_type RENAME COLUMN "valid " TO valid;
  

2. 定位并清理数据库中的非法字符 #

用SQL查询找出包含单个0xa0字节的数据：

-- 检查license表的文本字段
SELECT license_id, license_ar, license_en
FROM license.license
WHERE license_ar ~ '[^\u0000-\u007F\u00C0-\u00FF\u0100-\u017F\u0180-\u024F\u1E00-\u1EFF]'
   OR license_en ~ '[^\u0000-\u007F\u00C0-\u00FF\u0100-\u017F\u0180-\u024F\u1E00-\u1EFF]';

-- 检查wflo_type表的字段
SELECT wflo_type_id, valid
FROM workflow.wflo_type
WHERE valid::text ~ '[^\u0000-\u007F\u00C0-\u00FF\u0100-\u017F\u0180-\u024F\u1E00-\u1EFF]';

找到异常数据后，替换非法字符为正常空格：

UPDATE license.license
SET license_ar = REPLACE(license_ar, CHR(0xa0), ' '),
    license_en = REPLACE(license_en, CHR(0xa0), ' ')
WHERE license_ar LIKE '%' || CHR(0xa0) || '%'
   OR license_en LIKE '%' || CHR(0xa0) || '%';

3. 确保数据库连接编码正确 #

在NestJS的TypeOrm配置中明确指定UTF-8编码：

// app.module.ts中的TypeOrm配置
TypeOrmModule.forRoot({
  type: 'postgres',
  host: 'your-host',
  port: 5432,
  username: 'your-user',
  password: 'your-pass',
  database: 'your-db',
  charset: 'utf8',
  extra: {
    encoding: 'UTF8'
  },
  // 其他配置...
})

4. 配置PM2的字符集环境变量 #

修改PM2配置文件（如ecosystem.config.js），添加字符集环境变量：

module.exports = {
  apps: [{
    name: 'jhdms',
    script: 'dist/main.js',
    env: {
      NODE_ENV: 'production',
      LC_ALL: 'en_US.UTF-8',
      LANG: 'en_US.UTF-8'
    }
  }]
};

重启PM2生效：pm2 reload ecosystem.config.js

5. 数据写入环节添加编码校验 #

在创建/更新数据的接口中，过滤输入的非法字符：

function sanitizeString(str: string): string {
  return str.replace(/\u00A0/g, ' ');
}

// 在创建License的方法中调用
async createLicense(dto: CreateLicenseDto) {
  dto.license_ar = sanitizeString(dto.license_ar);
  dto.license_en = sanitizeString(dto.license_en);
  // 其他文本字段同理
  return this.save(dto);
}

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