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

本文涉及的产品
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
云原生数据库 PolarDB MySQL 版,通用型 2核8GB 50GB
简介: 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

(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í

参考
《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数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
6月前
|
SQL Java 数据库连接
【YashanDB知识库】解决mybatis的mapper文件sql语句结尾加分号";"报错
【YashanDB知识库】解决mybatis的mapper文件sql语句结尾加分号";"报错
|
4月前
|
存储 SQL 数据库连接
C#程序调用Sql Server存储过程异常处理:调用存储过程后不返回、不抛异常的解决方案
本文分析了C#程序操作Sql Server数据库时偶发的不返回、不抛异常问题,并提出了解决思路。首先解析了一个执行存储过程的函数`ExecuteProcedure`,其功能是调用存储过程并返回影响行数。针对代码执行被阻塞但无异常的情况,文章总结了可能原因,如死锁、无限循环或网络问题等。随后提供了多种解决方案:1) 增加日志定位问题;2) 使用异步操作提升响应性;3) 设置超时机制避免阻塞;4) 利用线程池分离主线程;5) 通过信号量同步线程;6) 监控数据库连接状态确保可用性。这些方法可有效应对数据库操作中的潜在问题,保障程序稳定性。
379 11
|
6月前
|
SQL Java 数据库连接
【YashanDB 知识库】解决 mybatis 的 mapper 文件 sql 语句结尾加分号";"报错
【YashanDB 知识库】解决 mybatis 的 mapper 文件 sql 语句结尾加分号";"报错
|
11月前
|
SQL 关系型数据库 MySQL
数据库导入SQL文件:全面解析与操作指南
在数据库管理中,将SQL文件导入数据库是一个常见且重要的操作。无论是迁移数据、恢复备份,还是测试和开发环境搭建,掌握如何正确导入SQL文件都至关重要。本文将详细介绍数据库导入SQL文件的全过程,包括准备工作、操作步骤以及常见问题解决方案,旨在为数据库管理员和开发者提供全面的操作指南。一、准备工作在导
1492 0
|
9月前
|
Oracle 安全 关系型数据库
【赵渝强老师】PostgreSQL的参数文件
PostgreSQL数据库的四个主要参数文件包括:`postgresql.conf`(主要配置文件)、`pg_hba.conf`(访问控制文件)、`pg_ident.conf`(用户映射文件)和`postgresql.auto.conf`(自动保存修改后的参数)。视频讲解和详细说明帮助理解各文件的作用。
292 19
|
9月前
|
存储 关系型数据库 数据库
【赵渝强老师】PostgreSQL的控制文件
本文介绍了PostgreSQL数据库的物理存储结构,重点解析了控制文件,包括其重要性及如何通过`pg_controldata`命令查看控制文件内容。控制文件记录了数据库运行的关键信息,如数据库状态、WAL位置等。
241 14
|
9月前
|
SQL 关系型数据库 MySQL
MySQL导入.sql文件后数据库乱码问题
本文分析了导入.sql文件后数据库备注出现乱码的原因,包括字符集不匹配、备注内容编码问题及MySQL版本或配置问题,并提供了详细的解决步骤,如检查和统一字符集设置、修改客户端连接方式、检查MySQL配置等,确保导入过程顺利。
|
9月前
|
存储 SQL 关系型数据库
【赵渝强老师】PostgreSQL的运行日志文件
PostgreSQL的物理存储结构包括数据文件、日志文件等。运行日志默认未开启,需配置`postgresql.conf`文件中的相关参数如`log_destination`、`log_directory`等,以记录数据库状态、错误信息等。示例配置中启用了CSV格式日志,便于管理和分析。通过创建表操作,可查看生成的日志文件,了解具体日志内容。
269 3
|
9月前
|
存储 关系型数据库 数据库
【赵渝强老师】PostgreSQL的数据文件
PostgreSQL的物理存储结构主要包括数据文件、日志文件等。数据文件按oid命名,超过1G时自动拆分。通过查询数据库和表的oid,可定位到具体的数据文件。例如,查询数据库oid后,再查询特定表的oid及relfilenode,即可找到该表对应的数据文件位置。
225 1
|
11月前
|
SQL 数据库
为什么 SQL 日志文件很大,我应该如何处理?
为什么 SQL 日志文件很大,我应该如何处理?

推荐镜像

更多