Effects of updating a table with rows from utf8_turkish_ci to utf8_general_ci?

Question

I was unable to join some tables because some of the tables / rows were utf8_general_ci and some of them were utf8_turkish_ci. Thus, I had to publish the Turkish language, convert it into a common language, and finally use it. However, I wonder what will happen to my application if I convert the source table from Turkish to general? I am using MySQL with PHP.

This was the initial error: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_turkish_ci,IMPLICIT) for operation '='

Answer 1

Your column data is stored using a character set. In this case, it seems to be utf8.

When you work with these columns (for example, to compare comparisons or ordering), MySQL uses sorting. Each column has a default sort, which it inherits from the default sort in the table.

Indexes have default column sorting baked on them so they can function efficiently.

You can do an equality comparison, which is determined by sorting. For example, in JOIN you can specify

 ON (turkish.village_name COLLATE utf8_general_ci) = euro.village_name 

or maybe

 ON turkish.village_name = (euro.village_name COLLATE utf8_turkish_ci) 

This should eliminate your illegal combination of sorts without requiring you to modify the table. This can help you avoid changing the database you are asking for. But be careful, using a COLLATE qualifier can defeat using an index. If you have a large table and you rely on performance indices, this can be useless.

So what happens if you change your tables to change the default sort?

• Your data will not change (unless you change the character set). It's good.
• Any indexes containing sorted columns will be restored.
• Your comparisons and orders are subject to change. I don’t know Turkish, so I can’t say what could break. But, for example, in Spanish, the letters N and C do not match. N precedes in the Spanish comparison, but in general sorting they are considered the same. Perhaps some aspect of the Turkish alphabet works the same way, so your ORDER BY results will be wrong.

But you can fix this by specifying the COLLATE modifier in your ORDER BY .

 ORDER BY (euro.village_name COLLATE utf8_turkish_ci)