WordPress数据库字符集转换

0条评论

数据库字符集转换

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

历史记录

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

定义字符集与排序规则

自WordPress 2.2版起,WordPress用户可以在 wp-config.php文件中自定义数据库字符集与排序规则。在wp-config.php文件中设定DB_CHARSETDB_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类型字符串。再次提醒:所有更改都无法复原,一定要在使用插件前备份数据库。