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

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介: 标签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数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
4天前
|
SQL
sql语句加正则 简化查询
sql语句加正则 简化查询
10 0
sql语句加正则 简化查询
|
22天前
|
SQL
sql server链接查询
sql server链接查询
15 1
|
22天前
|
SQL
sql server简单查询
sql server简单查询
12 1
|
23天前
|
关系型数据库 分布式数据库 数据库
PolarDB常见问题之加了索引但是查询没有使用如何解决
PolarDB是阿里云推出的下一代关系型数据库,具有高性能、高可用性和弹性伸缩能力,适用于大规模数据处理场景。本汇总囊括了PolarDB使用中用户可能遭遇的一系列常见问题及解答,旨在为数据库管理员和开发者提供全面的问题指导,确保数据库平稳运行和优化使用体验。
|
12天前
|
SQL 关系型数据库 MySQL
mysql一条sql查询出多个统计结果
mysql一条sql查询出多个统计结果
12 0
|
23天前
|
SQL
sql高级查询
sql高级查询
12 0
|
30天前
|
SQL 存储 数据可视化
10个高级的 SQL 查询技巧
10个高级的 SQL 查询技巧
|
24天前
|
SQL 数据库
sql server高级查询,看这篇文章就够了
sql server高级查询,看这篇文章就够了
18 0
|
27天前
|
SQL
T-SQL 语句查询
T-SQL 语句查询
55 0
|
27天前
|
SQL 存储 关系型数据库
sql数据库查询语句大全
sql数据库查询语句大全

相关产品

  • 云原生数据库 PolarDB