How to Convert Character Set and Collation of WordPress Database如何轉換字符集和校對WordPress的數據庫

Since自從 WordPress 2.2在WordPress 2.2 , WordPress supports feature that allows the user to define both the ， WordPress所支持的功能，使用戶可以界定雙方 WordPress database character set and collation在WordPress數據庫字符集和校對 with DB_CHARSET and DB_COLLATE values in wp-config.php file.與db_charset和db_collate價值觀在可濕性粉劑- config.php文件。 With these values defined, WordPress will use the designated database characterset (charset) and database collation (ie sort order of the letters, numbers, and symbols of a character set) when connecting a database tables.與這些價值觀的界定，在WordPress將使用指定的數據庫字符集（字符集）和數據庫的整理（即排序順序的字母，數字和符號的字符集）時，連接數據庫表。

However, in existing WordPress installations that are upgraded from earlier version of WordPress or not explicitly set a Unicode UTF-8 charset collation, the default database character set is normally set up as Latin1 (default on almost all MySQL installation) with latin1_swedish_ci collation.然而，在現有的WordPress安裝升級，從較早版本的WordPress的或沒有明確訂定的Unicode UTF - 8字符集整理，默認的數據庫字符集通常是成立以latin1 （默認對幾乎所有的MySQL安裝）與latin1_swedish_ci整理。 If you run a bilingual or multilingual blog with WordPress, you may face problem on character encoding when your blog posts are written in other foreign languages, or when you export and backup the database and later attempt to re-import the database dump in the event of database failure or server migration and moving.如果您運行的雙語或多語的博客的WordPress ，您可能會遇到的問題，對漢字編碼時，您的博客帖子是寫的其他外語，或當您的出口和備份數據庫，並稍後嘗試重新導入數據庫轉儲在事件數據庫的失敗或服務器遷移和感動。 The symptom is obvious, your WordPress posts or pages will contains garbled, weird and funny characters, sometime just lots of ?????症狀是顯而易見的，您的WordPress職位或網頁將包含亂碼，怪異的和奇怪的字，有時只是很多????? (question marks), rendering the WordPress database with your hard work useless and output unreadable. （問號） ，使在WordPress數據庫與您的辛勤工作和無用的輸出不可讀。 (May cause also by （可能會導致也由 wrong charset collation錯誤的字符集整理 ) ）

The best solution to the character encoding problem in WordPress is to convert the charset or database and collation to UTF-8 or Unicode.最好的辦法來解決漢字編碼問題在WordPress是要轉換字符集或數據庫和整理，以-8或Unicode 。 However, you CANNOT simply connect to MySQL via shell or phpMyAdmin and hoping all your scripts will convert nicely.但是，您不能簡單地連接到MySQL通過殼或phpmyadmin ，並希望您所有的腳本將轉換結果皆大歡喜。 As explain by作為解釋，由 WordPress database conversion guide在WordPress數據庫轉換指南 , convert character sets requires using the the MySQL ALTER TABLE command. ，轉換字符集，需要使用MySQL的改變表的命令。 When converting the character sets, all TEXT (and similar) fields are converted to UTF-8, but that conversion will BREAK existing TEXT because the conversion expects the data to be in latin1, but WordPress may have stored unicode characters in a latin1 database, and as a result, data could end up as garbage after a conversion!當轉換為字符集，所有的文本內容（和類似的）領域轉換為-8 ，但轉換將打破現有的文字，因為轉換，預計數據將在以latin1 ，但在WordPress可能已儲存的Unicode字符在一個以latin1數據庫，作為一個結果，數據最終可能作為垃圾後，轉換！

