再谈Wordpress的MySQL乱码问题解决方法

在MySQL4.1下,中文的WP就会产生种种的乱码问题。

一个程序( PHP,CGI 等)与MySQL建立连接后,这个程序发送给MySQL的数据采用的是什么字符集,MySQL 是无从得知的。所以解决乱码问题的根本就是我们在程序中告诉MySQL采用的编码是什么,简单的就是在程序中加入这样的一个语句:

SET NAMES 'utf8';

这个语句的效果等同于同时设定了

SET character_set_client='utf8';
SET character_set_connection='utf8';
SET character_set_results='utf8';

为什么这么做?

我们安装MySQL4.1时按照默认配置,那么default-character-set= utf8。在MySQL Command Line Client下查看到的查看系统的字符集和排序方式的设定为:

mysql> SHOW VARIABLES LIKE ‘character_set_%’;
+--------------------------+----------------------------+
| Variable_name            |            Value           |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | utf8                       |
| character_set_results    | latin1                     |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
7 rows in set (0.00 sec)

mysql> SHOW VARIABLES LIKE ‘collation_%’;
+----------------------+-------------------+
| Variable_name        |      Value        |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | utf8_general_ci   |
| collation_server     | utf8_general_ci   |
+----------------------+-------------------+
3 rows in set (0.00 sec)

按照MySQL的存储机制,数据在传输的过程中就会在latin1和utf8两种编码之间互相转换,这样就很容易的变成了乱码。我们在程序中设置了 SET NAMES ‘UTF8′,就等同于把所有的编码都设置为utf8,这样数据就没有了编码转换问题,也就没有了乱码问题了。

在WP中的设置方法

那么具体在WordPress中,怎么设定SET NAMES 'UTF8'呢?

对于PHP的MySQL系统来说,这样的修改很简单:

找到wp-includes/wp-db.php

    $this->dbh = @mysql_connect($dbhost,$dbuser,$dbpassword);
    //加上下面这行
    $this->query("SET NAMES 'utf8'");

这样数据在传输和存储的过程中,都不会出现乱码了。

然后把所有表和字段的字符集改成utf8,运行以下语句:

--
-- TABLE
-- 

