[問題] BIG5(ANSI or Latin1)資料庫轉換成 UTF-8 的問題

[DearHoney]

我的 MySQL 是 4.1 版，我現在想要把整討個論區的資料予以 UTF-8 化，phpBB 本身的程式轉換是 ok 的，我在公司已經有實戰經驗，但我自己的網站討論區在 mysqldump 出來後的資料約為 182MB，用 notepad、EmEditor、UltraEdit 都有難以編輯的問題，速度很慢很慢，只要稍微增刪修就要等個十幾秒以後才有反應。(P4 3.0G with 1GB memory)

因為討論區運作多年，各式各樣的留言都有，原本以 BIG5 運作，我也不知道為什麼沒有"許功蓋"這類的問題，現在看了 dump 出來的東西發現都已經有自動加上 "\" 來解決 BIG5 編碼時的問題。

然則這樣的 dump 檔，利用 ConvertZ 直接轉換成 UTF-8 後，那些原本為了解決「許功蓋」等的"\"就變成多餘的，而且嚴重干擾 SQL 語法，造成我無法 restore 回資料庫。

然後我自己發現，dump 出來的檔案，不要轉換成 UTF-8，保持原本 BIG5(ANSI) 的狀態，在前方加上：

代碼: 選擇全部

SET NAMES utf8; 
SET CHARACTER_SET_CLIENT=big5; 
SET CHARACTER_SET_RESULTS=utf8; 

還有每一個 CREATE TABLE 語句的結尾修改為：

代碼: 選擇全部

ENGINE=MyISAM DEFAULT CHARSET=utf8

就可以減少很多的錯誤，還原較大比例的資料回資料庫（代價是原本的許會變成"許\\"，但我可以接受），而且進入資料庫時是 UTF-8，然而，還是有些許問題，我想要手動修正這些問題，仔細一看 dump 檔的結構，發現在 insert 句子這裡，mysqldump 出來的檔案是這樣的結構：

代碼: 選擇全部

INSERT INTO `banlist` VALUES (1,12192,'',NULL),(2,25735,'',NULL);

如果只有兩筆也就算了，問題是我討論區有上萬筆的紀錄，都被他寫成一句 insert，造成我 debug 的超大障礙。反觀 phpmyadmin 的輸出功能，其做出的 sql 語法就是這樣的：

代碼: 選擇全部

INSERT INTO `banlist` VALUES (1,12192,'',NULL)
INSERT INTO `banlist` VALUES (2,25735,'',NULL)

這樣子至少很好 debug 啊～錯到哪裡可以告訴我正確的行數，我還比較好找問題。要怎麼樣才可以讓 mysqldump 出來的格式與 phpmyadmin 做出來的 insert 一樣呢？

拉拉雜雜講了一堆，也許我根本走偏了，是否有有經驗的人，轉換過像我這種超過 150MB 的資料庫到 UTF-8 的？

[flower]

您可以試著先把 wordlist 和 wordmatch 兩個資料表清空(保留資料表結構)
這兩個最少佔了一半的容量.......之後剩下最大的就是 posts_text
以我的例子 光 posts_text 就有 6x Mb 如果真的無法編輯
可以利用 phpMyAdmin 只輸出這個資料表(可以分段輸出) 之後再將許功蓋結尾的 \' 再一一修正

看您 posts_text 的容量決定切割成幾段處理 最後再一起輸進資料庫就可以了

我之前就是用這方法將資料庫由 big5 轉成 utf8

[brentsu]

請問 phpMyAdmin 可以匯入、匯出超過 150MB 的大型資料庫嗎？我剛接觸 MySQL 沒機會測試....

[flower]

我試過 130 Mb 可以正常匯出..
但我還是習慣使用 mysqldump 指令....只有在必須分段輸出修改時 才用 phpMyAdmin ...

根據伺服器端服務的連線時間設定 會影響您下載的大小.....這點也必須留意

