WordPress字符集从latin1转为UTF8

By using WordPress Plugin “bbWP2UTF8”, you can convert the character sets of your WP database from latin1 to UTF8, see more in this site.

MySQL数据库默认字符集一般是latin1格式的，如果没有特别注意的话，在以前的中文网站中就会留下一些历史遗留问题，比如GB2312、GBK或者UTF8类型的中文网站内容都会默认被当作latin1格式存储在数据库中。我的博客也是如此，WordPress的UTF8格式的字符都被以latin1_swedish_ci存储，为了迎接即将到来的WP3.0时代，我心里默念着如何将我的博客数据库转换到utf8_general_ci格式存储。

看了老韩的经验总结，觉得自己写转换代码实现起来还是有点困难。还是考虑看看有没有合适的WP插件可以用吧。还真找到了两个：UTF-8 Database Converter和Convert WP Database to UTF-8。两者我都实际测试了一下（注意一定要提前备份好数据库！）。前者倒是能成功的转换数据（我没有仔细检查所有的数据内容），但其已经停止更新，网上显示的信息说只兼容到WP2.2，我的WP版本是2.7.1，所以我还是不太放心。又测试了后者，但发现其不能转换诸如“旅行游记·Travelling”之类中英混杂的内容，出现乱码。于是又继续寻找，终于发现一款满意的：bbWP2UTF8。这里是作者网站。

bbWP2UTF8参考了WordPress codex站点的官方指导，可以将任意字符集的WP数据库转换到UTF8格式，也可以转换WP Mu多用户版本的数据库。首先下载插件并安装激活，这时就可以在WP后台看到一个独立的操作面板。官方指导说要将wp-config.php中写入：

define(‘DB_CHARSET’, ‘utf8’);

这个不一定，实际上在我的博客中由于是将UTF8数据当作latin1字符存储，我在这里使用的是：

define(‘DB_CHARSET’, ‘latin1’);

这样才能正常显示文字。该插件在一开始提供了数据表勾选的功能，你可以选择全部转换或者只转换一部分数据表，这在某些多站点公用一个数据库的情况下很有用。然后会有一系列的流程一步步指导你来完成转换，解释很详细，你可以仔细检查转换中是否出现问题。我把主要的内容列在下面，就不翻译了：

Step 1: Convert columns to binary counterparts for checked tables

