国外网站推广,博客网站建设基本流程,网站icp证明,购物网站建设渠道错误介绍
今天遇到一个错误#xff0c;记录一下
2025-02-10 17:04:35.264 HKT [28816] 错误: 编码WIN1252的字符0x0x81在编码UTF8没有相对应值
2025-02-10 17:04:35.264 HKT [28816] 错误: 编码UTF8的字符0x0xe9 0x94 0x99在编码记录一下
2025-02-10 17:04:35.264 HKT [28816] 错误: 编码WIN1252的字符0x0x81在编码UTF8没有相对应值
2025-02-10 17:04:35.264 HKT [28816] 错误: 编码UTF8的字符0x0xe9 0x94 0x99在编码WIN1252没有相对应值
2025-02-10 17:04:35.264 HKT [28816] ERROR: character with byte sequence 0xe9 0x94 0x99 in encoding UTF8 has no equivalent in encoding WIN1252背景描述
说一下背景公司有个业务场景需要通过psql.exe工具将sql文件的数据导入到PostgreSQL数据库里面操作系统是window系统数据库版本是12.3其中PostgreSQL服务端创建实例的时候声明了编码是UTF8sql文件里面也有声明编码方式是UTF8设置内容 SET client_encoding to ‘UTF8’; 在这个情况下测试修改了操作系统的时区本来时区是国内改成了英语美国后面就出现了这个问题导致一些数据导入失败因某些原因不能更改系统的配置需要在这个情况下解决问题。
在windows系统的cmd上登录数据库后有这段提示
WARNING:
Console code page (437) differs from Windows code page (1252)
8-bit characters might not work correctly. See psql referrence
page Notes for Windows users for details.这段错误的大致意思是数据库服务端代码页为437但客户端连接时实际使用的编码可能被Windows终端1252覆盖导致UTF8字符无法正确转换为WIN1252即Windows默认的ANSI编码。
解释一下为什么客户端的sql文件中设置了UTF8编码服务端也设置了UTF8为什么通过psql.exe导入数据会受到操作系统的编码影响。 原因虽然SQL文件中设置了 SET client_encoding TO UTF8但部分客户端工具如psql可能在连接建立时已根据终端环境【windows系统自己的】自动设置了编码覆盖了后续的SQL指令。
解决办法
在网上搜了很多教程都没有跟这个错误一样的或者没有提供一个解决办法无奈之下去官网查看。 PostgreSQL官网对每个工具的使用都有详细介绍其中psql的地址https://www.postgresql.org/docs/12/app-psql.html 在psql.exe工具的使用说明中看到这样一段话
Connecting to a Database
psql is a regular PostgreSQL client application. In order to connect to a database you need to know the name of your target database, the host name and port number of the server, and what user name you want to connect as. psql can be told about those parameters via command line options, namely -d, -h, -p, and -U respectively. If an argument is found that does not belong to any option it will be interpreted as the database name (or the user name, if the database name is already given). Not all of these options are required; there are useful defaults. If you omit the host name, psql will connect via a Unix-domain socket to a server on the local host, or via TCP/IP to localhost on machines that dont have Unix-domain sockets. The default port number is determined at compile time. Since the database server uses the same default, you will not have to specify the port in most cases. The default user name is your operating-system user name, as is the default database name. Note that you cannot just connect to any database under any user name. Your database administrator should have informed you about your access rights.When the defaults arent quite right, you can save yourself some typing by setting the environment variables PGDATABASE, PGHOST, PGPORT and/or PGUSER to appropriate values. (For additional environment variables, see Section 33.14.) It is also convenient to have a ~/.pgpass file to avoid regularly having to type in passwords. See Section 33.15 for more information.An alternative way to specify connection parameters is in a conninfo string or a URI, which is used instead of a database name. This mechanism give you very wide control over the connection. For example:$ psql servicemyservice sslmoderequire
$ psql postgresql://dbmaster:5433/mydb?sslmoderequire
This way you can also use LDAP for connection parameter lookup as described in Section 33.17. See Section 33.1.2 for more information on all the available connection options.If the connection could not be made for any reason (e.g., insufficient privileges, server is not running on the targeted host, etc.), psql will return an error and terminate.If both standard input and standard output are a terminal, then psql sets the client encoding to “auto”, which will detect the appropriate client encoding from the locale settings (LC_CTYPE environment variable on Unix systems). If this doesnt work out as expected, the client encoding can be overridden using the environment variable PGCLIENTENCODING.重点关注最后一句话内容是 If both standard input and standard output are a terminal, then psql sets the client encoding to “auto”, which will detect the appropriate client encoding from the locale settings (LC_CTYPE environment variable on Unix systems). If this doesn’t work out as expected, the client encoding can be overridden using the environment variable PGCLIENTENCODING. 翻译如果由于任何原因例如权限不足、服务器没有在目标主机上运行等导致连接无法建立psql将返回一个错误并且终止。 如果标准输入和标准输出都是一个终端那么psql会把客户端编码设置成“auto”这会使psql从区域设置Unix 系统上的LC_CTYPE环境变量中检测合适的客户端编码。如果这样不起作用可以使用环境变量PGCLIENTENCODING覆盖客户端编码。 这里提供了一个解决方法可以在打开window系统的cmd窗口的时候先设置一下环境变量PGCLIENTENCODING亲测有效。 设置方法
#设置cmd的编码
set PGCLIENTENCODINGUTF8
#执行其他的操作
psql -U username -d dbname -f your_file.sql 知识拓展
官方对这个还有其他的介绍【链接在前面psql.exe那】原文如下 Notes for Windows Users psql is built as a “console application”. Since the Windows console windows use a different encoding than the rest of the system, you must take special care when using 8-bit characters within psql. If psql detects a problematic console code page, it will warn you at startup. To change the console code page, two things are necessary: Set the code page by entering cmd.exe /c chcp 1252. (1252 is a code page that is appropriate for German; replace it with your value.) If you are using Cygwin, you can put this command in /etc/profile. Set the console font to Lucida Console, because the raster font does not work with the ANSI code page. 翻译一下大致意思是 给 Windows 用户的说明 psql是一个“控制台应用”。由于 Windows 的控制台窗口使用的是一种和系统中其他应用不同的编码在psql中使用 8 位字符时要特别注意。如果psql检测到一个有问题的控制台代码页它将会在启动时警告你。要更改控制台代码页有两件事是必要的 输入cmd.exe /c chcp 1252可以设置代码页1252 是适用于德语的一个代码页请在这里替换成你的值。如果正在使用 Cygwin可以把这个命令放在/etc/profile中。 把控制台字体设置为Lucida Console因为栅格字体无法与 ANSI 代码页一起使用。 这段说明后面还有示例因为时间有限并没有去一一验证感兴趣的伙伴可以去官网看看。