ALTER TABLE `wp_categories` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_comments` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_linkcategories` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_links` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_options` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_post2cat` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_postmeta` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_posts` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_usermeta` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_users` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

--
-- TABLE wp_categories
-- 

ALTER TABLE `wp_categories` CHANGE `cat_name` `cat_name` varchar( 55 ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_categories` CHANGE `category_nicename` `category_nicename` varchar( 200 ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_categories` CHANGE `category_description` `category_description` LONGTEXT CHARACTER SET utf8 COLLATE utf8_general_ci;

--
-- TABLE wp_comments
-- 

ALTER TABLE `wp_comments` CHANGE `comment_author` `comment_author` tinytext CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_comments` CHANGE `comment_author_email` `comment_author_email` varchar( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_comments` CHANGE `comment_author_url` `comment_author_url` varchar( 200 ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_comments` CHANGE `comment_author_IP` `comment_author_IP` varchar( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_comments` CHANGE `comment_content` `comment_content` text CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_comments` CHANGE `comment_approved` `comment_approved` ENUM( '0', '1', 'spam' ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_comments` CHANGE `comment_agent` `comment_agent` varchar( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_comments` CHANGE `comment_type` `comment_type` varchar( 20 ) CHARACTER SET utf8 COLLATE utf8_general_ci;

--
-- TABLE wp_linkcategories
-- 

ALTER TABLE `wp_linkcategories` CHANGE `cat_name` `cat_name` tinytext CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_linkcategories` CHANGE `auto_toggle` `auto_toggle` enum( 'Y', 'N' ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_linkcategories` CHANGE `show_images` `show_images` enum( 'Y', 'N' ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_linkcategories` CHANGE `show_description` `show_description` enum( 'Y', 'N' ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_linkcategories` CHANGE `show_rating` `show_rating` enum( 'Y', 'N' ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_linkcategories` CHANGE `show_updated` `show_updated` enum( 'Y', 'N' ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_linkcategories` CHANGE `sort_order` `sort_order` varchar( 64 ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_linkcategories` CHANGE `sort_desc` `sort_desc` enum( 'Y', 'N' ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_linkcategories` CHANGE `text_before_link` `text_before_link` varchar( 128 ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_linkcategories` CHANGE `text_after_link` `text_after_link` varchar( 128 ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_linkcategories` CHANGE `text_after_all` `text_after_all` varchar( 128 ) CHARACTER SET utf8 COLLATE utf8_general_ci;

--
-- TABLE wp_links
-- 

ALTER TABLE `wp_links` CHANGE `link_url` `link_url` varchar( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_links` CHANGE `link_name` `link_name` varchar( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_links` CHANGE `link_image` `link_image` varchar( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_links` CHANGE `link_target` `link_target` varchar( 25 ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_links` CHANGE `link_description` `link_description` varchar( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_links` CHANGE `link_visible` `link_visible` enum( 'Y', 'N' ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_links` CHANGE `link_rel` `link_rel` varchar( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_links` CHANGE `link_notes` `link_notes` mediumtext CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_links` CHANGE `link_rss` `link_rss` varchar( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci;

--
-- TABLE wp_options
-- 

ALTER TABLE `wp_options` CHANGE `option_name` `option_name` varchar( 64 ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_options` CHANGE `option_can_override` `option_can_override` enum( 'Y', 'N' ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_options` CHANGE `option_value` `option_value` longtext CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_options` CHANGE `option_description` `option_description` tinytext CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_options` CHANGE `autoload` `autoload` enum( 'Y', 'N' ) CHARACTER SET utf8 COLLATE utf8_general_ci;

--
-- TABLE wp_postmeta
-- 

ALTER TABLE `wp_postmeta` CHANGE `meta_key` `meta_key` varchar( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_postmeta` CHANGE `meta_value` `meta_value` longtext CHARACTER SET utf8 COLLATE utf8_general_ci;

--
-- TABLE wp_posts
-- 

ALTER TABLE `wp_posts` CHANGE `post_content` `post_content` longtext CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_posts` CHANGE `post_title` `post_title` text CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_posts` CHANGE `post_excerpt` `post_excerpt` text CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_posts` CHANGE `post_status` `post_status` enum( 'publish', 'draft', 'private', 'static', 'object', 'attachment' ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_posts` CHANGE `comment_status` `comment_status` enum( 'open', 'closed', 'registered_only' ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_posts` CHANGE `ping_status` `ping_status` enum( 'open', 'closed' ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_posts` CHANGE `post_password` `post_password` varchar( 20 ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_posts` CHANGE `post_name` `post_name` varchar( 200 ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_posts` CHANGE `to_ping` `to_ping` text CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_posts` CHANGE `pinged` `pinged` text CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_posts` CHANGE `post_content_filtered` `post_content_filtered` text CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_posts` CHANGE `guid` `guid` varchar( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_posts` CHANGE `post_type` `post_type` varchar( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_posts` CHANGE `post_mime_type` `post_mime_type` varchar( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci;

--
-- TABLE wp_usermeta
-- 

ALTER TABLE `wp_usermeta` CHANGE `meta_key` `meta_key` varchar( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_usermeta` CHANGE `meta_value` `meta_value` longtext CHARACTER SET utf8 COLLATE utf8_general_ci;

--
-- TABLE wp_users
-- 

ALTER TABLE `wp_users` CHANGE `user_login` `user_login` varchar( 60 ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_users` CHANGE `user_pass` `user_pass` varchar( 64 ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_users` CHANGE `user_nicename` `user_nicename` varchar( 50 ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_users` CHANGE `user_email` `user_email` varchar( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_users` CHANGE `user_url` `user_url` varchar( 100 ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_users` CHANGE `user_activation_key` `user_activation_key` varchar( 60 ) CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE `wp_users` CHANGE `display_name` `display_name` varchar( 250 ) CHARACTER SET utf8 COLLATE utf8_general_ci;
2006-12-27 10:49 272 mysql wordpress 乱码