数据库 阿里云 Rails 项目调整 RDS MySQL 编码为 utf8mb4 的详细步骤

manageyp · 2015年03月17日 · 最后由 Ruby-Hash 回复于 2016年12月13日 · 24993 次阅读
本帖已被管理员设置为精华贴

最近,将一个部署在阿里云上的 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

但是,这样处理至少有两个问题:

  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: 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

3 楼 已删除

还好项目一开始 mysql 的编码就用的 utf8mb4

#7 楼 @rubyu2

非常感谢分享!你们的实现比较好。

我最开始写了下面这个 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:不是在替阿里云做广告哈,只是,运维的同事比较偏向于,使用阿里云的服务。

#2 楼 @zgm

一切顺利哦 😄 👍

#1 楼 @meix 加油,小伙子 😄 👍

正是我需要的,太好了

#5 楼 @ywjno 同。

还有不用整个 table 都用 utf8mb4。有点浪费。有用户输入的字段 alter table modify column 用 utf8mb4 就行了。

:plus1: 😄 :

学习了,:)

正好遇到了问题,采集学习了

#13 楼 @est 客户要求没办法,就那么给他干了

哎,这个帖子咋不早发布半个月,半月前遇到问题,折腾了两天。

#18 楼 @rfei 不好意思啊,春节回来调整的,后来运行观察了一段时间。确定没问题了,才想到发出来的~~

前两天刚倒腾过,参考的是 xdite 的 blog: http://blog.xdite.net/posts/2013/12/19/mysql-with-utf8mb4

@rubyu2 的貌似不错

研究中:thumbsup:

马上就用到,感谢分享

马上就用到,感谢分享

感谢,先辈

感谢做出贡献的前辈。

不知道用 postgresql,有没有这样的问题,如何解决?

#27 楼 @steven_yue

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

#30 楼 @storm_zhang 哈哈哈,3Q,安卓大神!😃 👍

楼主我的 MySQL 已经收到 5.6 了但还是报错 Can't initialize character set utf8mb4 (path: /usr/share/mysql/charsets/),和你遇到是一个问题不?

#32 楼 @Hobo 不确定是不是同一个问题的。你可以到 Google 里检索一下看看。

#7 楼 @rubyu2 有两个问题

  1. column.sql_type == "varchar(255)" 是不是应该写成 column.sql_type > "varchar(191)"?
  2. 第 20 行如果有默认值的话会被清掉

#34 楼 @kalel 1,不是的。不过 rails string 的类型默认 255,所以转成 191。如果你们项目中有其他长度的 column,也可以单独处理的,这个只是适用多数情况。 2,默认值应该不会被清掉吧,跟 change_column 效果一样的。

哈哈哈,半个月前也刚踩坑。

#7 楼 @rubyu2 问几个问题

connection.execute "ALTER TABLE #{table} CHANGE `#{column.name}` `#{column.name}` varchar(191);"
  1. 如果 column 原先有内容超过 191 的,直接截断吗?
  2. 如果一张表的 varchar 255 字段最先不是做 index 后来改成是 index 的一部分,那岂不是长度限制就要变化了? 这样一来,是否意味着所有的 varchar 字段,只要有可能性做 index,那就在设计的时候就限制成 191?

#38 楼 @rubyu2 所以你们当时是存储内容不超过这个长度?

那么对于问题 2 呢?我觉得是个坑啊

#39 楼 @betterthornbird 当时我们的情况是这样,只有用户名,还有用户的签名里和回复有表情。然后其他地方是没有的。 多数情况下我们的 varchar(255) 类型的,都是存的非常短的内容,比如用户名,这个长度虽然定义了 255,但是到 100 的都没有,当时也是考虑过这个问题的。如果担心有截断,可以在这里 if 判断加上某个表或者某行不处理,或者增加长度,可以做的很灵活,当时我们就是这样处理的。

还有要注意一点!在正式服务器运行的时候,如果表是比较常用的表,比如 user 表,因为需要锁,而且表也比较大的话,可能会卡在那非常久,所以建议处理的时候,有选择处理,还有就是可能会需要避开高峰期。

#40 楼 @rubyu2 谢谢提醒!

if 判断加上某个表或者某行不处理,或者增加长度

这里是指列吧。

cool!解决问题!

utf8mb4有遇到过 invalid byte sequence in UTF-8 吗?比如存进去的是 \xED\xA0\xBD

#7 楼 @rubyu2 你这段会把原有的 null 约束和 default 值弄没... 改了一下,Rails4 测试通过,其他没测:https://gist.github.com/hooopo/9310986e87200828e5e9

同在阿里云上使用 Rails 和 Mysql 5.5, 一般只需要两步就可以解决了,

  1. 在 database 配置中添加下述配置 encoding: utf8mb4 charset: utf8mb4
  2. 在数据库中把表中需要使用 utf8mb4 的列的排序改成 utf8mb4_general_ci 即可

这种方式因为简单,而且对原数据库和表的改动最小,很灵活。 因为一般是要兼容 emoji 等特殊字符的描述类数据才需要 utf8mb4,所以也没什么可能需要给这种列加索引。

在 mysql 里面能保存了,但是取出来显示不正确呢,如何解决?

需要 登录 后方可回复, 如果你还没有账号请 注册新账号