c#mysql出现问题:第1行列的字符串值代码不正确1366 [英] c# Having trouble with mysql : Incorrect string value code 1366 for column at row 1

问题描述

在mysql中使用中文更新列时，使用ADO.NET的C#MVC5错误(版本:5.7.27-0ubuntu0.18.04.1-(Ubuntu)): {第1列标题_1"的字符串值不正确:'\ xE4 \ xB8 \ xAD \ xE6 \ x96 \ x87'"}

ERROR C# MVC5 using ADO.NET when update columns using Chinese in mysql(version:5.7.27-0ubuntu0.18.04.1 - (Ubuntu)): {"Incorrect string value: '\xE4\xB8\xAD\xE6\x96\x87' for column 'Title_1' at row 1"}

我已经尝试过将字符集用于数据库，表和列.

I have already tried charset for database, table and columns.

(1)'utf8' 'utf8_general_ci'
(2)'utf8' 'utf8_unicode_ci'
(3)'utf8mb4' 'utf8mb4_general_ci'
(4)'utf8mb4' 'utf8mb4_unicode_ci'

此外，我在Web.config的连接字符串中添加了charset = utf8mb4或charset = utf8

Also,I've added charset=utf8mb4 or charset=utf8 in connectionstring in Web.config

我的连接字符串是

<add name="constr" connectionString="Data Source=ServerIP;port=3306;Initial Catalog=jn001;User Id=Userid;password=Password;charset=utf8mb4">

Google全天候寻找答案， 仍然找不到解决方案， 请告诉我一个解决方案，谢谢！

Google all day finding answers, Still can't find a solution, Please tell me a solution,Thanks!

这是我的MySQL字符集

我已经遵循LocEngineer的广告，并将mysql服务器设置为utf8mb4

I've followed LocEngineer's advertisement and set mysql server to utf8mb4

这是我的新MySQL字符集

这是我的整理信息

对utf8mb4的所有更改(包括连接字符串)，但仍然发生相同的错误.

All change to utf8mb4 (including connectionstring) ,but still the same error occur.

无论如何，感谢您的建议.

Anyway ,thanks for the suggestions.

------------------------------ 9/5update -------------- ------------------

------------------------------9/5update--------------------------------

我的显示创建表

 public bool UpdateDetails(Class1 content)
        {
            connection();
            MySqlCommand cmdSet = new MySqlCommand("set names utf8mb4", con);
            MySqlCommand cmd = new MySqlCommand("update", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Title_1",content.Title_1);
            cmd.Parameters.AddWithValue("@Title_2", content.Title_2);
            cmd.Parameters.AddWithValue("@Title_3", content.Title_3);
            cmd.Parameters.AddWithValue("@Content_1", content.Content_1);
            cmd.Parameters.AddWithValue("@Content_2", content.Content_2);
            cmd.Parameters.AddWithValue("@Content_3", content.Content_3);

            con.Open();
            cmdSet.ExecuteNonQuery();
            int i = cmd.ExecuteNonQuery();
            con.Close();
            if (i >= 1)
                return true;
            else
                return false;
        }

这是mysql更新过程

this is mysql update Procedure

UPDATE Stop_Info SET Title_1 = Title_1,Title_2=Title_2,Title_3=Title_3,Content_1=Content_1,Content_2=Content_2,Content_3=Content_3 WHERE Pkey = 3

HTML表单accept-charset ="utf-8"

HTML form accept-charset="utf-8"

<form id="registerForm" method="post" action="~/Home/Index" class="form-horizontal" accept-charset="utf-8">

推荐答案

\xE4\xB8\xAD\xE6\x96\x87是中文(中文")的十六进制.

\xE4\xB8\xAD\xE6\x96\x87 is hex for 中文 ("Chinese language").

在MySQL中，这将与utf8或utf8mb4一起使用.但是，由于有几个中文字符需要4个字节，因此使用utf8mb4是正确的.同时，COLLATION(例如utf8mb4_unicode_ci)与手头的问题无关.

In MySQL, this will work with either utf8 or utf8mb4. However, since there are several Chinese characters that need 4 bytes, you are correct to use utf8mb4. Meanwhile, the COLLATION (eg, utf8mb4_unicode_ci) does not matter to the Question in hand.

单个列的字符集很重要，而不是数据库的字符集很重要.并且仅在5.5和5.6中就需要191 kludge.请提供SHOW CREATE TABLE.

The individual column's charset is important, not the database's. And the 191 kludge is needed only in 5.5 and 5.6. Please provide SHOW CREATE TABLE.

建立连接参数的另一种方法是在连接后立即发出SET NAMES utf8mb4. (这不是首选，但可能值得尝试. 应该是提到的Gorm问题的解决方法.)

Another way to establish the connection parameters is to issue SET NAMES utf8mb4 immediately after connecting. (This is not preferred, but might be worth trying. It should be a workaround for the Gorm issue mentioned.)

连接时是否收到错误消息?还是在发出特定查询时?如果是这样，请提供该查询.

Are you getting the error message when connecting? Or when issuing a particular query? If so, please provide that query.

检查是否未使用skip-character-set-client-handshake.

在 https://stackoverflow.com/a/38363567/1766831 中检查最佳做法"

附录由于您提到了存储过程，因此建议您执行SHOW CREATE PROCEDURE来查看它是用什么字符集构建的.这是可能发生的情况的示例:

Addenda Since you mentioned a stored procedure, I suggest you do SHOW CREATE PROCEDURE to see what charset it was built with. Here's an example of what can happen:

mysql> SET NAMES latin1;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE PROCEDURE x () BEGIN END ;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE PROCEDURE x \G
*************************** 1. row ***************************
           Procedure: x
            sql_mode: NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `x`()
BEGIN END
character_set_client: latin1
collation_connection: latin1_swedish_ci
  Database Collation: utf8mb4_unicode_520_ci
1 row in set (0.00 sec)

mysql> DROP PROCEDURE x;
Query OK, 0 rows affected (0.01 sec)

与之相对:

mysql> SET NAMES utf8mb4;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE PROCEDURE x () BEGIN END ;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW CREATE PROCEDURE x \G
*************************** 1. row ***************************
           Procedure: x
            sql_mode: NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `x`()
BEGIN END
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
  Database Collation: utf8mb4_unicode_520_ci
1 row in set (0.00 sec)

如果在PROCEDURE和FUNCTION声明中看不到utf8mb4，请重新构建它们.

If you don't see utf8mb4 on your PROCEDURE and FUNCTION declarations, rebuild them.

这篇关于c#mysql出现问题:第1行列的字符串值代码不正确1366的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！