[Maria/MySQL] Illegal mix of collations for operation '=' Error Troubleshooting

I met Illegal mix of collations for operation '=' exception when executing join select query.

It is because of this that difference of collation table or column.

1. Check Table Collation

SELECT TABLE_NAME
     , TABLE_COLLATION
  FROM information_schema.`TABLES` T
 WHERE T.table_schema = '<DB_NAME>'
   AND T.table_name = '<TABLE_NAME>'
;

2. Change Table Collation

ALTER TABLE <DB_NAME>.<TABLE_NAME> DEFAULT CHARACTER SET utf8 COLLATE 'utf8_general_ci';

3. Check Column Collation

SELECT TABLE_NAME
     , COLUMN_NAME
     , DATA_TYPE
     , COLLATION_NAME
  FROM information_schema.`COLUMNS`
  WHERE TABLE_SCHEMA = '<DB_NAME>'
    AND TABLE_NAME = '<TABLE_NAME>'
;

It is related with character type columns like VARCHAR, CHAR, and so on.

4. Change column collation

ALTER TABLE <DB_NAME>.<TABLE_NAME> modify <COLUMN_NAME> <DATA_TYPE> CHARACTER SET utf8 COLLATE utf8_general_ci;