Queries log
Performed query: ALTER TABLE wp_comments CHANGE comment_author comment_author TINYBLOB NOT NULL
Performed query: ALTER TABLE wp_comments CHANGE comment_author_email comment_author_email VARBINARY ( 100 ) NOT NULL
Performed query: ALTER TABLE wp_comments CHANGE comment_author_url comment_author_url VARBINARY ( 200 ) NOT NULL
Performed query: ALTER TABLE wp_comments CHANGE comment_author_IP comment_author_IP VARBINARY ( 100 ) NOT NULL
Performed query: ALTER TABLE wp_comments CHANGE comment_content comment_content BLOB NOT NULL
Performed query: ALTER TABLE wp_comments CHANGE comment_approved comment_approved VARBINARY ( 20 ) NOT NULL
Performed query: ALTER TABLE wp_comments CHANGE comment_agent comment_agent VARBINARY ( 255 ) NOT NULL
Performed query: ALTER TABLE wp_comments CHANGE comment_type comment_type VARBINARY ( 20 ) NOT NULL
Performed query: ALTER TABLE wp_linkfeeds CHANGE item_title item_title VARBINARY ( 255 ) NOT NULL
Performed query: ALTER TABLE wp_linkfeeds CHANGE item_link item_link BLOB NOT NULL
Performed query: ALTER TABLE wp_linkfeeds CHANGE item_content item_content BLOB NOT NULL
Performed query: ALTER TABLE wp_linkfeeds CHANGE item_creator item_creator VARBINARY ( 255 ) NOT NULL
Performed query: ALTER TABLE wp_linkfeeds CHANGE item_subject item_subject VARBINARY ( 255 ) NOT NULL
Performed query: ALTER TABLE wp_linkfeeds CHANGE item_guid item_guid TINYBLOB NOT NULL
Performed query: ALTER TABLE wp_links CHANGE link_url link_url VARBINARY ( 255 ) NOT NULL
Performed query: ALTER TABLE wp_links CHANGE link_name link_name VARBINARY ( 255 ) NOT NULL
Performed query: ALTER TABLE wp_links CHANGE link_image link_image VARBINARY ( 255 ) NOT NULL
Performed query: ALTER TABLE wp_links CHANGE link_target link_target VARBINARY ( 25 ) NOT NULL
Performed query: ALTER TABLE wp_links CHANGE link_description link_description VARBINARY ( 255 ) NOT NULL
Performed query: ALTER TABLE wp_links CHANGE link_visible link_visible VARBINARY ( 20 ) NOT NULL
Performed query: ALTER TABLE wp_links CHANGE link_rel link_rel VARBINARY ( 255 ) NOT NULL
Performed query: ALTER TABLE wp_links CHANGE link_notes link_notes MEDIUMBLOB NOT NULL
Performed query: ALTER TABLE wp_links CHANGE link_rss link_rss VARBINARY ( 255 ) NOT NULL
Performed query: ALTER TABLE wp_links CHANGE link_favicon link_favicon VARBINARY ( 255 ) NOT NULL
Performed query: ALTER TABLE wp_options CHANGE option_name option_name VARBINARY ( 64 ) NOT NULL
Performed query: ALTER TABLE wp_options CHANGE option_value option_value LONGBLOB NOT NULL
Performed query: ALTER TABLE wp_options CHANGE autoload autoload VARBINARY ( 20 ) NOT NULL
Performed query: ALTER TABLE wp_postmeta CHANGE meta_key meta_key VARBINARY ( 255 ) NOT NULL
Performed query: ALTER TABLE wp_postmeta CHANGE meta_value meta_value LONGBLOB NOT NULL
Performed query: ALTER TABLE wp_posts CHANGE post_content post_content LONGBLOB NOT NULL
Performed query: ALTER TABLE wp_posts CHANGE post_title post_title BLOB NOT NULL
Performed query: ALTER TABLE wp_posts CHANGE post_excerpt post_excerpt BLOB NOT NULL
Performed query: ALTER TABLE wp_posts CHANGE post_status post_status VARBINARY ( 20 ) NOT NULL
Performed query: ALTER TABLE wp_posts CHANGE comment_status comment_status VARBINARY ( 20 ) NOT NULL
Performed query: ALTER TABLE wp_posts CHANGE ping_status ping_status VARBINARY ( 20 ) NOT NULL
Performed query: ALTER TABLE wp_posts CHANGE post_password post_password VARBINARY ( 20 ) NOT NULL
Performed query: ALTER TABLE wp_posts CHANGE post_name post_name VARBINARY ( 200 ) NOT NULL
Performed query: ALTER TABLE wp_posts CHANGE to_ping to_ping BLOB NOT NULL
Performed query: ALTER TABLE wp_posts CHANGE pinged pinged BLOB NOT NULL
Performed query: ALTER TABLE wp_posts CHANGE post_content_filtered post_content_filtered BLOB NOT NULL
Performed query: ALTER TABLE wp_posts CHANGE guid guid VARBINARY ( 255 ) NOT NULL
Performed query: ALTER TABLE wp_posts CHANGE post_type post_type VARBINARY ( 20 ) NOT NULL
Performed query: ALTER TABLE wp_posts CHANGE post_mime_type post_mime_type VARBINARY ( 100 ) NOT NULL
Performed query: ALTER TABLE wp_term_taxonomy CHANGE taxonomy taxonomy VARBINARY ( 32 ) NOT NULL
Performed query: ALTER TABLE wp_term_taxonomy CHANGE description description LONGBLOB NOT NULL
Performed query: ALTER TABLE wp_terms CHANGE name name VARBINARY ( 200 ) NOT NULL
Performed query: ALTER TABLE wp_terms CHANGE slug slug VARBINARY ( 200 ) NOT NULL
Performed query: ALTER TABLE wp_usermeta CHANGE meta_key meta_key VARBINARY ( 255 ) NOT NULL
Performed query: ALTER TABLE wp_usermeta CHANGE meta_value meta_value LONGBLOB NOT NULL
Performed query: ALTER TABLE wp_users CHANGE user_login user_login VARBINARY ( 60 ) NOT NULL
Performed query: ALTER TABLE wp_users CHANGE user_pass user_pass VARBINARY ( 64 ) NOT NULL
Performed query: ALTER TABLE wp_users CHANGE user_nicename user_nicename VARBINARY ( 50 ) NOT NULL
Performed query: ALTER TABLE wp_users CHANGE user_email user_email VARBINARY ( 100 ) NOT NULL
Performed query: ALTER TABLE wp_users CHANGE user_url user_url VARBINARY ( 100 ) NOT NULL
Performed query: ALTER TABLE wp_users CHANGE user_activation_key user_activation_key VARBINARY ( 60 ) NOT NULL
Performed query: ALTER TABLE wp_users CHANGE display_name display_name VARBINARY ( 250 ) NOT NULL

