C# 不正确的字符串值：列的 '\xEF\xBF\xBD'
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11159118/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Incorrect string value: '\xEF\xBF\xBD' for column
提问by Robert H
I have a table I need to handle various characters. The characters include ?, ? etc.
我有一张需要处理各种字符的表。字符包括 ?, ? 等等。
I have set my table to utf-8 as the default collation, all columns use table default, however when I try to insert these characters I get error: Incorrect string value: '\xEF\xBF\xBD' for column 'buyerName' at row 1
我已将表设置为 utf-8 作为默认排序规则，所有列都使用表默认值，但是当我尝试插入这些字符时，出现错误：字符串值不正确：'\xEF\xBF\xBD' 列 'buyerName' 在第 1 行
My connection string is defined as
我的连接字符串定义为
string mySqlConn = "server="+server+";user="+username+";database="+database+";port="+port+";password="+password+";charset=utf8;";
I am at a loss as to why I am still seeing errors. Have I missed anything with either the .net connector, or with my MySQL setup?
我不知道为什么我仍然看到错误。我是否错过了 .net 连接器或 MySQL 设置的任何内容？
--Edit--
- 编辑 -
My (new) C# insert statement looks like:
我的（新）C# 插入语句如下所示：
MySqlCommand insert = new MySqlCommand( "INSERT INTO fulfilled_Shipments_Data " +
"(amazonOrderId,merchantOrderId,shipmentId,shipmentItemId,"+
"amazonOrderItemId,merchantOrderItemId,purchaseDate,"+ ...
VALUES (@amazonOrderId,@merchantOrderId,@shipmentId,@shipmentItemId,"+
"@amazonOrderItemId,@merchantOrderItemId,@purchaseDate,"+
"paymentsDate,shipmentDate,reportingDate,buyerEmail,buyerName,"+ ...
insert.Parameters.AddWithValue("@amazonorderId",lines[0]);
insert.Parameters.AddWithValue("@merchantOrderId",lines[1]);
insert.Parameters.AddWithValue("@shipmentId",lines[2]);
insert.Parameters.AddWithValue("@shipmentItemId",lines[3]);
insert.Parameters.AddWithValue("@amazonOrderItemId",lines[4]);
insert.Parameters.AddWithValue("@merchantOrderItemId",lines[5]);
insert.Parameters.AddWithValue("@purchaseDate",lines[6]);
insert.Parameters.AddWithValue("@paymentsDate",lines[7]);
insert.ExecuteNonQuery();
Assuming that this is the correct way to use parametrized statements, it is still giving an error
假设这是使用参数化语句的正确方法，它仍然给出错误
"Incorrect string value: '\xEF\xBF\xBD' for column 'buyerName' at row 1"
Any other ideas?
还有其他想法吗？
采纳答案by Elian Ebbing
\xEF\xBF\xBDis the UTF-8 encoding for the unicode character U+FFFD. This is a special character, also known as the "Replacement character". A quote from the wikipedia page about the special unicode characters:
\xEF\xBF\xBD是 unicode 字符的 UTF-8 编码U+FFFD。这是一个特殊字符，也称为“替换字符”。维基百科页面上关于特殊 unicode 字符的引用：
The replacement character ? (often a black diamond with a white question mark) is a symbol found in the Unicode standard at codepoint U+FFFD in the Specials table. It is used to indicate problems when a system is not able to decode a stream of data to a correct symbol. It is most commonly seen when a font does not contain a character, but is also seen when the data is invalid and does not match any character:
替换字符 ? （通常是带有白色问号的黑色菱形）是 Unicode 标准中特殊表中代码点 U+FFFD 处的一个符号。当系统无法将数据流解码为正确的符号时，它用于指示问题。当字体不包含字符时最常见，但在数据无效且不匹配任何字符时也会出现：
So it looks like your data source contains corrupted data. It is also possible that you try to read the data using the wrong encoding. Where do the lines come from?
所以看起来您的数据源包含损坏的数据。您也可能尝试使用错误的编码读取数据。线条从哪里来？
If you can't fix the data, and your input indeed contains invalid characters, you could just remove the replacement characters:
如果您无法修复数据，并且您的输入确实包含无效字符，则可以删除替换字符：
lines[n] = lines[n].Replace("\xFFFD", "");
回答by mattmanser
NEVER, EVER, EVER create a SQL statement like that. That's wide-open to SQL injection.
永远，永远，永远不要创建这样的 SQL 语句。这对 SQL 注入是完全开放的。
I'm adding this as an answer as that's such a fundamental mistake that you'll probably need to rewrite much of your program.
我将此添加为答案，因为这是一个基本错误，您可能需要重写大部分程序。
That's not how you supply parameters to a SQL statement and it's not worth anyone answering your question as you should be using parametrized queries which will probably also fix your problem.
这不是您向 SQL 语句提供参数的方式，也不值得任何人回答您的问题，因为您应该使用参数化查询，这也可能会解决您的问题。
回答by Elian Ebbing
Mattmanser is right, never write a sql query by concatenating the parameters directly in the query. An example of parametrized query is:
Mattmanser 是对的，永远不要通过在查询中直接连接参数来编写 sql 查询。参数化查询的一个例子是：
string lastname = "Doe";
double height = 6.1;
DateTime date = new DateTime(1978,4,18);
var connection = new MySqlConnection(connStr);
try
{
connection.Open();
var command = new MySqlCommand(
"SELECT * FROM tblPerson WHERE LastName = @Name AND Height > @Height AND BirthDate < @BirthDate", connection);
command.Parameters.AddWithValue("@Name", lastname);
command.Parameters.AddWithValue("@Height", height);
command.Parameters.AddWithValue("@Name", birthDate);
MySqlDataReader reader = command.ExecuteReader();
...
}
finally
{
connection.Close();
}
回答by Augusto Santana
To those who have a similar problem using PHP, try the function utf8_encode($string). It just works!
对于那些在使用 PHP 时遇到类似问题的人，请尝试使用该功能utf8_encode($string)。它只是有效！
