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

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
简介: 标签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数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
28天前
|
SQL 关系型数据库 MySQL
这样的SQL执行为什么不会报错?optimizer_trace深度历险
【10月更文挑战第12天】本文探讨了一条看似错误但实际上能成功执行的SQL语句,通过开启MySQL的优化器追踪功能,详细分析了SQL的执行过程,揭示了子查询被优化器解析为连接操作的原因,最终解释了为何该SQL不会报错。文章不仅增进了对SQL优化机制的理解,也展示了如何利用优化器追踪解决实际问题。
|
3天前
|
SQL 监控 关系型数据库
SQL语句当前及历史信息查询-performance schema的使用
本文介绍了如何使用MySQL的Performance Schema来获取SQL语句的当前和历史执行信息。Performance Schema默认在MySQL 8.0中启用,可以通过查询相关表来获取详细的SQL执行信息,包括当前执行的SQL、历史执行记录和统计汇总信息,从而快速定位和解决性能瓶颈。
|
15天前
|
SQL 存储 缓存
如何优化SQL查询性能?
【10月更文挑战第28天】如何优化SQL查询性能?
60 10
|
9天前
|
SQL 关系型数据库 MySQL
|
22天前
|
SQL 数据库 开发者
功能发布-自定义SQL查询
本期主要为大家介绍ClkLog九月上线的新功能-自定义SQL查询。
|
18天前
|
SQL 关系型数据库 MySQL
mysql编写sql脚本:要求表没有主键,但是想查询没有相同值的时候才进行插入
mysql编写sql脚本:要求表没有主键,但是想查询没有相同值的时候才进行插入
30 0
|
29天前
|
SQL 数据可视化 BI
SQL语句及查询结果解析:技巧与方法
在数据库管理和数据分析中,SQL语句扮演着至关重要的角色
|
2月前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
|
4月前
|
SQL 存储 监控
SQL Server的并行实施如何优化?
【7月更文挑战第23天】SQL Server的并行实施如何优化?
111 13
|
4月前
|
SQL
解锁 SQL Server 2022的时间序列数据功能
【7月更文挑战第14天】要解锁SQL Server 2022的时间序列数据功能,可使用`generate_series`函数生成整数序列,例如:`SELECT value FROM generate_series(1, 10)。此外,`date_bucket`函数能按指定间隔(如周)对日期时间值分组,这些工具结合窗口函数和其他时间日期函数,能高效处理和分析时间序列数据。更多信息请参考官方文档和技术资料。

相关产品

  • 云原生数据库 PolarDB
  • 云数据库 RDS PostgreSQL 版