字符串值错误:列的'\ xEF \ xBF \ xBD' [英] Incorrect string value: '\xEF\xBF\xBD' for column

问题描述

我有一张桌子需要处理各种字符.字符包括Ø，®等.

I have a table I need to handle various characters. The characters include Ø, ® etc.

我已将表设置为utf-8作为默认排序规则，所有列均使用表默认值，但是当我尝试插入这些字符时，我得到了错误:字符串值不正确:'\ xEF \ xBF \ xBD'第1行的BuyerName"

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

我的连接字符串定义为

string mySqlConn = "server="+server+";user="+username+";database="+database+";port="+port+";password="+password+";charset=utf8;";

为什么我仍然看到错误，我感到茫然. .net连接器或MySQL安装程序是否遗漏了任何内容?

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?

-编辑-

我的(新)C#插入语句如下:

My (new) C# insert statement looks like:

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"

还有其他想法吗?

推荐答案

\xEF\xBF\xBD是Unicode字符U+FFFD的UTF-8编码.这是一个特殊字符，也称为替换字符".引用有关特殊Unicode字符的Wikipedia页面:

\xEF\xBF\xBD is 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:

替换字符 (通常是带有白色问号的黑色菱形)是在Unicode标准中的特殊表中的代码点U + FFFD处找到的符号.当系统无法将数据流解码为正确的符号时，它用于指示问题.最常见的情况是字体不包含字符，但是当数据无效且不匹配任何字符时也会显示:

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:

因此，看来您的数据源包含损坏的数据.您也可能尝试使用错误的编码来读取数据.线是从哪里来的?

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", "");

这篇关于字符串值错误:列的'\ xEF \ xBF \ xBD'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！