Setting default collation of a table is not working

jlevitas16's profile image jlevitas16 posted 4 years ago in General Permalink

I created a database and set the default collation of the database to utfmf8_unicode_ci. I then exported empty tables that had a mix of utf8_unicode_ci and latin1_swedish_ci collation. When I go into each table, I want to change the default collation and all of the field's collation to utfmf8_unicode_ci. When I choose this option under Table->Options->Default Collation and click the Convert data checkbox, all of the fields change to utfmf8_unicode_ci, but when I save the changes, they all convert to utfmf8_general_ci. I can individually change each fiedl to utfmf8_unicode_ci, but that will take a long time. Why is this happening? Screen shots attached.

3 attachment(s):
  • original
  • switch-before-save
  • after-save
ansgar's profile image ansgar posted 4 years ago Permalink

I can confirm that. HeidiSQL sends such a query:

ALTER TABLE `ville`
    COLLATE='utf8mb4_unicode_ci',
    CONVERT TO CHARSET utf8mb4;

But the table has utf8mb4_general_ci afterwards.

If not this way, then how should the SQL look like in order to achieve a collation change in a table?

ansgar's profile image ansgar posted 4 years ago Permalink

Even appending a collation to the CONVERT clause does not change the column's collation:

ALTER TABLE `ville`
    COLLATE='utf8mb4_unicode_ci',
    CHARSET utf8mb4 COLLATE 'utf8mb4_unicode_ci';

https://dev.mysql.com/doc/refman/5.7/en/alter-table.html

Please login to leave a reply, or register at first.