1

I create a fake table by:

SELECT 'B' a_name UNION ALL  
SELECT 'A' a_name

and try to JOIN with different table but I see the error:

illegal mix of collations (latin1_swedish_ci implicit) and (utf8_general_ci coercible)

I am not the administrator so I am not able to update the table collation,

Is there any work-around for this problem ?

akaribi
  • 57
  • 3
  • Possible duplicate of https://stackoverflow.com/questions/41980698/mysql-select-with-different-collation – Red Nov 06 '18 at 16:26
  • 1
    Possible duplicate of [Troubleshooting "Illegal mix of collations" error in mysql](https://stackoverflow.com/questions/3029321/troubleshooting-illegal-mix-of-collations-error-in-mysql) – Red Nov 06 '18 at 16:29
  • I changed that to `SELECT 'B' COLLATE latin1_swedish_ci AS a_name UNION ALL SELECT 'A' COLLATE latin1_swedish_ci AS a_name` but now i see "COLLATION 'latin1_general_ci' is not valid for CHARACTER SET 'utf8'" – akaribi Nov 06 '18 at 16:31
  • is there any solution to that ? – akaribi Nov 06 '18 at 16:31

1 Answers1

0

It seems like that your other (actual) table is using utf8_general_ci; while your database connection/server configuration is set for latin1_swedish_ci. It can be other way around too.

Nevertheless, we can use CONVERT() function, to change the "fake table" to use utf8 (if the other (actual) table is using utf8).

SELECT CONVERT('B' USING utf8) AS a_name UNION ALL  
SELECT CONVERT('A' USING utf8) AS a_name
Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57