字符串值错误:列的'\ xEF \ xBF \ xBD'

问题描述

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

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:

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

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'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！