How to convert Mysql encoding utf8 to utf8mb4 in Rails project

Question

I have a Rails 3.2 project using Mysql 5.5.34, with utf8 encoding. Now I found that with utf8 encoding Mysql could not save unicode characters which represent emoji.

So is it OK for me to convert the whole database to use utf8mb4 encoding that I found on the web that could hold 4 byte unicode include emoji?

Is all the information I have in the database covered by utf8mb4 encoding? Will I face data loses if I do that?

Is there any way that Rails provide to do that?

Thanks a lot for helping.

I am kinda facing the same situation.. What did you end up doing? — Hari
– Hari, Commented Apr 24, 2015 at 5:20
@Hari Sorry for the late response. I've done that following this blog: blog.xdite.net/posts/2013/12/19/mysql-with-utf8mb4 . Unfortunately it's written in Chinese, would you try Google translate to see if it helps? — larryzhao
– larryzhao, Commented May 5, 2015 at 14:57

sirko · Accepted Answer · 2014-08-11 14:48:07Z

1

Actually you just need to migrate the column you want to encode with utf8mb4.

execute("ALTER TABLE yourtablename MODIFY yourcolumnname TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;")

If you plan to migrate the data itself it might not be possible, since the common utf8 consists out of 3 byte chars and the utf8mb4 out of 4 byte. So you might already have corrupt data in your db.

Furthermore Rails 3.2 has an encoding issue within ActiveSupports JSON encoding. In case you plan to work with json and emojis, you will need to add a patch like the following (based on the solution in rails 4 https://github.com/rails/rails/blob/4-0-stable/activesupport/lib/active_support/json/encoding.rb) or just simply upgrade to rails 4.

module ActiveSupport
  module JSON
    module Encoding
      class << self
        def escape(string)
          if string.respond_to?(:force_encoding)
            string = string.encode(::Encoding::UTF_8, :undef => :replace).force_encoding(::Encoding::BINARY)
          end
          json = string.gsub(escape_regex) { |s| ESCAPED_CHARS[s] }
          json = %("#{json}")
          json.force_encoding(::Encoding::UTF_8) if json.respond_to?(:force_encoding)
          json
        end
      end
    end
  end
end

edited Aug 11, 2014 at 14:48

answered Aug 11, 2014 at 14:40

sirko

1431 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Hari Over a year ago

I did the alter table query above.. the smilies still end up as a bunch of question marks like this "????" .. Any idea?

sirko Over a year ago

Anything I should add to the answer?

Vigneshwaran Over a year ago

@Hari What did you do to solve the database connection?

Collectives™ on Stack Overflow

How to convert Mysql encoding utf8 to utf8mb4 in Rails project

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related