-2

I have a utf8mb4 character that needs to be saved in a column in MySQL. All the solutions pointed towards either removing the 4-byte character or changing the collation or charset of my table.

I have also noticed that Rails(ActiveRecord) safely converted to \U0001F4A1 and stored it in the database. I want to know how ActiveRecord is doing it and how I can do this

akxer
  • 132
  • 9

1 Answers1

1

Short answer: You cannot do that. If you want to save 4 byte character in mysql you need to use UTF8mb4

Longer answer: In a hacky way, you can save characters like that with serialization. Say your model is Foo and column where you saving 4 byte character is bar, you will have something like:

class Foo < ActiveRecord::Base
  serialize :bar
end

This IS hack though. It will work, but you will need to serialize all the other existing records (otherwise you're asking for trouble).

Been playing with ideas like that while trying to suppport emojis on legacy UTF8 database and unfortunately, other than opting for UTF8mb4, there's no other good way to go about it.

Another alternative if you're only interested in dealing with emojis is use something like emojimmy

TomD
  • 781
  • 4
  • 17
  • I did convert my table's charset to utf8mb4 but that didn't change anything, i'm still getting the same error as before, serialize did work, however as you said, it's kind of a hack, so I won't do it unless totally necessary – akxer Jun 19 '17 at 16:24
  • @akabhirav Have you also updated your database.yml file to use utf8mb4 encoding instead of utf8? As per http://blog.arkency.com/2015/05/how-to-store-emoji-in-a-rails-app-with-a-mysql-database/ – TomD Jun 20 '17 at 08:13
  • I changed the charset for just one table, is it possible to do changes in database.yml for a single table – akxer Jun 20 '17 at 08:28
  • Not really without hacking it through.You should be able to mix and match encodings so just try to change encoding in database.yml and see what happens! If you've got already estabilished database with thousands of records, your best bet is to use something like emojimmy. If it's relatively new and clean, it might be worth starting over with utf8mb4 – TomD Jun 20 '17 at 09:29
  • You can't simply change the table definition, you need to also state that the client has utf8mb4, not just utf8. – Rick James Jun 30 '17 at 22:46