[問題] MySQL 內 latin1 編碼儲存的 utf8 編碼資料轉 utf8 編碼儲存

文章由 **IOann** » 2006-12-25 08:08

●架設主機作業系統：Windows Xp Professioanl SP2
●您的上網方式：HiNet ADSL, 8m/640k with Dynamic IP
●您安裝的程式：Apache 2.2.3、MySQL 5.0.27、php 5.2.0、phpMyAdmin 2.9.1.1
●您的 phpBB2 版本：phpBB 2.0.21。
●您的 domain(網域名稱) ：metalwow.homeip.net
●您的 phpBB2 連結網址： http://metalwow.homeip.net/forum/
●錯誤代碼：

代碼: 選擇全部

MySQL 傳回：
#1406 - Data too long for column 'word_text' at row 4

不知道有沒有發錯版，網路、討論區上的相關文章我閱讀了很多，如果已經有人問過這個問題，請提點一下。感恩！

我是初學者。
我也許連整個架構的意義都搞錯。
只要能達成目的，任何方法我都願意嘗試。
以下有任何錯誤，煩請指正。

主機：
IBM PC
Windows XP SP2
Apache 2.2.3
MySQL 5.0.27
PHP 5.2.0
phpMyAdmin 2.9.1.1
phpBB 2.0.21

根據我從網路上文章學到的知識，得到以下的了解 (我是初學者，有錯煩請指正)
Apache 是用來把 PC 變成一台 Server (使這台電腦能夠被別人連進來？)
MySQL 是資料庫，功能是把討論區裡的參數、文章、其他資料等有條理的保存起來
PHP 是程式語言 (功能是 libraries ？)
phpMyAdmin 是用 php 寫出來方便使用者管理 MySQL 的 GUI 介面程式

我的目的：
我自己已經架好存在自己電腦中的討論區，移動到另一台電腦上。

目前所使用的手段： (請問我這樣備份，有不完全的地方嗎？)
0. 在另一台電腦上安裝相同版本的 Apache、MySQL、PHP、phpMyAdmin
1. 完整複製存在硬碟中的的論壇目錄到另一台電腦
2. 備份資料庫 (備份出來的 .sql 檔不到 2MB)
3. 還原資料庫

錯誤 code：
備份資料庫時，發現資料庫中 search_wordlish 與 serch_wordmatch 兩個(表格?)特肥大
在還原時，發現這兩個 (單位是表格嗎？) 沒辦法順利匯入。
phpMyAdmin 上所顯示的 error code 是：
MySQL 傳回：
#1406 - Data too long for column 'word_text' at row 4
就字面上的意思看來，是表示這個表格裡 word_text 這欄的第 4 列資料太長？
請問有沒有辦法解決？

我查過網路上的資料，似乎 UTF-8 與 ANSI 間的轉換會有這種問題。
但備份時匯出的的資料應該是 UTF-8 沒錯，還原時也選擇了 UTF-8 編碼。
討論區在 MySQL 中總共有 38 項 (表格?)，只有這兩個沒辦法正確還原。
只還原剩餘的 36 個項目後，討論區可以順利登入，但無法發表、搜尋文章。

其他問題：
所謂還原 .sql 時，檔案大小必需限制 2MB 以下，是 MySQL 本身的限制嗎？
還是 php 程式語言本身的限制？
或者是 Apache 的限制？
還是 phpMyAdmin 的限制？
請問為什麼要做這麼限制？
這個限制，可以藉由調整參數而加大所限制的檔案大小嗎？
如果是 MySQL 的限制，請問是為了跟 MySQL 的商業版本做功能上的區隔嗎？

以上諸多問題，是目前自學中碰到的，沒有老師可以問，希望強者能教教我 Orz

附加檔案：
wow1.sql 是那兩個特別肥大的表格，*****SPAM*****.sql 是其餘的。
http://sm917.myweb.hinet.net/forum/*****SPAM*****.7z

文章由 **IOann** » 2006-12-25 09:24

data too long 的問題已經解決了！真的是編碼的問題。

把 search_wordlish 和 serch_wordmatch 兩個資料表中 latin1 的字樣換成 utf8 後，就可以正確匯入了。

現在又多出了幾個其他的問題：

我 phpBB 用 UTF-8 的版本，為什麼建立一資料表裡寫的會是 latin1 呢？
其他的資料表裡也是寫 latin1，為什麼確能正確匯入呢？ (改成 utf8 反而不能正確匯入)
正體中文的編碼，不是 UTF-8 就該是 Big5，為什麼會跑出 latin1 (拉丁語系？) 來呢？
我討論區的資料庫現在除了 search_wordlish 和 serch_wordmatch 兩個資料表是 UTF-8 編碼外，其餘全都是 latin1 編碼嗎？
如果是，為什麼會造成這麼情況呢？我該怎麼把他們轉成 UTF-8 編碼呢？