[DearHoney]

但是我的問題是，因為我的資料庫版本已經到 4.1 版，phpmyadmin 匯出的檔案，似乎強迫 UTF-8，造成裡面的 SQL 語法正確，但是資料部分全部變成亂碼。我必須使用 mysqldump 時設定 default charset 為 Latin1 才會輸出正確的檔案，但這時候的 INSERT 語法就有我一開始所講的問題，全部幾成同一行，難以 debug......

[flower]

我是用 mysql 4.1.15....
mysqld 預設是 latin1 所以使用 mysqldump 也要加入 --default-character-set=latin1 輸入的資料才會是原來的編碼
我的 phpbb2 的資料庫 語言設定也是 utf8 連線校正是選 utf8_general_ci

我的情況跟您應該差不多.......

剛用 phpMyAdmin 2.6.4-pl4 試了一下 輸出 topics 和 posts_text 兩個資料表\r
打開看也沒有破壞編碼 還是 utf8 ........

所以並無問題 還是你要更新 phpMyAdmin 試試

[DearHoney]

我的 phpmyadmin 沒問題，但是沒辦法用 phpmyadmin 輸出本質上是 ANSI/BIG5 的資料，因資料庫已經變為 4.1 版，內部改為 UTF-8 儲存了。

所以我只能用 mysqldump --default-character-set=latin1 來輸出，但這時候 INSERT 塞成一行，難以 debug.....

[stevenbb]

我的 phpmyadmin 沒問題，但是沒辦法用 phpmyadmin 輸出本質上是 ANSI/BIG5 的資料，因資料庫已經變為 4.1 版，內部改為 UTF-8 儲存了。

所以我只能用 mysqldump --default-character-set=latin1 來輸出，但這時候 INSERT 塞成一行，難以 debug.....

去抓 2.5.7 版的，應該可以正確看到文字

我上次做了一次相同的轉換
步驟是從 2.5.7 各別資料表輸出，將資料存入文字檔
處理跳脫字元 \ 和 ?, 問題，將 ?, 修改成 ?', 或 ',
我不確定產生的原因，但有些欄位的資料後面就變成 ?, 所以匯入時在切割欄位資料上就出現問題
處理完後，就將文字檔丟給 coverterZ
最後將內容從新版的 phpmyadmin 中匯入\r
重複所有的資料表都轉換完成

[DearHoney]

我的問題最後是自己解決了.....

phpmyadmin 就是會輸出 UTF-8，造成亂碼，所以我還是用 mysqldump 加上 --default-character-set=latin1 的參數，這樣可以輸出正常的內容。

但這樣輸出後，insert 全塞成一行，終於我在國外的網站找到解答，再加上 --skip-opt 參數，就可以變成一行一行輸出的 insert 語句。

至此，這份 dump 出來的檔案，是 BIG5/ANSI 編碼，且 INSERT 句子是我要的一行一行的。

然後，在 dump 檔前方，加上：

SET NAMES utf8;
SET CHARACTER_SET_CLIENT=big5;
SET CHARACTER_SET_RESULTS=utf8;

每一個 CREATE TABLE 的後方結尾處改為：

ENGINE=MyISAM DEFAULT CHARSET=utf8

接著利用命令列，mysql < xxx.sql 的方式來 import，用這個方法的好處是，只要其中的 SQL 有錯誤，他會清楚的指出錯誤的行數，再去編輯即可。如果是在 mysql 內使用 source 指令，則只會嗶嗶嗶的叫，並且一直往下執行，搞不清楚錯在哪裡。

就這樣，花個幾個小時，我成功轉換了。現在，我用 phpmyadmin 配合 UTF-8 語系，而且 MySQL 是 4.1 版，可以看到討論區在資料庫內的內容了。

