mysql中文乱码 go_c#连接mysql中文乱码解决方案（MySql.Data.dll）

今天用C#类连接mysql数据库出现中文乱码具体解决方案如下：

用到的mysql表结构：

CREATE TABLE IF NOT EXISTS `tet` (

`id` int(11) NOT NULL,

`name` varchar(255) NOT NULL,

`url` varchar(255) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

用到的mysql表数据：

INSERT INTO `tet` (`id`, `name`, `url`) VALUES

(1, '百度 ', 'http://www.baidu.com '),

(0, 'google ', 'http://www.google.com.hk '),

(3, '400电话 ', 'http://www.my400800.cn ');

一、查询结果没有问题，在查询条件中如果输入中文查询不到内容，用insert测试为，插入的内容是乱码

首先在MySQL Command Line Client里面输入“SHOW VARIABLES LIKE 'character_set_%';”，可看到如下字符：

Variable_name

Value

character_set_client

utf8

character_set_connection

utf8

character_set_database

utf8

character_set_filesystem

binary

character_set_results

utf8

character_set_server

latin1

character_set_system

utf8

character_sets_dir

C:\Program Files\VertrigoServ\Mysql\share\charsets\

可以用如下连接字符串解决中文乱码问题

User Id=test;Host=localhost;Database=eczhou;password=test;charset='gb2312'

二、检索结果和插入内容都是乱码

用MySQL Command Line Client里面输入“SHOW VARIABLES LIKE 'character_set_%';”，可看到如下字符：

Variable_name

Value

character_set_client

utf8

character_set_connection

utf8

character_set_database

utf8

character_set_filesystem

binary

character_set_results

utf8

character_set_server

utf8

character_set_system

utf8

character_sets_dir

/usr/share/mysql/charsets/

即便是连接字符串改成

User Id=test;Host=localhost;Database=eczhou;password=test;charset=utf8 检索的结果还是乱码

郁闷中。。。。。。。

然后用  show variables like 'collation%';显示结果是：

+----------------------+-----------------+

| Variable_name        | Value           |

+----------------------+-----------------+

| collation_connection | utf8_general_ci |

| collation_database   | utf8_general_ci |

| collation_server     | utf8_general_ci |

+----------------------+-----------------+

show variables like 'character%';

+--------------------------+----------------------------+

| Variable_name            | Value                      |

+--------------------------+----------------------------+

| character_set_client     | latin1                     |

| character_set_connection | latin1                     |

| character_set_database   | utf8                       |

| character_set_filesystem | binary                     |

| character_set_results    | latin1                     |

| character_set_server     | utf8                       |

| character_set_system     | utf8                       |

| character_sets_dir       | /usr/share/mysql/charsets/ |

+--------------------------+----------------------------+

经过不懈努力的查找，终于找到问题了,问题就出在上面红色字体的编码上，我的解决方法如下：

连接字符串修改如下：

server=localhost;uid=root;pwd=root;database=test;Charset=latin1

c#代码如下：

///

/// 执行查询语句，返回DataSet

///

/// 查询语句

/// DataSet

public static DataSet Query(string SQLString)

{

using (MySqlConnection connection = new MySqlConnection(connectionString))

{

DataSet ds = new DataSet();

try

{

connection.Open();

MySqlCommand M = new MySqlCommand("set names 'latin1'", connection);

M.ExecuteNonQuery();

//

MySqlDataAdapter da = new MySqlDataAdapter(SQLString, connection);

da.Fill(ds);

}

catch (MySqlException ex)

{

connection.Close();

throw new Exception(ex.Message);

}

finally

{

connection.Close();

}

DataTable dt = ds.Tables[0];

foreach (DataRow dr in dt.Rows)

{

for (int i = 0; i < dt.Columns.Count; i++)

{

dr[i] = Encoding.UTF8.GetString(Encoding.GetEncoding("latin1").GetBytes(dr[i].ToString()));

}

}

return ds;

}

}

一定要注意上面的红色字体。