Short:
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?
Long:
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:
Before:
Also mit Nummer/Abe?
Was wird�€�s denn?
ISO-8859-1
After:
Also mit Nummer/Abe?
Was wird
UTF-8
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%'
Result:
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_connectionandcollation_databaseto 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?
utf8mb4_general_ci". Do you mean the connection collation? Because the data in the table isutf8mb4_unicode_ci, and both of those collations (as well as anyutf8*,utf16*, orutf32*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 betweenutf8mb4_unicode_ciandutf8mb4_general_ciin terms of storing characters.utf8beforeutf8mb4. Did you check all of the data after the initial convert toutf8? All characters that work inutf8should also work inutf8mb4. Is it possible the truncation happened in the initial convert toutf8that 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\x00null string terminator? Not sure how MySQL handles displaying those, but SQL Server won't show anything after a\x00even though the data is there.