Change entire db's collation and solve illegal mix of collations

Question

I'm having a problem when doing LIKE '' queries in mySQL

These are my variables

• character_set_client utf8
• character_set_connection utf8
• character_set_database latin1
• character_set_filesystem binary
• character_set_results utf8
• character_set_server latin1
• character_set_system utf8
• character_sets_dir C:\xampp\mysql\share\charsets\

1. How could I change db collation? And tables? I have:

   • Some tables as MyISAM with latin1_swedish_ci
   • Some tables as InnoDB with utf8_general_ci

   I guess I should convert every table InnoDB with utf8_general_ci

2. I guess DB is set to latin1_swedish_ci so I should change this also. Doing the process from answer 1) will avoid the Illegal mix of collations error in mySql right? Without changing any line of code...?

Answer 1

Charset and collation can be set to utf8_general_ci no matter which engine you are using. So, no need to change the MyISAM to InnoDB if you don't need, you can just change the collation.

Charset and collation is set per column. You can alter a table as a whole, but that really goes and alters each column. Remember that the charset and collation set on a table is really just a default for new columns, and the charset and collation set on the database is just a default for new tables.

Lastly, you correctly have the client, connection and results charsets all set to utf8. This is essential, otherwise MySQL will transcode going in or out. Besure that your client code explicitly sets these when setting up the a database connection.