CODEWITHSUNDEEP
mysqlruby-on-railsutf-8
Zachary Wright
Zachary Wright

Reputation: 24070

How to have a column specific character encoding in Ruby on Rails and MySQL?

I have a new table I am adding to a large Ruby on Rails project. Our database currently uses the UTF8 character encoding. However, I need to support emoji's in one column of this new table.

In order to do that, that column needs to be in UTF8mb4 encoding, since the base UTF8 encoding in MySQL doesn't support emoji's.

I can change things on the mysql side by executing this SQL:

execute "ALTER TABLE table_name CHANGE column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin"

The problem is this doesn't fix the issue, I actually have to change the 'encoding' option in the database.yml file from utf8 to utf8mb4.

The problem with this is when my test run loads the database schema, suddenly it can't because characters in utf8mb4 use more bytes than they do in utf8, and we have existing indexes that are too long to fit into the new utf8mb4 byte requirement.

I don't really want to edit all of our existing indexes that break this, our database is pretty large.

Is there no way in Rails to use a character encoding on a table or column level? Do I have to update the database.yml file and therefore force my entire database to use utf8mb4?

Upvotes: 3

Views: 1662

Answers (1)

Rick James
Rick James

Reputation: 142278

  • Change VARCHAR(255) to VARCHAR(191), for any column being indexed, to get around index limit, or
  • Update to MySQL 5.7, which does not have the issue.

If you need to discuss more, please provide SHOW CREATE TABLE and the specific connection parameters, perhaps found in application_controller.rb.

Upvotes: 2

Related Questions