c# - 不正确的字符串值 : '\xEF\xBF\xBD' for column

Question

我有一张表需要处理各种字符。字符包括Ø、®等。

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

我的连接字符串定义为

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

我不知道为什么我仍然看到错误。我是否遗漏了 .net 连接器或我的 MySQL 设置的任何内容？

--编辑--

我的(新)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();

假设这是使用参数化语句的正确方法，它仍然报错

 "Incorrect string value: '\xEF\xBF\xBD' for column 'buyerName' at row 1"

还有其他想法吗？

Answer 1

\xEF\xBF\xBD 是 unicode 字符 U+FFFD 的 UTF-8 编码。这是一个特殊字符，也称为“替换字符”。引自 the wikipedia page about the special unicode characters :

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:

看来您的数据源包含损坏的数据。也有可能您尝试使用错误的编码来读取数据。线条从何而来？

如果您无法修复数据，并且您的输入确实包含无效字符，您可以删除替换字符:

lines[n] = lines[n].Replace("\xFFFD", "");