Converted all columns to their binary counterparts using the queries above. Next step will change all chosen tables’ characterset to UTF8.

Step 2: Convert tables to UTF8 character set

Queries log
Performed query: ALTER TABLE `wp_comments` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
Performed query: ALTER TABLE `wp_linkfeeds` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
Performed query: ALTER TABLE `wp_links` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
Performed query: ALTER TABLE `wp_options` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
Performed query: ALTER TABLE `wp_postmeta` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
Performed query: ALTER TABLE `wp_posts` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
Performed query: ALTER TABLE `wp_term_relationships` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
Performed query: ALTER TABLE `wp_term_taxonomy` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
Performed query: ALTER TABLE `wp_terms` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
Performed query: ALTER TABLE `wp_usermeta` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
Performed query: ALTER TABLE `wp_users` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

Converted all tables to UTF8 using the queries above. Next step will change the database’s characterset to UTF8.

Step 3: Convert database default character set to UTF8

Queries log
Performed query: ALTER DATABASE `dbname` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

Converted database to UTF8 using the query above. Next step will change the columns back to their original types.

Step 4: Convert columns back from binary to original types

Finished!

Converted columns back to their original types. The database should now be converted to UTF8, see the list below for the current database character set and its tables collation. Don’t forgot to de-activate this plugin and preferably remove it, because it has no use anymore after converting the database.

