COLLATION 'utf8_bin' is not valid for CHARACTER SET 'utf8mb4'

Hkm Sadek 0
Viewed 28

I am trying to add emoji like in my application. In order to make it work, I have to use charset : 'utf8mb4' in database connection. But then my other search query doesn't work and throughs error like this

select id, full_name, profile_pic from users where school_id = 1 and exists (select * from groupmembers where (group_id = '110') and (users.id = groupmembers.user_id)) and full_name like '%d%' COLLATE utf8_bin - ER_COLLATION_CHARSET_MISMATCH: COLLATION 'utf8_bin' is not valid for CHARACTER SET 'utf8mb4

How can I make both works together? I am using adonis.js framework and it uses knex query/

adonis.js knex.js mysql
1 Answers

Collations are specific to a character set. If your character set is utf8mb4, then you could use collation utf8mb4_bin but not utf8_bin.

This will become a bit more confusing someday, because MySQL has the intention to change the name of utf8mb4 to utf8. Cf. https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8.html

You can check all the collations allowed by this query:

select collation_name 
from information_schema.collation_character_set_applicability 
where character_set_name = @@character_set_connection;
Bill Karwin0
Related