数据库字符集转换
本文简要介绍了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类型字符串。再次提醒:所有更改都无法复原,一定要在使用插件前备份数据库。
分类:中文手册