Queries log
Performed query: ALTER TABLE wp_comments CHANGE comment_author comment_author TINYTEXT NOT NULL
Performed query: ALTER TABLE wp_comments CHANGE comment_author_email comment_author_email VARCHAR ( 100 ) NOT NULL
Performed query: ALTER TABLE wp_comments CHANGE comment_author_url comment_author_url VARCHAR ( 200 ) NOT NULL
Performed query: ALTER TABLE wp_comments CHANGE comment_author_IP comment_author_IP VARCHAR ( 100 ) NOT NULL
Performed query: ALTER TABLE wp_comments CHANGE comment_content comment_content TEXT NOT NULL
Performed query: ALTER TABLE wp_comments CHANGE comment_approved comment_approved VARCHAR ( 20 ) NOT NULL
Performed query: ALTER TABLE wp_comments CHANGE comment_agent comment_agent VARCHAR ( 255 ) NOT NULL
Performed query: ALTER TABLE wp_comments CHANGE comment_type comment_type VARCHAR ( 20 ) NOT NULL
Performed query: ALTER TABLE wp_linkfeeds CHANGE item_title item_title VARCHAR ( 255 ) NOT NULL
Performed query: ALTER TABLE wp_linkfeeds CHANGE item_link item_link TEXT NOT NULL
Performed query: ALTER TABLE wp_linkfeeds CHANGE item_content item_content TEXT NOT NULL
Performed query: ALTER TABLE wp_linkfeeds CHANGE item_creator item_creator VARCHAR ( 255 ) NOT NULL
Performed query: ALTER TABLE wp_linkfeeds CHANGE item_subject item_subject VARCHAR ( 255 ) NOT NULL
Performed query: ALTER TABLE wp_linkfeeds CHANGE item_guid item_guid TINYTEXT NOT NULL
Performed query: ALTER TABLE wp_links CHANGE link_url link_url VARCHAR ( 255 ) NOT NULL
Performed query: ALTER TABLE wp_links CHANGE link_name link_name VARCHAR ( 255 ) NOT NULL
Performed query: ALTER TABLE wp_links CHANGE link_image link_image VARCHAR ( 255 ) NOT NULL
Performed query: ALTER TABLE wp_links CHANGE link_target link_target VARCHAR ( 25 ) NOT NULL
Performed query: ALTER TABLE wp_links CHANGE link_description link_description VARCHAR ( 255 ) NOT NULL
Performed query: ALTER TABLE wp_links CHANGE link_visible link_visible VARCHAR ( 20 ) NOT NULL
Performed query: ALTER TABLE wp_links CHANGE link_rel link_rel VARCHAR ( 255 ) NOT NULL
Performed query: ALTER TABLE wp_links CHANGE link_notes link_notes MEDIUMTEXT NOT NULL
Performed query: ALTER TABLE wp_links CHANGE link_rss link_rss VARCHAR ( 255 ) NOT NULL
Performed query: ALTER TABLE wp_links CHANGE link_favicon link_favicon VARCHAR ( 255 ) NOT NULL
Performed query: ALTER TABLE wp_options CHANGE option_name option_name VARCHAR ( 64 ) NOT NULL
Performed query: ALTER TABLE wp_options CHANGE option_value option_value LONGTEXT NOT NULL
Performed query: ALTER TABLE wp_options CHANGE autoload autoload VARCHAR ( 20 ) NOT NULL
Performed query: ALTER TABLE wp_postmeta CHANGE meta_key meta_key VARCHAR ( 255 ) NOT NULL
Performed query: ALTER TABLE wp_postmeta CHANGE meta_value meta_value LONGTEXT NOT NULL
Performed query: ALTER TABLE wp_posts CHANGE post_content post_content LONGTEXT NOT NULL
Performed query: ALTER TABLE wp_posts CHANGE post_title post_title TEXT NOT NULL
Performed query: ALTER TABLE wp_posts CHANGE post_excerpt post_excerpt TEXT NOT NULL
Performed query: ALTER TABLE wp_posts CHANGE post_status post_status VARCHAR ( 20 ) NOT NULL
Performed query: ALTER TABLE wp_posts CHANGE comment_status comment_status VARCHAR ( 20 ) NOT NULL
Performed query: ALTER TABLE wp_posts CHANGE ping_status ping_status VARCHAR ( 20 ) NOT NULL
Performed query: ALTER TABLE wp_posts CHANGE post_password post_password VARCHAR ( 20 ) NOT NULL
Performed query: ALTER TABLE wp_posts CHANGE post_name post_name VARCHAR ( 200 ) NOT NULL
Performed query: ALTER TABLE wp_posts CHANGE to_ping to_ping TEXT NOT NULL
Performed query: ALTER TABLE wp_posts CHANGE pinged pinged TEXT NOT NULL
Performed query: ALTER TABLE wp_posts CHANGE post_content_filtered post_content_filtered TEXT NOT NULL
Performed query: ALTER TABLE wp_posts CHANGE guid guid VARCHAR ( 255 ) NOT NULL
Performed query: ALTER TABLE wp_posts CHANGE post_type post_type VARCHAR ( 20 ) NOT NULL
Performed query: ALTER TABLE wp_posts CHANGE post_mime_type post_mime_type VARCHAR ( 100 ) NOT NULL
Performed query: ALTER TABLE wp_term_taxonomy CHANGE taxonomy taxonomy VARCHAR ( 32 ) NOT NULL
Performed query: ALTER TABLE wp_term_taxonomy CHANGE description description LONGTEXT NOT NULL
Performed query: ALTER TABLE wp_terms CHANGE name name VARCHAR ( 200 ) NOT NULL
Performed query: ALTER TABLE wp_terms CHANGE slug slug VARCHAR ( 200 ) NOT NULL
Performed query: ALTER TABLE wp_usermeta CHANGE meta_key meta_key VARCHAR ( 255 ) NOT NULL
Performed query: ALTER TABLE wp_usermeta CHANGE meta_value meta_value LONGTEXT NOT NULL
Performed query: ALTER TABLE wp_users CHANGE user_login user_login VARCHAR ( 60 ) NOT NULL
Performed query: ALTER TABLE wp_users CHANGE user_pass user_pass VARCHAR ( 64 ) NOT NULL
Performed query: ALTER TABLE wp_users CHANGE user_nicename user_nicename VARCHAR ( 50 ) NOT NULL
Performed query: ALTER TABLE wp_users CHANGE user_email user_email VARCHAR ( 100 ) NOT NULL
Performed query: ALTER TABLE wp_users CHANGE user_url user_url VARCHAR ( 100 ) NOT NULL
Performed query: ALTER TABLE wp_users CHANGE user_activation_key user_activation_key VARCHAR ( 60 ) NOT NULL
Performed query: ALTER TABLE wp_users CHANGE display_name display_name VARCHAR ( 250 ) NOT NULL
Current database character set: utf8