文章由 **IOann** » 2006-12-25 10:38

剛才又發現了似乎是 %programfiles%\mysql\MySQL Server 5.0\my.ini 內沒有設定好。
要設「default-character-set=utf8」才行。

但還是不知道怎麼把現在 latin1 編碼的資料表轉成 UTF-8。
還有第一篇的問題還是找不到。

文章由 **chiouss** » 2006-12-25 12:56

IOann 寫:但還是不知道怎麼把現在 latin1 編碼的資料表轉成 UTF-8。

ConvertZ or iconv...

所謂還原 .sql 時，檔案大小必需限制 2MB 以下，是 MySQL 本身的限制嗎？

請找 PHP 的 post_max_size...

--
以上都是老問題了...zzz...

文章由 **IOann** » 2006-12-27 14:50

因為我一直找不到 iconv 這個程式，ConverZ 也不支援 latin1 to UTF-8。
問了學長，他說 iconv 是 Linux 上的程式 @@"

我目前的狀況，我自己也一知半解，所以不知道該怎麼形容。

總之，我一開始用 phpMyAdmin 建立空的資料庫時，沒有指定 MySQL 編碼，所以 MySQL 用預設編碼 latin1 來儲存資料。
而我的 phpBB 討論區是使用 UTF-8 編碼。
現在我用 phpMyAdmin 直接檢視資料表含有中文的部份，是亂碼如：

在這個狀況下 dump 資料庫的 .sql 檔也是亂碼。

修改 phpMyAdmin\libraries\select_lang.lib.php 中的 'utf-8' => 'utf8', 為 'utf-8' => 'latin1', 後，可以正常顯示中文，dump 出來的 .sql 檔也是中文，但不知道這樣做會有什麼其他的 bug，所以沒有嘗試。而在這種狀況下 dump 出來的 .sql 檔匯入資料庫後，用 phpMyAdmin 在 'utf-8' => 'utf8' 狀況下，可以正常檢視中文，但討論沒有辦法正確顯示中文，會變成一堆問號。

因為資料庫裡的東西蠻重要的，所以沒有辦法全部重頭來，一定要用轉碼的方式來達到 MySQL 改用 UTF-8 編碼儲存資料的目的。
目前還找不到方法，所以還在使用 latin1 編碼儲存。

希望高手能教教我 @@"

文章由 **chiouss** » 2006-12-27 18:25

如果你確定你是 big5 被當成 latin1 轉 utf8，請照下面第一篇的方法用 mysqldump 把資料挖出來再進行轉碼。沒辦法用 mysqldump 的話請參考第二篇設定 phpMyAdmin 的 AllowAnywhereRecoding。這件事情我已經在這個站上說過十多次了，早就已經是 FAQ 的東西了...

http://chiouss0817.wordpress.com/2006/0 ... -to-utf-8/
http://chiouss0817.wordpress.com/2006/0 ... dmin-tips/

文章由 **IOann** » 2007-01-12 21:01

http://phpbb-tw.net/phpbb/viewtopic.php?t=45784

我的狀況跟這篇一樣。我一直找不到解決的辦法，他好像還沒有找到。
不知道有沒有人也有過同樣狀況並已解決呢？

文章由 **chiouss** » 2007-01-12 21:23

IOann 寫:http://phpbb-tw.net/phpbb/viewtopic.php?t=45784

我的狀況跟這篇一樣。我一直找不到解決的辦法，他好像還沒有找到。
不知道有沒有人也有過同樣狀況並已解決呢？

你只要沒有和第一個人一樣把資料庫的資料弄爛應該都還有辦法...

文章由 **IOann** » 2007-01-13 01:31

因為我看 phpBB 預設是用 latin1_swedish_ci 存取 mysql database，所以用 mysqldump 加上 default-character-set=latin1 參數匯出資料，並試著再匯回 database 發現能正確備份。之後，有把最原始的資料表刪光，再用這第二手的備份資料還原資料表。應該是沒有把資料庫弄爛吧？

不過，我找還是找不到方法轉換編碼。有在這裡 http://www.iconv.com/iconv.htm 試過 latin1 > utf8 、 big > utf8 ，結果皆失敗。

這是我匯出的資料表: http://sm917.myweb.hinet.net/temp/confuse.sql (請抓下來看，直接點讀取時間頗長)

文章由 **chiouss** » 2007-01-13 01:55

IOann 寫:這是我匯出的資料表: http://sm917.myweb.hinet.net/temp/confuse.sql (請抓下來看，直接點讀取時間頗長)

