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#代码如下:- /// <summary>
- /// 执行查询语句,返回DataSet
- /// </summary>
- /// <param name="SQLString">查询语句</param>
- /// <returns>DataSet</returns>
- 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;
- }
- }
-
复制代码 一定要注意上面的红色字体。
|