Henry's Blog

Aliyun RDS Change MySQL Charset From utf8 To utf8mb4

本篇记录了，将一个部署在阿里云上的 Rails 项目，连接 MySQL 数据库的编码，由 utf8 调整为 utf8mb4 的详细步骤。
环境说明：
服务器系统 Centos，版本为 6.5；
数据库使用的是阿里云 RDS MySQL，版本为 5.5.18；
Rails 版本为 4.1.2；
mysql2 Gem, 版本为 0.3.16。

为什么要使用 utf8mb4 编码

根本的原因在于，采用 utf8 编码的 MySQL 无法保存 占位是4个字节的 Emoji 表情。
为了使后端的项目，全面支持客户端输入的 Emoji 表情，升级编码为 utf8mb4 是最佳解决方案。
之前一篇博文有讲到，不调整 MySQL 编码，使用 rumoji 替换 Emoji 表情为字母编号。
Ruby on Rails Use MySQL DB Support iPhone emoji。
这样处理至少有两个问题：
1. Emoji 表情会持续的更新，rumoji 库如果没有及时更新，输入新的表情，则会报错。
2. 使用 rumoji 对用户输入的字符，做正则匹配解析，增加了系统的开销，降低了性能。
另外，看到很多网友建议，迁移 MySQL 至 PostgreSQL、MongoDB 等，迁移成本不小，我们的项目暂时不考虑。

备注：MySQL 5.5.3 版本之后，引入了 utf8mb4 字符集。
在 mysql client 端，输入以下命令，确认 mysql server 是否支持 utf8mb4 编码。 

    mysql> SHOW CHAR SET WHERE Charset LIKE "%utf8%";
    +---------+---------------+--------------------+--------+
    | Charset | Description   | Default collation  | Maxlen |
    +---------+---------------+--------------------+--------+
    | utf8    | UTF-8 Unicode | utf8_general_ci    |      3 |
    | utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci |      4 |
    +---------+---------------+--------------------+--------+
至于排序规则（collation） 选择默认的 utf8mb4_general_ci，还是 utf8mb4_unicode_ci。
请参考下面文章的介绍：Character Set & Collation In MySQL
作者，从排序的准确性，以及性能方面，告诉我们应该选用 utf8mb4_unicode_ci 。

实施的步骤

1. 在阿里云 RDS 控制台，新建一个数据库
名称为：production_new
字符集选择：utf8mb4

2. 修改 database.yml 编码

    # config/database.yml
    encoding: utf8mb4

3. Ruby 程序接收和返回 JSON 数据时，强制使用 UTF-8 编码

    "string".force_encoding("UTF-8")

4. 停止 Ruby 进程，停止队列等服务

5. 部署项目报错
正式版部署之前，有在本地和 Staging 做过测试。
信心满满的在正式版部署 Ruby 项目，执行的过程中，遇到下面的错误：

  Character set 'utf8mb4' is not a compiled character set and is not specified in the '/path/mysql/charsets/Index.xml' file
  rake aborted!
  Mysql2::Error: Can't initialize character set utf8mb4 (path: /path/mysql/charsets/)
  /path/.rvm/gems/ruby-2.0.0-p598/gems/mysql2-0.3.16/lib/mysql2/client.rb:70:in `connect'
  /path/.rvm/gems/ruby-2.0.0-p598/gems/mysql2-0.3.16/lib/mysql2/client.rb:70:in `initialize'
经过一番检查后发现，错误是由于当前系统上的 MySQL 客户端版本过低导致。
  # 列出所有被安装的 mysql 包
  rpm -qa | grep mysql
  mysql-devel-5.1.73-3.el6_5.x86_64
  mysql-5.1.73-3.el6_5.x86_64
  mysql-libs-5.1.73-3.el6_5.x86_64

6. 移除现有的旧版本，重新安装 5.5 版本的客户端 

  # 清理现有 mysql
  yum list installed | grep -i mysql
  yum remove mysql mysql-*

  # 修改安装源，不修改源，重新安装的仍然是 5.1 的版本
  rpm -Uvh http://repo.webtatic.com/yum/el6/latest.rpm

  # 安装 5.5 版本的 mysql client
  yum install libmysqlclient16 --enablerepo=webtatic
  yum install mysql55w mysql55w-libs mysql55w-devel --enablerepo=webtatic

  # 如果你需要安装 mysql server，请执行
  yum install  mysql55w-server --enablerepo=webtatic

7. 重新安装 mysql2 Gem
MySQL 5.5 客户端安装完毕之后，再次运行项目，同样出现上面的报错信息。 于是，决定重新安装一遍 mysql2 Gem。安装完成之后，项目运行正常。

  # 重新安装 mysql2 Gem
  gem uninstall mysql2
  gem install mysql2 -v '0.3.16'

8. 备份和还原数据库 

  # 备份现有数据库
  mysqldump -h host -u user -p production_old > production_old.sql

  # 还原至新数据库
  mysql -h host -u user -p production_new < production_old.sql

9. 执行调整单个表(table)编码的 SQL 文件 

  mysql -h host -u user -p production_new < db/sql/utf8mb4_charset.sql

SQL 文件的内容，举例说明如下:
通常只需要修改 table 的编码，若该 table 内有字段(column)是 VARCHAR，或者 TEXT， MySQL 将自动调整该字段的编码，与 table 的默认编码保持一致

  ALTER TABLE `versions` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

MySQL 索引长度的限制错误
在执行 SQL 文件时，发现有些 table 更改编码时报错，信息如下：

  # ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

报错原因在于：MySQL Innodb 的索引长度限制为 767 字节，UTF8mb4 字符集是 4 个字节，
767 字节 / 4 字节每字符 = 191 字符（即默认的索引最大长度）
因此在 varchar(255) 类型字段上，创建索引会失败，提示最大索引长度为767字节。
实践中发现，MySQL 5.5.x 的版本，只有 Unique 的索引，或者联合索引，才会报错。
普通索引，Rails 的 Migration 会自动对索引 Index 的长度增加一个 191 的限制。
而 MySQL 5.6.x 的版本，即使普通索引也会报错。
处理思路：先移除索引，然后修改表的编码，修改成功之后。 调整索引对应字段的长度为 191，最后再次创建索引。 

  ALTER TABLE `users` DROP INDEX index_users_on_email;
  ALTER TABLE `users` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  ALTER TABLE `users` MODIFY `email` VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  ALTER TABLE  `users` ADD UNIQUE index_users_on_email (email);

  # 查看一下最新的表结构
  SHOW CREATE TABLE `users`;

10. 再次启动服务

备注：使用 Migration 迁移 Table 编码
如果没有索引的问题，可以使用下面的代码，修改表的编码，简洁许多。
也可以使用下面的代码，打印 SQL 语句到 .sql 文件。
然后，对引起索引 Index 错误的 Table 单独增加处理的 SQL 语句。

  def up
    char_set = 'utf8mb4'
    collation = 'utf8mb4_unicode_ci'
    table_names = ActiveRecord::Base.connection.tables
    table_names.each do |table_name|
      execute "ALTER TABLE `#{table_name}` CONVERT TO CHARACTER SET #{char_set} COLLATE #{collation};"
    end
  end

参考链接
Support Emoji in Rails 3.2.14
active_record, MySQL, and emoji
update MySQL version from 5.1 to 5.5 in CentOS 6.2
Character Set & Collation In MySQL
What's the difference between utf8_general_ci and utf8_unicode_ci

2015-03-05

rocket-wing