从节点到 Postgres DB 的查询不是 UTF8 [英] Queries from Node to Postgres DB not in UTF8

问题描述

对于一个项目，我必须在 JS(Node)中编写一个服务器，它从 Windows 计算机上的数据库(Postgres)返回元素.

For a project, I do have to code a server in JS (Node) which return elements from a database (Postgres) on a Windows computer.

一切都工作正常"，连接正常并且元素返回，但是，我确实有编码问题.我的元素是法语，因此，服务器无法正确看到"某些字母，例如éàç"(我打印了结果).控制台正在使用 UTF8(我测试过)，所以问题出在其他地方.

Everything is "working fine" in a way that connections are OK and elements are returned, however, I do have an encoding issue. My elements are in French, hence, some letters, such as "éàç", are not correctly "seen" by the server (I printed the result). The console is working with UTF8 (I tested it) so the problem is elsewhere.

在我看来，这个问题来自对数据库的查询(来自客户端的 HTML 和 JS 都可以，并且支持这些字母).这是我返回错误内容的代码的一部分.

It seems to me that this issue comes from the query to the database (HTML and JS from the client side are OK and support these letters). Here is a part of my code which returns the wrong things.

var client = new pg.Client({
user: 'xx',
password: 'yy', 
database: 'phrases',
host: 'localhost', 
client_encoding: 'utf8',
port: '5432'
});
client.connect();

client.query('SELECT id,mots from phrase WHERE langue=$1 ORDER BY anno limit 20;', ['fr'])
.then(
  function(data){
    text = data.rows[0].mots; //<= return the text needed
    console.log(text);
  }
);

我几乎可以肯定问题不是来自这部分代码，而是来自数据库方面，但是我可能错了(因此我不明白为什么).从 PSQL 控制台，我确实有:

I'm almost sure the problem is not from this part of code but from the database side however I may be wrong (and thus I can't see why). From the PSQL console, I do have:

show CLIENT_ENCODING; > UTF8
show SERVER_ENCODING; > UTF8

来自数据库:

    Nom    | PropriÚtaire | Encodage |      Collationnement       |    Type caract.    |     Droits d'acc
-----------+-------------+----------+----------------------------+--------------------+-----------------------
phrases   | postgres    | UTF8     | French_France.1252         | French_France.1252 |

我尝试从 python 程序(向 DB 打印一个简单的请求)查看结果是否相同，它们是；字母印刷不好.因此，我确定问题来自数据库并与编码相关联，但无法找到有效的方法.

I tried from a python program (print a simple request to the DB) to see if results were the same, they are; letters aren't well printed. Hence I'm sure the problem comes from the DB and are linked with the encoding but no way to find something working.

有人有想法吗?

最好，

推荐答案

我知道这是一个旧帖子，作者可能永远不会看到这个，但我认为应该为其他有此问题的人写一个答案.

I know this is an old post and the author is probably never going to see this, but I think that an answer should be written for others with this issue.

我遇到了与您描述的问题非常相似的问题.显然，编码是正确的，但是当您执行 SELECT * FROM table 查询时，某些字母会呈现为乱码.这可能是因为 Windows 不会自动使用 UTF-8 编码.

I had a very similar problem as the one you describe. Apparently, the encoding is correct, but when you do a SELECT * FROM table query some letters are rendered as gibberish. This might be because Windows does not automatically use UTF-8 encoding.

在通过 cmd 进入您的数据库之前，您可以运行命令 chcp 65001.这会起作用，但通常被认为是低效和危险的.更好的解决方案是执行以下操作:

Before entering your database via cmd you could run the command chcp 65001. This will work but is generally considered inefficient and dangerous. A better solution would be to do the following:

1. 搜索应用运行并输入intl.cpl
2. 转到管理标签
3. 点击Change System Locale
4. 选中左下角的框.

归功于@mklement0 的这个答案

这篇关于从节点到 Postgres DB 的查询不是 UTF8的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！