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;
            }
        }

 

一定要注意上面的紅色字型。