default database collation not respected while importing

Question

In my database, the collation was originally utf8_general_ci. However, I noticed that utf8_unicode_ci is necessary because of better sorting accuracy.

So I exported all database using phpmyadmin and checked that the word "COLLATION" does not appear in the exported sql file (except for only 2 times in one table where it is set to binary) so generally this script is collation agnostic and should not imply any specific collation when importing but use database default.

After dropping all tables, the database collation was changed to utf8_unicode_ci and then the import script was run from phpmyadmin. But as a result, all tables and all columns are shown again with utf8_general_ci collation (and sorting is incorrect). Why?? And what to do to change it?

P.S. The export/import script contains commented line at the beginning:

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

I don't know if it has any impact while importing, but after opening mysql console, the command show variables like 'collation_connection'shows COLLATION_CONNECTION as cp852_general_ci. However, in phpmyadmin->variables the variable 'collation_connection' is set to utf8_general_ci. But there is no way to change it.

Answer 1

That happens because the database export is setting the character set on every table, and such a clause comes with a default collation that depends on the character set, not on the collation of your connection. utf8_general_ci is the default collation for utf8.

You'll have to convert your tables with something like ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci; or edit your database export if this is affordable.

As for the MySQL console: the command-line client is pretty much broken on Windows. It'll never support, display or read Unicode, and you're getting a per-connection collation for that client that matches your Windows so-called OEM character set for your locale. This is a Windows misfeature that's difficult to workaround in portable software. PHPMyAdmin uses a web server and doesn't suffer from this problem. I advise you to use a UNIX-like operating system like GNU/Linux for any serious work in any case, not just for this reason. As an added benefit, MySQL, Apache and your whole application stack perform better on Linux.