不過，回頭我開始用 phpBB 套上 UTF-8 的 Language 時，從資料庫出來的全部變成亂碼了。最後我發現，我要自行修改 phpBB 程式，找個地方先執行一下 SET NAMES UTF8 的 SQL 指令，才能將資料庫內的資料以 UTF-8 的方式存取。

然後我赫然發現，使用 mysqldump 搭配 --skip-opt 參數後，CREATE TABLE 的指令少了 auto_increment 的設定，這蠻嚴重的，所以我還要回頭對照原使的 phpBB 資料庫，把每一個需要 auto_increment 的 field 給 alter 回來。

MySQL 在 4.0 的人就不用管 SET NAMES UTF8 這個部分了，都是因為 MySQL 4.1 開始正式支援 Unicode 的關係。

[DearHoney]

有在使用 MySQL 4.1 以上，且從資料庫結構開始就是 utf8_bin，phpBB 也是轉換成 utf8 的，不妨用 phpmyadmin 看看，這時候看得到討論區的內容嗎？應該全部都是亂碼，但反反得正的結果，您的 phpBB 表現出來的是正確的編碼。

同樣的情況，我在測試 wordpress 這個 blog 系統，他一開始就是 UTF8，搭配 MySQL 4.1 也是好好的，但是我用 phpmyadmin 去看時，就是一堆的亂碼，想必也是同樣的問題。要解決，也是要把資料倒出來，然後將資料庫語系設定為 UTF8，再把資料倒回去，然後要修改 wordpress 找個一開始的地方先下一個 SET NAMES UTF8 的命令，就可以在 wordpress 以及 phpmyadmin 內都看到編碼正確的文字了。

[stevenbb]

有在使用 MySQL 4.1 以上，且從資料庫結構開始就是 utf8_bin，phpBB 也是轉換成 utf8 的，不妨用 phpmyadmin 看看，這時候看得到討論區的內容嗎？應該全部都是亂碼，但反反得正的結果，您的 phpBB 表現出來的是正確的編碼。

phpBB 顯示的結果跟 phpmyadmin 顯示的結果兩者間應該沒關係吧\r
我兩邊的顯示都正常，我ㄧ開始就是用 mysql 4.1

資料庫=latin1
mysql=latin1
phpbb=big5版本\r
不用修改 phpbb 正常，phpmyadmin 2.5.7 正常，2.6.x 以上亂碼\r

資料庫=latin1
mysql=utf8
phpbb=big5版本\r
phpbb加入 mysql_query("set names 'latin1'", $this->db_connect_id); 後正常
phpmyadmin 2.5.7 亂碼(原始文章錯誤)，2.6.x 以上亂碼\r

我另外將資料庫轉成 utf8

資料庫=utf8
mysql=latin1
phpbb=utf8版本\r
phpbb加入 mysql_query("set names 'utf8'", $this->db_connect_id); 後正常
phpmyadmin 2.5.7 亂碼，2.6.x 以上正常

資料庫=utf8
mysql=utf8
phpbb=utf8版本\r
不用修改 phpbb 正常，phpmyadmin 2.5.7 亂碼，2.6.x 以上正常

[stevenbb]

資料庫=latin1
mysql=utf8
phpbb=big5版本\r
phpbb加入 mysql_query("set names 'latin1'", $this->db_connect_id); 後正常
phpmyadmin 2.5.7 正常，2.6.x 以上亂碼

修正此處， phpmyadmin 2.5.7 應該是不正常

經過這些測試，我認為程式沒有設定 SET NAMES xxx; 時，就會依照 mysql 的設定在跑
phpmyadmin 2.5.7 沒有設定 SET NAMES，2.6.X 後的版本是有的

就這樣，花個幾個小時，我成功轉換了。現在，我用 phpmyadmin 配合 UTF-8 語系，而且 MySQL 是 4.1 版，可以看到討論區在資料庫內的內容了。