The guide provides a very rough and vague guide as a solution on how to actually convert WordPress MySQL database tables from one character set to another, usually UTF-8.本指南提供了一個非常粗略的和模糊的指導，作為一種解決辦法就如何轉換，其實在WordPress MySQL數據庫表從一字符集，以另一種，通常-8 。 However, the guide actually works, although the process can be lengthy.不過，指南，其實工程，雖然過程中可以長時間。 To convert, the steps involved generally are to alter each and every TEXT and related fields inside every WP tables to BLOB, then alter the character set of database and finally change the BLOB fields back to TEXT.轉換，所涉的步驟一般是改變每個文本及相關領域內的每一個可濕性粉劑表的BLOB ，然後改變字符集的數據庫和最後改變的BLOB字段返回正文。 Looks easy, but how long it would take to convert so many fields on so many tables?看起來容易，但需時多久轉換，使許多領域上有這麼多表？ Furthermore, you will also need to remember the original type and length or values of all fields.此外，您還需要記住原來的類型和長度或價值的各個領域。

andersapt has posted a conversion script named已發布的轉換腳本命名 convert_to_utf8_sql_generator.txt which automatically generates a list of SQL statements and commands need to fully convert your WordPress database to UTF8 based on the guide.其中，自動生成清單， SQL語句和命令，要充分轉換為您的WordPress數據庫utf8進行的基礎上，引導。 However, there seems to be a minor bug with the script, although the author claimed it worked, where in my case, it simply won’t generate the list of SQL commands to run due to the error “PHP Fatal error: Call to a member function get_results() on a non-object in convert.php on line 37″.不過，似乎有一種輕微的錯誤與腳本，雖然作者聲稱，它的工作，凡在我而言，它根本不會產生名單， SQL命令執行，由於錯誤的“ PHP的致命錯誤：電話一成員函數get_results （ ）對一個非對象在convert.php上線37 “ 。 Once fixed, with this script in hand we can easily and quickly convert the database, tables and fields to use utf8_general_ci collation.一旦固定，與此腳本在手，我們可以方便快捷地轉換為數據庫，表和字段使用utf8_general_ci整理。

Note: I have tried out注：我已嘗試了 UTF-8 Database Converter plugin -8數據庫轉換插件 , but it’sa failure. ，但它是失敗的。 It seemed like the author change to character set directly.它好像作者改變字符集，直接。

Guide to Convert WordPress Database Character Set to UTF8 (Unicode) 指南轉換的WordPress數據庫字符集，以utf8進行（的Unicode ）

1. Take the WordPress blog offline by placing a out-of-service or maintenance notice.採取WordPress所博客離線放置一個地地道道的服務或維修，恕不另行通知。
2. Backup database - this is very important, nothing is guaranteed to work.備份數據庫-這是非常重要的，沒有什麼是有保障的工作。 If you’re using cPanel or other control panel, it’s best to perform a database backup from the control panel itself, where you can restore the database in one piece instead of by SQL statements, in the case of normal dump.如果您使用的cPanel或其他控制面板，它的最佳執行數據庫備份，從控制面板本身，在那裡您可以還原數據庫在一條不是由SQL語句，在該案件的正常轉儲。
3. Download the fixed下載定額 convert_to_utf8_sql_generator.txt script and save it with a PHP extension.腳本並保存它與一個PHP擴展。
4. Modify the script to input the database name your WordPress blog is using.修改腳本來輸入數據庫的名字你的WordPress所博客是使用。 Locate the following text:找到下列文字：

   Tables_in_ DATABASENAME tables_in_ databasename

   The DATABASENAME in red is the only thing that you need to change to match your WordPress database name.該databasename在紅色是唯一的一件事，你需要變化，以符合您的WordPress數據庫名稱。 It should looks like this after change, for instance,它應該看起來像這樣後的變化，舉例來說，

   Tables_in_wp_mydigitallife tables_in_wp_mydigitallife

