c# - 22021 : invalid byte sequence for encoding "UTF8": 0x00

Question

我正在从 C# 批量导入 PostgreSQL，其中一条记录给我这个错误:

22021: invalid byte sequence for encoding "UTF8": 0x00

我用谷歌搜索了一下，一般的建议是这是指一个空字段，但在我的例子中情况并非如此。我找到了导致错误的字符串，它是这样的:

Addresses the following: Let $A$ be a Banach algebra, and let $\sum:\0\rightarrow I\rightarrow\mathfrak A\overset\pi\to\longrightarrow A\rightarrow 0$ be an extension of $A$, where $\mathfrak A$ is a Banach algebra and $I$ is a closed ideal in $\mathfrak A$.

我正在从一个 XML 文件中读取它，并在文件流中定义了 UTF-8。

我反序列化的 C# 类中的转义字符串是:

"Addresses the following: Let $A$ be a Banach algebra, and let $\\sum\\:\\0\\rightarrow I\\rightarrow\\mathfrak A\\overset\\pi\\to\\longrightarrow A\\rightarrow 0$ be an extension of $A$, where $\\mathfrak A$ is a Banach algebra and $I$ is a closed ideal in $\\mathfrak A$."

显然字符串有问题。我猜应该有某种数学符号，但究竟是什么破坏了导入并使 PostgreSQL 报告它是一个空字段？应该以什么格式阅读？

如果我手动覆盖此字段，则导入工作正常，因此此字符串 100% 有问题。

Answer 1

由于是批量导入，我假设您正在创建一个文件或某种大字符串以发送到 Postgres？在那种情况下，字符串可能启用了转义字符，而不是通过准备好的语句执行它。因此，Postgres 正在转义并解释为 0x00 的可能是字符串中的\0。

来自文档:https://www.postgresql.org/docs/8.3/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS

PostgreSQL also accepts "escape" string constants, which are an extension to the SQL standard. An escape string constant is specified by writing the letter E (upper or lower case) just before the opening single quote, e.g. E'foo'. (When continuing an escape string constant across lines, write E only before the first opening quote.) Within an escape string, a backslash character () begins a C-like backslash escape sequence, in which the combination of backslash and following character(s) represents a special byte value. \b is a backspace, \f is a form feed, \n is a newline, \r is a carriage return, \t is a tab. Also supported are \digits, where digits represents an octal byte value, and \xhexdigits, where hexdigits represents a hexadecimal byte value. (It is your responsibility that the byte sequences you create are valid characters in the server character set encoding.) Any other character following a backslash is taken literally. Thus, to include a backslash character, write two backslashes (\). Also, a single quote can be included in an escape string by writing \', in addition to the normal way of ''.

因此，如果您的批量语句在字符串前加上 E，例如 E'hello'，请不要这样做。