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

Question

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?

Workaround, you can serialize value before save it to utf-8 database. — Alex Kojin
– Alex Kojin, Commented Nov 19, 2016 at 16:34

Rick James · Accepted Answer · 2016-11-19 21:34:58Z

2

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.

answered Nov 19, 2016 at 21:34

Rick James

144k15 gold badges144 silver badges254 bronze badges

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

1 Comment

Rick James Over a year ago

More details and more options: mysql.rjweb.org/doc.php/limits#767_limit_in_innodb_indexes

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related