PostgreSQL sql文件编码引起的数据导入乱码或查询字符集异常报错(invalid byte sequence)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介: 标签PostgreSQL , 乱码 , 文件编码背景当用户客户端字符集与服务端字符集不匹配时,写入的多字节字符(例如中文)可能出现乱码。例子数据库字符集为sql_ascii,允许存储任意编码字符。

标签

PostgreSQL , 乱码 , 文件编码


背景

当用户客户端字符集与服务端字符集不匹配时,写入的多字节字符(例如中文)可能出现乱码。

例子

数据库字符集为sql_ascii,允许存储任意编码字符。

digoal@pg11-320tb-zfs-> psql  
psql (11beta4)  
Type "help" for help.  
  
postgres=# \l+  
                                                                 List of databases  
   Name    |  Owner   | Encoding  | Collate |   Ctype    |   Access privileges   |  Size  | Tablespace |                Description                   
-----------+----------+-----------+---------+------------+-----------------------+--------+------------+--------------------------------------------  
 postgres  | postgres | SQL_ASCII | C       | en_US.utf8 |                       | 140 TB | pg_default | default administrative connection database  
 template0 | postgres | SQL_ASCII | C       | en_US.utf8 | =c/postgres          +| 15 MB  | pg_default | unmodifiable empty database  
           |          |           |         |            | postgres=CTc/postgres |        |            |   
 template1 | postgres | SQL_ASCII | C       | en_US.utf8 | =c/postgres          +| 15 MB  | pg_default | default template for new databases  
           |          |           |         |            | postgres=CTc/postgres |        |            |   
(3 rows)  

客户端为utf8编码

digoal@pg11-320tb-zfs-> echo $LANG  
en_US.utf8  

编辑一个文件,以UTF8编码

vi test.sql  
  
insert into tbl values (1, '你好');  

内容如下

digoal@pg11-320tb-zfs-> cat test.sql  
insert into tbl values (1, '你好');  

编码如下

digoal@pg11-320tb-zfs-> file test.sql  
test.sql: UTF-8 Unicode text  

转换为GBK,写入数据库

digoal@pg11-320tb-zfs-> iconv --help  
Usage: iconv [OPTION...] [FILE...]  
Convert encoding of given files from one encoding to another.  
  
 Input/Output format specification:  
  -f, --from-code=NAME       encoding of original text  
  -t, --to-code=NAME         encoding for output  
  
 Information:  
  -l, --list                 list all known coded character sets  
  
 Output control:  
  -c                         omit invalid characters from output  
  -o, --output=FILE          output file  
  -s, --silent               suppress warnings  
      --verbose              print progress information  
  
  -?, --help                 Give this help list  
      --usage                Give a short usage message  
  -V, --version              Print program version  
  
Mandatory or optional arguments to long options are also mandatory or optional  
for any corresponding short options.  
  
For bug reporting instructions, please see:  
<http://www.gnu.org/software/libc/bugs.html>.  
  
  
  
  
digoal@pg11-320tb-zfs-> iconv -f UTF8 -t GBK test.sql|psql -f -  
INSERT 0 1  

或者这样写入(client_encoding和server都设置为sql_ascii时,不检查编码合法性,直接存入数据库)

digoal@pg11-320tb-zfs-> iconv -f UTF8 -t GBK test.sql -o test.sql.gbk  
  
digoal@pg11-320tb-zfs-> psql  
psql (11beta4)  
Type "help" for help.  
  
postgres=# set client_encoding =sql_ascii;  
SET  
postgres=# \i ./test.sql.gbk  
INSERT 0 1  

当设置客户端client_encoding为utf8编码时,由于存入的数据编码不合法,导致查询异常

digoal@pg11-320tb-zfs-> psql  
psql (11beta4)  
Type "help" for help.  
  
  
postgres=# set client_encoding =utf8;  
SET  
postgres=# select * from tbl;  
ERROR:  invalid byte sequence for encoding "UTF8": 0xc4 0xe3  

当client_encoding设置为GBK编码,查询为乱码

postgres=# set client_encoding =gbk;  
SET  
postgres=# select * from tbl;  
 id | info   
----+------  
  1 | ?oí  
          (1 row)  

参考

《PostgreSQL 多字节字符集合法性检测》

《[转] SqlServe到PG迁移错误:无效的编码序列"UTF8": 0x00》

《PostgreSQL UTF8 和 GB18030编码map文件不完整的问题》

《PostgreSQL WHY ERROR: invalid byte sequence for encoding "UTF8"》

《PostgreSQL SQL_ASCII encoding introduce》

《PostgreSQL Server Encoding sql_ascii attention》

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
10天前
|
SQL 人工智能 关系型数据库
PostgreSQL 常用SQL(持续更新...)
PostgreSQL 常用SQL(持续更新...)
|
10天前
|
存储 SQL 关系型数据库
关系型数据库强大的查询功能
【5月更文挑战第9天】关系型数据库强大的查询功能
14 3
|
10天前
|
缓存 关系型数据库 数据库
关系型数据库高效查询和统计
【5月更文挑战第8天】关系型数据库高效查询和统计
51 7
|
1天前
|
SQL 监控 关系型数据库
【PolarDB开源】PolarDB SQL优化实践:提升查询效率与资源利用
【5月更文挑战第24天】PolarDB是高性能的云原生数据库,强调SQL查询优化以提升性能。本文分享了其SQL优化策略,包括查询分析、索引优化、查询重写、批量操作和并行查询,以及性能监控与调优方法。通过这些措施,可以减少响应时间、提高并发处理能力和降低成本。文中还提供了相关示例代码,展示如何分析查询和创建索引,帮助用户实现更高效的数据库管理。
10 1
|
2天前
|
关系型数据库 分布式数据库 数据库
PolarDB-X助攻《香肠派对》百亿好友关系实现毫秒级查询
云原生数据库PolarDB分布式版(PolarDB for Xscale,简称PolarDB-X)有极强的线性扩展能力,能够多写多读;它的全局索引能力,是分布式改造的利器,成功解决了传统分布式方案中多维度查询的难题,在《香肠派对》的好友系统上,实现了百亿好友关系20万QPS的毫秒级查询。
PolarDB-X助攻《香肠派对》百亿好友关系实现毫秒级查询
|
2天前
|
SQL 关系型数据库 数据库
实时计算 Flink版操作报错之使用SQL 将 PostgreSQL 的 date 类型字段转换为 TIMESTAMP 类型时遇到报错,该如何处理
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
8天前
|
SQL Oracle 关系型数据库
实时计算 Flink版产品使用合集之从MySQL到Flink 1.16.2 Flink-SQL的数据同步工作出现了一个异常如何解决
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
27 0
|
9天前
|
存储 SQL 关系型数据库
关系型数据库结构化数据存储查询方式
【5月更文挑战第10天】关系型数据库结构化数据存储查询方式
77 2
|
10天前
|
SQL 关系型数据库 数据管理
Microsoft SQL Server 是微软公司开发的一款关系型数据库管理系统
【5月更文挑战第14天】Microsoft SQL Server 是微软公司开发的一款关系型数据库管理系统
19 2
|
10天前
|
存储 Cloud Native 关系型数据库
PolarDB-X 是面向超高并发、海量存储和复杂查询场景设计的云原生分布式数据库系统
【5月更文挑战第14天】PolarDB-X 是面向超高并发、海量存储和复杂查询场景设计的云原生分布式数据库系统
119 2

相关产品

  • 云原生数据库 PolarDB