Error in query (1267): Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='

SET SESSION default_collation_for_utf8mb4 = 'utf8mb4_unicode_ci'


The Unicode organization has been evolving the specification over the years. Here are the mappings from its "versions" to MySQL Collations:

4.0   _unicode_
5.20  _unicode_520_
9.0   _0900_

The suffix (MySQL doc):

_bin      -- just compare the bits; don't consider case folding, accents, etc
_ci       -- explicitly case insensitive (A=a) and implicitly accent insensitive (a=á)
_ai_ci    -- explicitly case insensitive and accent insensitive
_as (etc) -- accent-sensitive (etc)


Performance:

_bin         -- simple, fast
_general_ci  -- fails to compare multiple letters; eg ss=ß, so somewhat fast
...          -- slower
_900_        -- (8.0) much faster because of a rewrite

From mysql:8 default collation
collation_connection    utf8mb4_0900_ai_ci
collation_database    utf8mb4_0900_ai_ci
collation_server    utf8mb4_0900_ai_ci

So we can go with utf8mb4_0900_ai_ci
    because 1. Unicode 9.0 specification
            2. introduced in mydql 8
            3. reported faster Performance    

Step 1 - data base alter collate

ALTER DATABASE `dbname` COLLATE utf8mb4_unicode_ci

Step 2 - alter collate of table 

 ALTER TABLE tablename  COLLATE 'utf8mb4_0900_ai_ci';