My tables have the collation utf8mb4_unicode_ci, but the data itself is utf8mb4_general_ci and now I like to know how I can convert it into unicode?

I used this query to convert all my latin tables to utf8:

ALTER TABLE ' . $table_name . ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci

Then I reminded a security hole in wordpress regarding truncated blog comments through bypassing the 3 bytes limit and I decided to convert all tables to utf8mb4:

ALTER TABLE ' . $table_name . ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

Everything seemed to work, but after a while I received through my own security check, that some content has been truncated:

Also mit Nummer/Abe?
Was wird�€�s denn?

Also mit Nummer/Abe?
Was wird

As you can see someone was able to send a latin/iso quotation mark and mysql truncated the string.

So I checked my mysql collaction settings:

SHOW variables WHERE variable_name LIKE '%coll%' OR variable_name LIKE '%char%'


character_set_client: utf8mb4
character_set_connection: utf8mb4
character_set_database: utf8mb4
character_set_filesystem: binary
character_set_results: utf8mb4
character_set_server: latin1
character_set_system: utf8
character_sets_dir: /usr/share/mysql/charsets/
collation_connection: utf8mb4_general_ci
collation_database: utf8mb4_unicode_ci
collation_server: latin1_swedish_ci

As I'm not able to change server collations I asked my hosting company and they said:

Settings on the server will be overwritten by your database settings, so no change is necessary. But you should set collation_connection and collation_database to the same value.

I tested a little bit around and found out that this sets collation_connection to utf8mb4_general_ci:

mysqli_set_charset('utf8mb4', $this->db_connect_id);

And if I use this instead, it sets it to utf8mb4_unicode_ci:

mysql_query('SET collation_connection = @@collation_database;');

Now I could use this setting, but then all existing special chars are scrambled. Only new posts will be saved and displayed correctly. This means my tables have the correct utf8mb4_unicode_ci collation, but the data itself seems to be formatted as utf8mb4_general_ci. I do not know why, maybe because the connection itself while calling CONVERT TO CHARACTER SET was set to utf8mb4_general_ci?!

Anyway... So how can I solve this issue? How can I convert the utf8mb4_general_ci data into utf8mb4_unicode_ci so I'm able to use utf8mb4_unicode_ci as collation_connection?

  • I don#t know of ot os too late. But you should have two databases on the same server, one with the old charset and one with the new inclusive all tables, with he right and then you connect to the databases with the correct charset in the connection string and transfer so the data.
    – nbk
    Apr 2, 2020 at 17:24
  • mgutt: something doesn't seem right about this description. Not sure what you mean by "the data itself is utf8mb4_general_ci". Do you mean the connection collation? Because the data in the table is utf8mb4_unicode_ci, and both of those collations (as well as any utf8*, utf16*, or utf32* collation), are Unicode. The "utf" prefix means Unicode. The "unicode" vs "general" part of the collation name refers to the sorting, not the encoding of the characters. Meaning, there should be no difference between utf8mb4_unicode_ci and utf8mb4_general_ci in terms of storing characters. Apr 10, 2020 at 15:10
  • 1
    Also, you said you first converted to utf8 before utf8mb4. Did you check all of the data after the initial convert to utf8? All characters that work in utf8 should also work in utf8mb4. Is it possible the truncation happened in the initial convert to utf8 that wasn't notice until after the second convert? Also, can you check the actual bytes of that truncated value to see if there aren't additional characters being hidden by a \x00 null string terminator? Not sure how MySQL handles displaying those, but SQL Server won't show anything after a \x00 even though the data is there. Apr 10, 2020 at 15:17
  • See this for black diamonds: stackoverflow.com/questions/38363566/…
    – Rick James
    Apr 11, 2020 at 6:42
  • A character set (latin1 / utf8 / utf8mb4) determines the encoding. The collation determines the sort order.
    – Rick James
    Apr 11, 2020 at 6:43


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.