数据库 阿里云 Rails 项目调整 RDS MySQL 编码为 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 http://manageyp.github.com/ruby-on-rails/2014/12/10/ruby-on-rails-use-mysql-db-support-iphone-emoji.html
但是，这样处理至少有两个问题：
- Emoji 表情会持续的更新，rumoji 库如果没有及时更新，输入新的表情，则会报错。
- 使用 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： http://infopotato.com/blog/index/mysql_character_set_and_collation 作者从排序的准确性，以及性能方面，告诉我们应该选用 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 做过测试。 信心满满的在正式版部署，执行的过程中，遇到下面的错误：
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
原文链接 Aliyun RDS Change MySQL Charset From utf8 To utf8mb4: http://manageyp.github.com/ruby-on-rails/2015/03/05/aliyun-rds-change-mysql-charset-from-utf8-to-utf8mb4.html
参考链接 （推荐阅读）： Support Emoji in Rails 3.2.14 http://mumaren.me/blog/2013/11/27/support-emoji-in-rails-3-dot-2-14/
active_record, MySQL, and emoji http://tech.taskrabbit.com/blog/2014/04/24/active-record-mysql-and-emoji/
update MySQL version from 5.1 to 5.5 in CentOS 6.2 http://stackoverflow.com/questions/9361720/update-mysql-version-from-5-1-to-5-5-in-centos-6-2
Character Set & Collation In MySQL http://infopotato.com/blog/index/mysql_character_set_and_collation
这是我们的，忘记原文在哪里了。
desc "Database related tasks"
namespace :database do
desc "Convert to utf8mb4"
task convert_to_utf8mb4: :environment do
connection = ActiveRecord::Base.connection
database = connection.current_database
connection.execute "ALTER DATABASE #{database} CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;"
puts "Converted #{database} character set"
# Change all tables
connection.tables.each do |table|
connection.columns(table).each do |column|
if column.sql_type == "varchar(255)"
puts "#{column.name} is varchar(255)"
# Check for 255 indexed columns
connection.indexes(table).each do |index|
if index.columns.include?(column.name)
puts "#{column.name} has index, altering length..."
connection.execute "ALTER TABLE #{table} CHANGE `#{column.name}` `#{column.name}` varchar(191);"
puts "...done!"
end
end
end
end
puts "Converting #{table}..."
connection.execute "ALTER TABLE #{table} CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
puts "...#{table} converted!"
connection.columns(table).each do |column|
if column.type == :string || column.type == :text
puts "Converting #{column.name}..."
connection.execute "ALTER TABLE #{table} CHANGE `#{column.name}` `#{column.name}` #{column.sql_type} CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
puts "...#{column.name} done!"
end
end
puts "Repairing #{table}..."
connection.execute "REPAIR TABLE #{table};"
puts "...#{table} repaired!"
puts "Optimizing #{table}..."
connection.execute "OPTIMIZE TABLE #{table};"
puts "...#{table} optimized!"
end
end
end
非常感谢分享！你们的实现比较好。
我最开始写了下面这个 Migration，处理只针对自身的项目，不够严谨。
def up
char_set = 'utf8mb4'
collation = 'utf8mb4_unicode_ci'
db_name = ActiveRecord::Base.connection.current_database
table_names = ActiveRecord::Base.connection.tables
# change database
execute "ALTER DATABASE `#{db_name}` CHARACTER SET #{char_set} COLLATE #{collation};"
# Mysql2::Error: Specified key was too long; max key length is 767 bytes
remove_index :users, :email
#change table
table_names.each do |table_name|
execute "ALTER TABLE `#{table_name}` CONVERT TO CHARACTER SET #{char_set} COLLATE #{collation};"
# change sting and text column
columns = ActiveRecord::Base.connection.columns(table_name)
columns.each do |column|
case column.type.to_s
when 'string'
if ['email'].include?(column.name.to_s)
execute "ALTER TABLE `#{table_name}` MODIFY `#{column.name}` VARCHAR(191) CHARACTER SET #{char_set} COLLATE #{collation};"
else
execute "ALTER TABLE `#{table_name}` MODIFY `#{column.name}` VARCHAR(255) CHARACTER SET #{char_set} COLLATE #{collation};"
end
when 'text'
execute "ALTER TABLE `#{table_name}` MODIFY `#{column.name}` TEXT CHARACTER SET #{char_set} COLLATE #{collation};"
end
end
end
# rebuild index
add_index :users, :email, unique: true
end
后期发现，以上的代码实现，如果没有索引的问题，不需要单独处理 String 和 Text 字段的。代码多余了。 然后，在本地测试和预发布环境，执行没有问题，但是，正式版部署遇到了问题。 之后，使用纯 SQL 脚本，在 MySQL client 里面调试，查找问题。
问题一是：MySQL client 版本低； 问题二是：阿里云的 RDS 数据库，只能在创建时设置编码，其后不能更改，可能是出于安全保护措施。
参加阿里云官方文档： RDS MySQL 使用 UTF8mb4 字符集存储 emoji 表情 http://help.aliyun.com/knowledge_detail.htm?knowledgeId=5990076&categoryId=8314909
BTW：不是在替阿里云做广告哈，只是，运维的同事比较偏向于，使用阿里云的服务。
前两天刚倒腾过，参考的是 xdite 的 blog： http://blog.xdite.net/posts/2013/12/19/mysql-with-utf8mb4
@rubyu2 的貌似不错
PostgreSQL 的 Unicode 编码，支持 1-4 个字节，所以，天然支持 Emoji 表情。
参考下面的官方说明文档： http://www.postgresql.org/docs/9.1/static/multibyte.html
如果你已经安装好 PostgreSQL，Rails 4 的相关配置如下：
# Gemfile
gem "pg", '0.17.0'
# config/database.yml
development:
adapter: postgresql
encoding: unicode
database: database_name
pool: 5
username: postgresql_user
password: postgresql_password
楼主我的 MySQL 已经收到 5.6 了但还是报错 Can't initialize character set utf8mb4 (path: /usr/share/mysql/charsets/)，和你遇到是一个问题不？
- column.sql_type == "varchar(255)" 是不是应该写成 column.sql_type > "varchar(191)"？
- 第 20 行如果有默认值的话会被清掉
connection.execute "ALTER TABLE #{table} CHANGE `#{column.name}` `#{column.name}` varchar(191);"
- 如果 column 原先有内容超过 191 的，直接截断吗？
- 如果一张表的 varchar 255 字段最先不是做 index 后来改成是 index 的一部分，那岂不是长度限制就要变化了？ 这样一来，是否意味着所有的 varchar 字段，只要有可能性做 index，那就在设计的时候就限制成 191？
#39 楼 @betterthornbird 当时我们的情况是这样，只有用户名，还有用户的签名里和回复有表情。然后其他地方是没有的。 多数情况下我们的 varchar(255) 类型的，都是存的非常短的内容，比如用户名，这个长度虽然定义了 255，但是到 100 的都没有，当时也是考虑过这个问题的。如果担心有截断，可以在这里 if 判断加上某个表或者某行不处理，或者增加长度，可以做的很灵活，当时我们就是这样处理的。
还有要注意一点！在正式服务器运行的时候，如果表是比较常用的表，比如 user 表，因为需要锁，而且表也比较大的话，可能会卡在那非常久，所以建议处理的时候，有选择处理，还有就是可能会需要避开高峰期。
#7 楼 @rubyu2 你这段会把原有的 null 约束和 default 值弄没... 改了一下，Rails4 测试通过，其他没测：https://gist.github.com/hooopo/9310986e87200828e5e9
同在阿里云上使用 Rails 和 Mysql 5.5, 一般只需要两步就可以解决了，
- 在 database 配置中添加下述配置 encoding: utf8mb4 charset: utf8mb4
- 在数据库中把表中需要使用 utf8mb4 的列的排序改成 utf8mb4_general_ci 即可
这种方式因为简单，而且对原数据库和表的改动最小，很灵活。 因为一般是要兼容 emoji 等特殊字符的描述类数据才需要 utf8mb4，所以也没什么可能需要给这种列加索引。
