MySQL 數據庫字符集轉換及版本升級/降級的詳細教程
本文為穆亦風原創，原帖地址 http://club.muzone.cn/viewthread.php?tid=28605
轉貼請注明出處，非常感謝！
最近discuz發布了新的版本，免費了，用的人更多了，以前使用其它論壇程序和discuz2.5/3.0的紛紛轉換或升級到discuz4.0，可見discuz作為中國人開發的PHP論壇程序，確實是非常優秀的，在大家欣喜若狂的時候，也遇到了一些問題
看到不少用戶反映轉換完以後是亂碼的情況,出現這種現象的主要原因是這類用戶使用的都是MySQL4.1以上的版本.下面作一個說明,希望出現這個問題的朋友都能耐心的把這個文檔看完!!!
MySQL 4.1開始，對多語言的支持有了很大變化 (這導致了問題的出現)。盡管大部分的地方 (包括個人使用和主機提供商)，MySQL 3、4.0 仍然占主導地位；但 MySQL 4.1 乃至5.0是 MySQL 官方推薦的數據庫，已經有主機提供商開始提供並將會越來越多；因為 latin1 在許多地方 (下邊會詳細描述具體是哪些地方) 作為默認的字符集，成功的蒙蔽了許多 PHP 程序的開發者和用戶，掩蓋了在中文等語言環境下會出現的問題。
MySQL 4.1開始把多國語言字符集分的更加詳細，所以導致數據庫遷移，或則dz論壇升級到4.0後（dz4.0開始使用gbk或utf-8編碼）出現亂碼問題。
MySQL 4.1的字符集支持(Character Set Support)有兩個方面：字符集(Character set)和排序方式(Collation)。對於字符集的支持細化到四個層次: 服務器(server)，數據庫(database)，數據表(table)和連接(connection)。
查看系統的字符集和排序方式的設定可以通過下面的兩條命令：
QUOTE:
MySQL> SHOW VARIABLES LIKE 'character_set_%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_clIEnt | latin1 |
| character_set_connection | latin1 |
| character_set_database | latin1 |
| character_set_results | latin1 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/MySQL/charsets/ |
+--------------------------+----------------------------+
7 rows in set (0.00 sec)
MySQL> SHOW VARIABLES LIKE 'collation_%';
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)
MySQL 4.1 對於字符集的指定可以細化到一台機器上安裝的 MySQL，其中的一個數據庫，其中的一張表，其中的一欄，應該用什麼字符集。但是，傳統的 Web 程序在創建數據庫和數據表時並沒有使用那麼復雜的配置，它們用的是默認的配置，那麼，默認的配置從何而來呢？
編譯 MySQL 時，指定了一個默認的字符集，這個字符集是 latin1；
安裝 MySQL 時，可以在配置文件 (my.ini) 中指定一個默認的的字符集，如果沒指定，這個值繼承自編譯時指定的；
啟動 MySQLd 時，可以在命令行參數中指定一個默認的的字符集，如果沒指定，這個值繼承自配置文件中的；
此時 character_set_server 被設定為這個默認的字符集；
當創建一個新的數據庫時，除非明確指定，這個數據庫的字符集被缺省設定為 character_set_server；
當選定了一個數據庫時，character_set_database 被設定為這個數據庫默認的字符集；
在這個數據庫裡創建一張表時，表默認的字符集被設定為 character_set_database，也就是這個數據庫默認的字符集；
當在表內設置一欄時，除非明確指定，否則此欄缺省的字符集就是表默認的字符集；
這個字符集就是數據庫中實際存儲數據采用的字符集，MySQLdump 出來的內容就是這個字符集下的；
當我們按照原來的方式通過PHP存取MySQL數據庫時，就算設置了表的默認字符集為utf8並且通過UTF-8編碼發送查詢，你會發現存入數據庫的仍然是亂碼。問題就出在這個connection連接層上。
想要進行“正確”的存儲和得到“正確”的結果，最方便的是在所有query開始之前執行一下：
SET NAMES 'gbk';
其中gbk是數據庫字符集。
它相當於下面的三句指令：
SET character_set_clIEnt = gbk;
SET character_set_results = gbk;
SET character_set_connection = gbk;
4.1和5.0默認使用的是latin1字符集(木頭:媽的,老外真霸道,妄想讓全世界都是使用瑞典字符集嗎)
如果我們只想使用gbk字符集存儲和獲取數據,
我們在編譯MySQL 4.1和 5.0的時候，需要注意在my.ini或者my.cnf中添加兩處參數
[Copy to clipboard] [ - ]
CODE:
[MySQLd]
default-character-set=utf8
[Copy to clipboard] [ - ]
CODE:
#settings for clients (connection, results, clIEnts)
[MySQL]
default-character-set=utf8
下面我們來說主題，如何轉換數據庫字符集
兩種方法，
QUOTE:
第一種----更改存儲字符集
主要的思想就是把數據庫的字符集有latin1改為gbk，big5,或者utf8; 以下操作必須擁有主機權限。假設當前操作的數據庫名為：database
導出
首先需要把數據導為MySQL4.0的格式，具體的命令如下：
MySQLdump -uroot -p --default-character-set=latin1 --set-charset=gbk --skip-opt databse > d4.sql
--default-characte-set 以前數據庫的字符集，這個一般情況下都是latin1的，
--set-charset 導出的數據的字符集，這個可以設置為gbk，utf8,或者big5
導入
首先使用下面語句新建一個GBK字符集的數據庫（test）
CREATE DATABASE `d4` DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
然後把剛才導出的數據導入到當前的數據庫中就ok了。
MySQL -uroot -p --default-character-set=gbk -f d4<d4.sql
通過以上的導出和導入就把數據庫的字符集改為正確的存儲方式了。
其中d4為新建庫的名稱，d4.sql為導出文件的名字
但是這種方法，發現數據庫數據存儲量無端變大30%，真是郁悶
QUOTE:
另外一種其實原理相同，但是需要手動操作，一般用於第一種方法失敗後的選擇
不過這種方法如果數據庫很大，估計很難做，因為光打開文件就能讓你死機
首先還是用PHPmyadmin或者用MySQL本身的dump導出 .sql文件
然後用UltraEdit打開你備份的所有xxxx.sql文件，查找
[Copy to clipboard] [ - ]
CODE:
DEFAULT CHARSET=latin1
latin1這裡也許是別的，反正是你不想要的，要轉成gbk或者big5的字符集
把這個替換為“空”
在查找
[Copy to clipboard] [ - ]
CODE:
CREATE TABLE cdb_sessions (
sid char(6) character set latin1 collate latin1_bin NOT NULL default '',
ip1 tinyint(3) unsigned NOT NULL default '0',
ip2 tinyint(3) unsigned NOT NULL default '0',
ip3 tinyint(3) unsigned NOT NULL default '0',
ip4 tinyint(3) unsigned NOT NULL default '0',
uid mediumint(8) unsigned NOT NULL default '0',
username char(15) NOT NULL default '',
groupid smallint(6) unsigned NOT NULL default '0',
styleid smallint(6) unsigned NOT NULL default '0',
invisible tinyint(1) NOT NULL default '0',
`action` tinyint(1) unsigned NOT NULL default '0',
lastactivity int(10) unsigned NOT NULL default '0',
fid smallint(6) unsigned NOT NULL default '0',
tid mediumint(8) unsigned NOT NULL default '0',
nickname char(15) NOT NULL default '',
UNIQUE KEY sid (sid)
) ENGINE=HEAP MAX_ROWS=1000;
替換為
[Copy to clipboard] [ - ]
CODE:
CREATE TABLE `cdb_sessions` (
`sid` char(6) binary NOT NULL default '',
`ip1` tinyint(3) unsigned NOT NULL default '0',
`ip2` tinyint(3) unsigned NOT NULL default '0',
`ip3` tinyint(3) unsigned NOT NULL default '0',
`ip4` tinyint(3) unsigned NOT NULL default '0',
`uid` mediumint(8) unsigned NOT NULL default '0',
`username` char(15) NOT NULL default '',
`groupid` smallint(6) unsigned NOT NULL default '0',
`styleid` smallint(6) unsigned NOT NULL default '0',
`invisible` tinyint(1) NOT NULL default '0',
`action` tinyint(1) unsigned NOT NULL default '0',
`lastactivity` int(10) unsigned NOT NULL default '0',
`fid` smallint(6) unsigned NOT NULL default '0',
`tid` mediumint(8) unsigned NOT NULL default '0',
`nickname` char(15) NOT NULL default '',
UNIQUE KEY `sid` (`sid`)
) TYPE=HEAP MAX_ROWS=2000;
這一步更為簡單的辦法就是刪除掉關於cdb_sessions表的這一段，將來全新裝一個d4，將這個表導出
將其內容復制，粘貼到 sql文件的最後面
保存後，再把這個sql文件導入到你的庫中
就OK了
用這兩種方法就可以很方便的把4.1和5.0的MySQL數據庫降級到4.0
簡單的過程就是
A導出4.1/5.0的庫
B進行處理，轉換成gbk字符集
C徹底卸載4.1或者5.0
D安裝4.0.26
E然後導入處理完的庫
降級的時候導出庫可以用這個方法
mysqldump -uroot -p --default-character-set=latin1 --set-charset=gbk --skip-opt databse --compatible=MySQL40 > d4.sql
這樣導出的就是4.0的庫勒
至於MySQL版本的升級，
如果數據文件中有中文信息，那麼將MySQL 4.0的數據文件，直接拷貝到MySQL 4.1中就是不可以的，即便在my.ini中設置了default-character-set為正確的字符集。雖然貌似沒有問題，但MySQL 4.1的字符集有一處非常惱人的地方，以gbk為例，原本MySQL 4.0數據中varchar,char等長度都會變為原來的一半，這樣存儲中文容量不變，而英文的存儲容量就少了一半。這是直接拷貝數據文件帶來的最大問題。
所以，升級的根本，如果想使用“正確”的字符集，還是先用MySQLdump導出成文件，然後導入。