不過，回頭我開始用 phpBB 套上 UTF-8 的 Language 時，從資料庫出來的全部變成亂碼了。最後我發現，我要自行修改 phpBB 程式，找個地方先執行一下 SET NAMES UTF8 的 SQL 指令，才能將資料庫內的資料以 UTF-8 的方式存取。

因此，會造成你這樣的問題應該是在於你 mysql 的設定不是 utf8
所以 phpbb 沒有設定 SET NAMES 就會跟著 mysql 的設定
而 2.6.x 後的版本是有設定 SET NAMES 的

另外你提到\r

有在使用 MySQL 4.1 以上，且從資料庫結構開始就是 utf8_bin，phpBB 也是轉換成 utf8 的，不妨用 phpmyadmin 看看，這時候看得到討論區的內容嗎？應該全部都是亂碼，但反反得正的結果，您的 phpBB 表現出來的是正確的編碼。

我做了一個測試

mysql 的 windows 安裝版本預設是 latin1

而我猜竹貓的 2.0.18 utf8版本實際上也只有改變語系檔為 utf8
install 目錄下的 mysql_schema.sql 內容並沒有指定資料表是 utf8
db 目錄下的 mysql4.php 內容也沒有 SET NAMES utf8 字串\r

mysql 安裝使用預設值，然後用竹貓的 2.0.18 版本安裝
使用這種組合的人應該很多吧\r

這樣的組合當使用者在安裝 phpbb 時，透過 phpmyadmin 新增資料庫，即使連線部分設定 utf8
安裝後，資料表上面的確都是顯示 utf8

結果就是，在討論區看到的文字都是正常的，可是在 phpmyadmin 2.6.x 後的版本卻都是亂碼\r
另外一點是 phpmyadmin 2.5.7 的版本雖然是亂碼，但只要點右鍵將編碼改成 utf8 版面雖然跑掉了，不過內容卻不是亂碼了

事實上似乎是將 utf8 的文字以 latin1 的格式存了起來，下面的測試讓我看起來很擔心

我將此資料庫下三種不同參數備份\r

mysqldump -u root -p temp > temp1.sql
資料表最後面是 DEFAULT CHARSET=utf8
文字內容是亂碼的

mysqldump --default-character-set=utf8 -u root -p temp > temp2.sql
資料表最後面是 DEFAULT CHARSET=utf8
文字內容依然是亂碼的

mysqldump --default-character-set=latin1 -u root -p temp > temp3.sql
資料表最後面是 DEFAULT CHARSET=utf8
但是，文字內容是正確的

官方並沒有 utf8 的版本，竹貓要不要確認看看 utf8 版本有沒有問題

[~倉木麻衣~]

官方並沒有 utf8 的版本，竹貓要不要確認看看 utf8 版本有沒有問題

個人是覺得還有改進的空間
昨天在自己本機上測試的時候, 有先對phpBB 2.0.18 utf8版做了些小變動
不外乎就是此篇中提到的那幾個項目\r

