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. 从你自己的网站。