mysql字元集調整的個人總結

(轉 http://blog.donews.com/yllr/archive/2007/01/03/1107388.aspx)

mysql4.1以後版本都支援多字元集的支援，但是安裝預設的字元集竟然是 latin1這個瑞典文，所以很多人想安裝discuz等論壇為gbk、utf-8等字元集的論壇就出現了一些問題。最近我在網上找了一些資料，加上自己又專門在一台linux伺服器上實驗了兩天的經曆，基本解決了字元集的問題，現將我的心得寫出來，供廣大網遊參考。

     一、原理篇：
           
             mysql伺服器中有六個關鍵位置使用了字元集的概念，他們是：client 、connection、database、results、server 、system。

             a、其中client是用戶端使用的字元集，相當於網頁中的字元集設定如下

<meta http-equiv="Content-Type" content="text/html; charset=utf-8">。
       b、其中的connection是串連資料庫的字元集設定類型，如果php沒有指明串連資料庫使用的字元集類型
就按照伺服器端預設的字元集設定。
       c、其中database是資料庫伺服器中某個庫使用的字元集設定，如果建庫時沒有指明，將使用伺服器安裝
時指定的字元集設定。
       d、results是資料庫給用戶端返回時使用的字元集設定，如果沒有指明，使用伺服器預設的字元集。
       e、server是伺服器安裝時指定的預設字元集設定。
       f、system是資料庫系統使用的字元集設定。
      
      system一般預設是utf-8字元集，server是最高的字元集設定，database沒有單獨設定就按照server的字元
集設定，其他都是按照server的設定設定字元集。還有，資料庫內的每個表和欄位也都有字元集的概念，一般都
是根據上一級結構決定自身的字元集，比如表就根據database庫的設定決定自己的字元集，欄位根據表來決定自己
的字元集。

   二、統一字元集的方法：（以utf-8字元集為例，因為他是目前支援文字種類最廣的字元集）

      1、徹底解決字元集的方法：

       要徹底解決字元集的方法就是讓mysql在安裝的時候就是用utf-8的字元集設定，這樣可以使上面的六個關鍵
點的編碼都為utf-8。

      a、 在windows下安裝mysql有提示可以選擇字元集，我們選擇utf-8就可以了。

      b、在linux下有三種安裝方法，第一種是rpm包安裝，這種因為我沒有使用過所以沒有發言權。

         第二種為可執行程式安裝，這種安裝因為已經被編譯成了latin1這種瑞典語的字元集，所以無法完全解決字
符集問題，這個版本我們後面會講到怎麼解決字元集問題。
         第三種為源碼自行編譯安裝，這種安裝可以在編譯時間設定字元集類型，這部分主要講這種安裝方式。

        在編譯mysql是我們可以用這樣的指令：./configure --default-character-set=utf8
這樣，在編譯的時候，就會把mysql的server項編譯成utf8的編碼，這樣這個mysql下建立的所有資料庫都將使用utf8
編碼儲存，所有有關的方面都是utf8編碼。


      2、局部堅決字元集的方法：
 
        如果遇到自己的資料庫使用的是預設安裝的latin1字元集的（很奇怪為什麼mysql要使用這麼個預設字元集）
的情況我們可以這樣來解決。

       a、預設請況下我們在mysql命令列使用status指令察看狀態，可以看到如下內容：

        mysql> status
--------------
mysql  Ver 14.7 Distrib 4.1.9, for pc-linux-gnu (i686)

Connection id:          62
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.1.11-beta-log
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /tmp/mysql.sock
Uptime:                 58 min 23 sec

Threads: 2  Questions: 1067  Slow queries: 0  Opens: 0  Flush tables: 1  Open tables: 75  Queries per second avg: 0.305
--------------

         還可以用SHOW VARIABLES LIKE 'character_set_%';指令察看內容如下：

+--------------------------+---------------------------------------------------------------------------+
| Variable_name            | Value                                                                     |
+--------------------------+---------------------------------------------------------------------------+
| character_set_client     | latin1                                                                    |
| character_set_connection | latin1                                                                    |
| character_set_database   | latin1                                                                    |
| character_set_filesystem | binary                                                                    |
| character_set_results    | latin1                                                                    |
| character_set_server     | latin1                                                                    |
| character_set_system     | utf8                                                                      |
| character_sets_dir       | /usr/local/src/mysql-5.1.11-beta-linux-i686-glibc23/share/mysql/charsets/ |
+--------------------------+---------------------------------------------------------------------------+
8 rows in set (0.00 sec)

          這就說明，除system是utf8之外的所有內容的字元集都為latin1，我們怎麼樣才能使用utf字元集呢？
有人說可以在my.cnf檔案的[mysqld]段中加入default-character-set=utf8這一項就可以解決，但經過我的實驗，這一條
完全沒有必要，以下是增加了這一項後得顯示結果。

mysql> status;
--------------
mysql  Ver 14.7 Distrib 4.1.9, for pc-linux-gnu (i686)

Connection id:          62
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.1.11-beta-log
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    latin1
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    latin1
UNIX socket:            /tmp/mysql.sock
Uptime:                 1 hour 6 min 18 sec

Threads: 2  Questions: 1071  Slow queries: 0  Opens: 0  Flush tables: 1  Open tables: 75  Queries per second avg: 0.269
--------------

mysql> SHOW VARIABLES LIKE 'character_set_%';
+--------------------------+---------------------------------------------------------------------------+
| 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/local/src/mysql-5.1.11-beta-linux-i686-glibc23/share/mysql/charsets/ |
+--------------------------+---------------------------------------------------------------------------+
8 rows in set (0.00 sec)


mysql> SHOW VARIABLES LIKE 'collation_%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | utf8_general_ci   |
| collation_server     | utf8_general_ci   |
+----------------------+-------------------+
3 rows in set (0.01 sec)


       從上面可以看出，使用了設定檔中修改預設字元集的方法，並沒有把client、connection、
results這三項改成utf8，而且在建資料庫的時候我們可以通過這樣的指令實現資料庫的字元集設定：

 CREATE DATABASE `database` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

       這樣此資料庫內的所有表和欄位都將為utf8字元集編碼，所以在設定檔中修改的方法也就失去了意義。

     
     b、我的解決辦法。

      我們可以完全無視資料庫預設的字元集是什麼，我們關心的只有資料庫在建立的時候是不是加入了字元集
選擇。

      (1)使用如下指令建立資料庫：
        
      
     CREATE DATABASE `database` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

      (2)用戶端php程式使用如下方法設定串連所使用的字元集：  
PHP程式在查詢資料庫之前，執行mysql_query("set names utf8;");

      例子：

1. <?php
2. mysql_connect('localhost','user','password');
3. mysql_select_db('my_db');
4.  
5. //請注意,這步很關鍵,如果沒有這步,所有的資料讀寫都會不正確的
6. //它的作用是設定本次資料庫聯結過程中,資料轉送的預設字元集
7. mysql_query("set names utf8;");
8.  
9. //必須將gb2312(本地編碼)轉換成utf-8,也可以使用iconv()函數
10. mysql_query(mb_convet_encoding("insert into my_table values('測試');", "utf-8", "gb2312"));
11. ?>

     (3)如果你想使用gb2312編碼,那麼建議你使用latin1作為資料表的預設字元集,這樣就能直接用中
文在命令列工具中插入資料,並且可以直接顯示出來.而不要使用gb2312或者gbk等字元集,如果擔心查詢
排序等問題,可以使用binary屬性約束,例如:
create table my_table ( name varchar(20) binary not null default '')type=myisam default charset latin1;

   自此,使用utf8字元集的完整的例子結束了。

     三、舊資料升級辦法

    (1) 匯出資料庫：

mysqldump -uroot -p123456 --default-character-set=latin1 --set-charset=utf8 --opt olddatabase > newdatabase.sql

    (2) 修改newdatabase.sql，在檔案開頭增加一條sql語句： “SET NAMES utf8;“，儲存。

    (3)mysql -hlocalhost -uroot my_db < newdatabase.sql

 

Trackback: http://tb.donews.net/TrackBack.aspx?PostId=1107388