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
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%'
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_databaseto the same value.
I tested a little bit around and found out that this sets
And if I use this instead, it sets it to
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
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