5. Upload the convert_to_utf8_sql_generator.php (or you can rename to a shorter name such as convert.php) to the base root WordPress installation directory, where wp-config.php is also located.上傳convert_to_utf8_sql_generator.php （或者您可以重命名為較短的名稱，如convert.php ）向基地根的WordPress安裝目錄，其中可濕性粉劑- config.php也是位於。
6. Now, call and browse the script from any web browser.現在，請瀏覽該腳本從任何Web瀏覽器。 To do this, simply add convert_to_utf8_sql_generator.php (or any name you give to the script) to the end of your blog URL (ie http://www.mywebsite.com/convert_to_utf8_sql_generator.php) and press Enter.為此，只需添加convert_to_utf8_sql_generator.php （或以任何名義，你給腳本）到去年底，您的博客網址（即http://www.mywebsite.com/convert_to_utf8_sql_generator.php ） ，然後按下ENTER 。 A long list of SQL statements will be generated on the web page.一長列的SQL語句會產生該網頁。
7. Ensure that your post_content and post title fields on wp_posts table DOES NOT belongs to any indexes or FULLTEXT indexes.確保您的post_content和職銜領域的wp_posts表不屬於任何指標或全文索引。 Else the type of the fields may not be converted to BLOB with one of the errors list below.其他類型的領域可能不會被轉換為BLOB的其中一個錯誤下面的列表中。 Some plugins, such as related posts tend to add indexes to these fields.一些插件，如相關的職位，往往添加指標，這些領域。 In this case, temporarily drop the indexes.在這種情況下，暫時放棄指標。

   ERROR 1170 (42000): BLOB/TEXT column ‘post_content’ used in key specification without a key length錯誤1170 （ 42000 ） ： BLOB的/純文字欄' post_content '用在關鍵的規格沒有一個密鑰長度

   ERROR 1283 (HY000): Column ‘post_content’ cannot be part of FULLTEXT index錯誤1283 （ hy000 ） ：欄' post_content '不能的一部分，全文索引

8. Login to your server shell by Telnet or SSH.登錄到您的服務器殼的Telnet或SSH 。 You can skip this part of using Unix shell if you intend to use phpMyAdmin to do the dirty work, but I have not tried it.你可以跳過這部分的使用Unix殼，如果您打算使用phpmyadmin做骯髒的工作，但我並沒有嘗試它。 So if you do, do feedback on whether it can be done.因此，如果你這樣做，這樣做的反饋意見，是否可以做的。
9. Connect to MySQL server from the shell.連接到MySQL服務器從殼。
10. Issue the following command first in MySQL prompt:發出下列命令首先在MySQL提示：

    use DATABASENAME ;使用databasename ;

    Again, replace DATABASENAME in red to the actual WordPress database name.再次，取代databasename在紅色，以實際的WordPress數據庫名稱。

11. Then copy and paste the whole list of SQL statements auto generated by the conversion script, and paste them into the MySQL prompt.然後複製並粘貼整個名單的SQL語句，汽車所產生的轉換腳本，並粘貼到MySQL的提示。 Each and every SQL command should now be processed and executed by MySQL one by one.每一個SQL命令，現在應該處理和執行由MySQL了一個又一個。 You may need to press Enter key to finish off the last one.您可能需要按下ENTER的關鍵，完成關閉最後一個。
12. During the processing, the similar error messages related to key length as mentioned may appears.加工過程中，類似的錯誤訊息相關的密鑰長度提到的可能出現。 In my case, the conversion to BLOB failed with such message in the following fields:在我的情況下，轉換為BLOB的失敗，這類信息在以下領域：

    wp_categories.category_nicename
    wp_comments.comment_approved
    wp_links.link_visible
    wp_options.option_name
    wp_postmeta.meta_key
    wp_posts.post_status
    wp_posts.post_name
    wp_posts.post_type
    wp_usermeta.meta_key
    wp_users.user_login

    All these fields are unlikely to contains non-ASCII characters.所有這些領域是不可能包含非ASCII字符。 And fields such as category_nicename (category slug) and post_name (post slug) have been URL encoded (where your URL with unsafe non-alphanumeric characters will be replaced with a percent (%) sign followed by two hex digits and spaces encoded as plus (+) signs).和等領域的category_nicename （第1類塞）和post_name （後段塞）已URL編碼（如您的網址與不安全的非字母數字字符將代之以一個百分號（ ％ ）簽署其次是由兩個十六進制數字和位編碼為加號（ + ）標誌） 。 Initial encoding of byte codes and character assignments for UTF-8 is consistent with ASCII, so direct conversion of these fields to UTF8 should not bring too much problem.初步的編碼字節碼和性格任務-8是一致的ASCII ，所以直接轉換這些領域的utf8進行，應該不會帶來太大的問題。

13. Edit the wp-config.php file to add in DB_CHARSET and DB_COLLATE definitions.編輯可濕性粉劑- config.php文件添加在db_charset和db_collate的定義。 Add the following two lines, preferably under the section of MySQL Settings:添加以下兩行，最好是根據第MySQL的設置：

    define(’DB_CHARSET’, ‘utf8′);界定（ ' db_charset ' ， ' utf8進行' ） ;
    define(’DB_COLLATE’, ”);界定（ ' db_collate ' ， “ ） ;

    As explained in作為解釋，在 WordPress Codex食品法典委員會的WordPress , DB_COLLATE is left blank (null) so that the database collation will be automatically assigned by MySQL based on the database character set. ， db_collate是左空（ NULL ） ，使該數據庫的整理會自動指定由MySQL的基礎上，數據庫字符集。

14. Recreate the indexes and/or FULLTEXT indexes been dropped, if any.再現指標和/或全文指標已下降，如有的話。
15. Activate the blog back into production mode.激活博客返回到生產模式。
16. Check your blog to see if everything and every characters is okay.檢查您的博客看到，如果一切和每一個字是好的。
17. Delete the PHP script.刪除PHP腳本。

IMPORTANT : This is a machine translated page which is provided "as is" without warranty. 重要說明 ：這是一個機器翻譯網頁是“按原樣”提供的擔保。 Machine translation may be difficult to understand.機器翻譯可能很難理解。 Please refer to請參閱 original English article英文原版的文章 whenever possible.只要有可能。

Share and contribute or get technical support and help at分享和貢獻，或取得技術的支持和幫助，在 My Digital Life Forums 我的數字生活論壇 . 。

Related Articles相關文章

• WordPress Charset Encoding Problem After Upgrading to Version 2.2在WordPress字符的編碼問題後，升級到2.2版
• How to Backup and Restore (Export and Import) MySQL Databases Tutorial如何備份和恢復（出口和進口）的MySQL數據庫補習
• IMP-00016 Required Character Set Conversion Not Supported Error when Import to Oracle Database進出口- 00016所需的字符集轉換不支持時出現錯誤，導入到Oracle數據庫
• Oracle EXP-00091 Error When Export Database甲骨文進出口- 00091時發生錯誤，進出口數據庫
• WordPress 2.2 Released for Free Download在WordPress 2.2發布免費下載
• Disable and Turn Off Post Revisions Tracking in WordPress 2.6 or Above禁用和關閉後的修改，跟踪在WordPress 2.6或以上
• Check and Optimize MySQL Database Automatically with Crontab/Cron檢查和優化MySQL數據庫自動與crontab /玉米
• How to Customize, Modify or Change WordPress Database Connection Error Page如何自訂，修改或改變的WordPress數據庫連接錯誤頁
• Download WordPress 2.3 Release Candidate 1 (RC1) with Tags Support下載的WordPress 2.3候選版本1 （ RC1的）與標籤支持
• How to Move WordPress Blog to New Domain or Location如何移動的WordPress所博客新的網域或位置

This entry was posted on Saturday, June 23rd, 2007 at 2:59 am and is filed under 此項目被張貼在週六， 2007年6月23日在上午02時59分，並提交下 Internet Services 上網服務 . 。 You can follow any responses to this entry through the 您可以按照任何的反應，此項目通過 RSS 2.0 2.0 feed. 餵養。 You can 您可以 leave a response 留下的回應 , or ，或 trackback Trackback跟踪 from your own site. 從你自己的網站。