這 SQL 已經是 UTF8 了，不需要再轉過...

文章由 **IOann** » 2007-01-14 00:36

但我用 phpMyAdmin 開啟這個 database 來看，卻是用 latin1_swedish_ci 校對。而且在 phpMyAdmin 裡檢視和 mysqldump 後都是亂碼。此外在 phpBB 裡無法使用某些特定的中文字註冊，如：專砍負心人；重新建立一個 phpBB 的討論區，並在 db.php 內加入指定使用 utf-8 存取資料庫的 code 後，這幾個字是可以註冊的。推測問題還是出在編碼上。

文章由 **chiouss** » 2007-01-14 01:15

你需要的是：

在 SQL 最前面加上
代碼: 選擇全部
```
SET NAMES utf8;
```
並且把所有的 latin1 取代成 utf8 之後匯入
在 phpBB 的 php 檔案加入
代碼: 選擇全部
```
@mysql_query('SET NAMES utf8;',$this->db_connect_id),
```
，我個人習慣加在 mysql.php 裡面

你的問題不能拿之前的範例來照抄是因為你用的就是 UTF8 版的 phpBB，而不像之前那些人用的是 Big5 版。

文章由 **IOann** » 2007-01-17 00:15

非常感謝樓上先進！
參考他說的方法，再試了一次，終於成功了。

留下我的操作方法給未來遇到這個問題的網友參考：

Step 0:
我的資料庫是 MySQL 5.0
phpMyAdmin 2.9.1
phpBB 2.0.21
php 5.2.0
Windows XP
Apache 2.2.3

Step 1:
進入命令提示字元，用 MySQL\bin\MySQLdump.exe 指令匯出資料庫。

代碼: 選擇全部

mysqldump -u 資料庫使用者名稱 -p 欲匯出的資料庫名稱 > database_latin1.sql --default-character-set=latin1

(這個指令都在同一行)

Step 2:
用專業的文字編輯器打開剛才匯出的 database_latin1.sql，
1. 在檔案最前面加入 SET NAMES utf8;
2. 將 /*!40101 SET NAMES latin1 */; 改為 /*!40101 SET NAMES utf8 */;
3. 將所有 ENGINE=InnoDB DEFAULT CHARSET=latin1; 改為 ENGINE=InnoDB DEFAULT CHARSET=utf8;
4. 另存新檔為 database_utf8.sql

Step 3:
用 phpMyAdmin 重新匯入 database_utf8.sql 到新的資料庫，
匯入時，如果在 search_wordlist 資料表出現資料太長無法匯入的錯誤，則重新編輯 database_utf8.sql，將 search_wordlist 和 search_wordmatch 兩個資料表的內容刪空 (注意，不要將整個資料表刪掉了)，另存新檔後再重新匯入。

Step 4:
編輯 phpBB\config.php，將 $dbname = '資料庫名稱'; 內的資料庫名稱設成 step 3 內新創的資料庫名稱。

Step 5:
用 phpMyadmin 開啟新資料庫，檢視資料表內的中文是否正確顯示。
如果正確顯示，則用瀏覽器開啟 phpBB 討論區，檢視中文是否正確顯示。
如果沒有正確顯示，則編輯 phpBB\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;");

Step 6:
重新用瀏覽器開啟 phpBB 討論區，檢視中文是否正確顯示。

以上是我的做法，終於成功使得 phpMyAdmin 能正確顯示資料表內的中文。
但轉了之後才發現，其實有無轉碼，差別只在 phpMyAdmin 內能否正確顯示中文資料，對 phpBB 本身存取資料庫並無影響；因為我的 phpBB 本來就是 UTF-8 版本，資料庫內顯示為亂碼，只是因為 phpBB 不是用 UTF-8 來存取 UTF-8 資料。所以其實是非必要的一項工作。
我本來以為，某些中文，如「專砍負心人」無法作為註冊 ID 是因為編碼沒有處理好，想不到轉了碼之後還是不能用，看來這是 phpBB 本身的限制。

文章由 **心靈捕手** » 2007-01-17 06:42

IOann 寫:用 phpMyAdmin 重新匯入 database_utf8.sql 到新的資料庫，

恭喜您! 終於解決了問題.
匯入前, 建立 '新的資料庫' 是重點.
http://phpbb-tw.net/phpbb/viewtopic.php?p=245172#245172

IOann 寫: 我本來以為，某些中文，如「專砍負心人」無法作為註冊 ID 是因為編碼沒有處理好，想不到轉了碼之後還是不能用，看來這是 phpBB 本身的限制。

參考以下的修改:
http://phpbb-tw.net/phpbb/viewtopic.php?p=226438#226438