数据库字符集转换

本文简要介绍了WordPress MySQL数据库表格字符集的转换过程。警告：字符集转换是个复杂的过程，转换前请务必进行完整数据库备份。

历史记录

自WordPress 2.1.3版起，WordPress采用 latin1字符集以及latin1_swedish_ci排序规则创建其数据库。

定义字符集与排序规则

自WordPress 2.2版起，WordPress用户可以在 wp-config.php文件中自定义数据库字符集与排序规则。在wp-config.php文件中设定DB_CHARSET与DB_COLLATE 的值，之后WordPress也会根据相应设置来创建数据库。但该设置不适用于新WordPress安装文件，也不适用于“已安装”WordPress的副本。下面介绍如何为已有WordPress安装文件转换字符集与排序规则。

转换数据库

无论做任何变动前，请务必备份数据库。备份数据库一文中有相应操作说明。

为了方便描述，假设现在有一个以latin1字符集的数据库，我们需要将这个数据库转换为utf8字符集。

问题

需要用MySQL ALTER TABLE命令来转换数据库中的字符集。转换时，所有文本（以及类似）字段都被转换为UTF-8字符。WordPress可能在使用latin1字符集的数据库中存储Unicode字符，而进行字符集转换时默认需要转换的是latin1字符集，于是转换可能会打乱原有的文本，直接导致转换后的内容惨不忍睹，失去使用价值。

解决方法

将所有文本和类似字段转换为相应二进制字符串，然后再进行字符集转换，最后将二进制字符串转换回文本。

示例步骤：

1. 通知访问者，此时博客处于无法访问状态

2. 备份数据库

3. ALTER TABLE wp_users MODIFY display_name BLOB

4. 在所有其它表/列上使用ALTER TABLE命令

5. ALTER DATABASE wordpress charset=utf8

6. ALTER TABLE wp_users charset=utf8

7. 在所有其它表/列上使用ALTER TABLE命令

8. ALTER TABLE wp_users MODIFY display_name TEXT CHARACTER SET utf8;

9. 在所有其它表/列上使用ALTER TABLE命令

10. 在wp-config.php中添加DB_CHARSET 与 DB_COLLATE定义

11. 将博客调回可访问状态

字符串字段也需要转换成相应的二进制字符串，如：

• CHAR -> BINARY
• VARCHAR -> VARBINARY
• TINYTEXT -> TINYBLOB
• TEXT -> BLOB
• MEDIUMTEXT -> MEDIUMBLOB
• LONGTEXT -> LONGBLOB

该信息原先发布在Forum Thread 117955中。

以下语句（将其中的MyDb更改成自己的数据库名称）可以生成执行这些转换的SQL语句。如果用 -s –skip-column-names运行mysql，通过复制粘贴获取输出结果会更方便：

USE information_schema; 

SELECT CONCAT('ALTER TABLE ', table_name, ' MODIFY ', column_name, ' ', REPLACE(column_type,

'char', 'binary'), ';') FROM columns WHERE table_schema = 'MyDb' and data_type LIKE '%char%'; 

SELECT CONCAT('ALTER TABLE ', table_name, ' MODIFY ', column_name, ' ', REPLACE(column_type,

'text', 'blob'), ';') FROM columns WHERE table_schema = 'MyDb' and data_type LIKE '%text%'; 

SELECT CONCAT('ALTER TABLE ', table_name, ' MODIFY ', column_name, ' ', column_type, ' CHARACTER 

SET utf8;') FROM columns WHERE table_schema = 'MyDb' and data_type LIKE '%char%';    

SELECT CONCAT('ALTER TABLE ', table_name, ' MODIFY ', column_name, ' ', column_type, ' CHARACTER 

SET utf8;') FROM columns WHERE table_schema = 'MyDb' and data_type LIKE '%text%'; 

根据数据库中的表和列，这些语句会输出另一些语句。将其中的数据库名称改为自己的数据库，然后运行输出的语句。

然后更改默认语言：

ALTER DATABASE MyDb CHARACTER SET utf8;  

也可以恢复列的类型，但一定要确保没有改动之前二进制格式或blob类型的列：

SELECT CONCAT('ALTER TABLE ', table_name, ' MODIFY ', column_name, ' ', REPLACE(column_type, 
'binary', 'char'), ';') FROM columns WHERE table_schema = 'MyDb' and data_type LIKE '%binary%';    
SELECT CONCAT('ALTER TABLE ', table_name, ' MODIFY ', column_name, ' ', REPLACE(column_type, 
'blob', 'text'), ';') FROM columns WHERE table_schema = 'MyDb' and data_type LIKE '%blob%';    

（代码来源于Haidong Ji’s blog中的一篇文章）

ENUM类型和SET类型的转换规则更加详细：将字符设为二进制字符，如果确定ENUM或SET中的字符转换后不会出现混乱，也可以讲字符设为UTF8字符。该操作的SQL语句是：

• ALTER TABLE wp_links CHANGE link_visible link_visible ENUM(‘Y’,’N’) CHARACTER SET utf8;

其中的字段名称和ENUM类型都需要重复出现。

指定BINARY和VARBINARY后，字段长度也需要被指定，字段长度值等于原始CHAR以及VARCHAR的字段长度值。换言之，VARCHAR(200)变成VARBINARY(200)。

因此我们在第三步和第四步将CHAR, VARCHAR, TEXT, ENUM, 以及SET字段转换为相应的二进制字符（BLOB, VARBINARY等），在第五步中将数据库字符转换为utf8，第六步和第七步中将所有表转换为utf8字符，最后在第八步和第九步中将二进制字段字符转换为相应的CHAR, VARCHAR, TEXT, ENUM, 以及SET类型的utf8字符集。

转换字符集的关键在于，所有字段都被转换成相应的二进制类型字段而不是CHAR, VARCHAR, TEXT, ENUM, 以及SET字段，这样可以保证数据库和表中的字符集转换为utf8时不出现差错。

转换脚本和插件

WordPress论坛中一位会员在一个主题中提交了自己制作的字符集转换脚本，Convert UTF8 SQL Generator（现在已经是死链接），该脚本可以自动转换WordPress数据库中的字符集。

UTF-8 Database Converter插件目前仍然可以使用，使用前请仔细阅读插件中的readme文件。该插件可能会损坏WordPress新版本中的数据。

UTF-8 Sanitize插件也可以用来转换数据库字符集，该插件能够自动将不同字符转换为相应的UTF类型字符串。再次提醒：所有更改都无法复原，一定要在使用插件前备份数据库。

分类：中文手册