1. 將install/schemas/mysql_schema.sql裡的每一個CREATE TABLE最後加上ENGINE=MyISAM DEFAULT CHARSET=utf8
   例如:

   代碼: 選擇全部

   CREATE TABLE phpbb_user_group (
      group_id mediumint(8) DEFAULT '0' NOT NULL,
      user_id mediumint(8) DEFAULT '0' NOT NULL,
      user_pending tinyint(1),
      KEY group_id (group_id),
      KEY user_id (user_id)
   ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

2. 在install/install.php裡這句之前

   代碼: 選擇全部

   $sql_query = @fread(@fopen($dbms_schema, 'r'), @filesize($dbms_schema));

   加上

   代碼: 選擇全部

   				$sql_query = "ALTER DATABASE `$dbname` CHARACTER SET utf8";
   				if (!($result = $db->sql_query($sql_query)))
   				{
   					$error = $db->sql_error();
   			
   					page_header($lang['Install'], '');
   					page_error($lang['Installer_Error'], $lang['Install_db_error'] . '<br />' . $error['message']);
   					page_footer();
   					exit;
   				}

   目的是確認整個資料庫的編碼為utf8
3. 修改includes/db.php, 在 ?>之前加上

   代碼: 選擇全部

   $db->sql_query("SET NAMES utf8;");
   $db->sql_query("SET CHARACTER_SET_CLIENT=utf8;");
   $db->sql_query("SET CHARACTER_SET_RESULTS=utf8;");

在全新安裝的系統之下, 上述的執行結果正常
不論是在phpbb裡觀看或是在phMyadmin(2.6.1 rc2)裡, 中文字都很正常, 用phpMyadmin輸出的結果也正常

[依夢兒]

請問 phpMyAdmin 可以匯入、匯出超過 150MB 的大型資料庫嗎？我剛接觸 MySQL 沒機會測試....

可以試試：http://www.ozerov.de/bigdump.php。

個人之前使用它將轉碼為 UTF-8 的 .sql 檔(約 160 MB)還原回資料庫，過程順利。
這樣的 Script 對於不是將 phpBB 架設在自己電腦(例如租用虛擬主機)又沒有 SSH 操作權限的網友來說非常好用。

[chinwork]

我的問題最後是自己解決了.....

phpmyadmin 就是會輸出 UTF-8，造成亂碼，所以我還是用 mysqldump 加上 --default-character-set=latin1 的參數，這樣可以輸出正常的內容。

但這樣輸出後，insert 全塞成一行，終於我在國外的網站找到解答，再加上 --skip-opt 參數，就可以變成一行一行輸出的 insert 語句。

至此，這份 dump 出來的檔案，是 BIG5/ANSI 編碼，且 INSERT 句子是我要的一行一行的。

然後，在 dump 檔前方，加上：

SET NAMES utf8;
SET CHARACTER_SET_CLIENT=big5;
SET CHARACTER_SET_RESULTS=utf8;

每一個 CREATE TABLE 的後方結尾處改為：

ENGINE=MyISAM DEFAULT CHARSET=utf8

接著利用命令列，mysql < xxx.sql 的方式來 import，用這個方法的好處是，只要其中的 SQL 有錯誤，他會清楚的指出錯誤的行數，再去編輯即可。如果是在 mysql 內使用 source 指令，則只會嗶嗶嗶的叫，並且一直往下執行，搞不清楚錯在哪裡。

就這樣，花個幾個小時，我成功轉換了。現在，我用 phpmyadmin 配合 UTF-8 語系，而且 MySQL 是 4.1 版，可以看到討論區在資料庫內的內容了。

不過，回頭我開始用 phpBB 套上 UTF-8 的 Language 時，從資料庫出來的全部變成亂碼了。最後我發現，我要自行修改 phpBB 程式，找個地方先執行一下 SET NAMES UTF8 的 SQL 指令，才能將資料庫內的資料以 UTF-8 的方式存取。

然後我赫然發現，使用 mysqldump 搭配 --skip-opt 參數後，CREATE TABLE 的指令少了 auto_increment 的設定，這蠻嚴重的，所以我還要回頭對照原使的 phpBB 資料庫，把每一個需要 auto_increment 的 field 給 alter 回來。

MySQL 在 4.0 的人就不用管 SET NAMES UTF8 這個部分了，都是因為 MySQL 4.1 開始正式支援 Unicode 的關係。

我參照您說的步驟將資料轉入資料庫，目前PHPBB的編碼是utf8再執行，
PHPBB 的部分文字可以正常顯示，但資料庫的文字都顯示亂碼，
我用 phpMyAdmin - 2.8.2 看文字是正常的，
看到資料庫型態為 MySQL 文字編碼: UTF-8 Unicode (utf8)
您有提到這個 『SET NAMES UTF8 』，請問我要加在PHPBB程式哪裡呢？？
請指教。

我爬文爬到了，去改了include/db.php
就可以了～