用utf8_turkish_ci行到utf8_general_ci行更新表的效果？ [英] Effects of updating a table with rows from utf8_turkish_ci to utf8_general_ci?

问题描述

我无法加入某些表，因为某些表/行是utf8_general_ci，而有些是utf8_turkish_ci。因此，我不得不复制土耳其语，将其转换为一般语言，最后使用它。但是我想知道，如果我将原始表从土耳其语转换为一般表格，对我的应用程序会发生什么？我使用MySQL和PHP。

I was unable to join some tables because some of the tables/rows were utf8_general_ci and some were utf8_turkish_ci. Thus I had to dublicate the turkish one, convert it to general and finally use it. However I wonder, what will happen to my application if I convert the original table from turkish to general? I use MySQL with PHP.

这是最初的错误：排序规则（utf8_general_ci，IMPLICIT）和（utf8_turkish_ci，IMPLICIT）的非法混合'='

推荐答案

您的列的数据使用字符集存储。在这种情况下，它似乎是utf8。

Your columns' data are stored using a character set. In this case it seems to be utf8.

当您对这些列进行操作（例如，进行相等比较或排序）时，MySQL会采用归类。每列都有一个默认排序规则，它从表的默认排序规则继承而来。

When you operate upon those columns (doing, for example, equality comparisons or ordering), MySQL employs a collation. Each column has a default collation, which it inherits from the table's default collation.

索引具有列的默认排序规则，因此索引可以有效运行。

Indexes have the column's default collation baked in to them so they can function efficiently.

您可以执行归类合格的相等比较。例如，在 JOIN 中，您可以指定

You can do an equality comparison that's qualified by collation. For example, in a JOIN you can specify

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

或者也许

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

这将消除您的非法排序规则混合，而无需更改表。这可以帮助您避免您要查询的数据库更改。但是要注意，使用 COLLATE 限定词可能会破坏索引的使用。如果您有一个大表，并且依靠索引来提高性能，那么这可能无济于事。

That should eliminate your illegal mix of collations without requiring you to alter your table. This may help you avoid the database change you're asking about. But beware, using the COLLATE qualifier can defeat the use of an index. If you have a large table and you are relying on indexes for performance, this may be unhelpful.

因此，如果您更改表以更改默认排序规则，会发生什么情况？

So, what will happen if you alter your tables to change the default collation?

1. 您的数据不会更改（除非您也更改了字符集）。很好。


2. 任何带有归类列的索引都将重新生成。


3. 您的比较和顺序可能会更改。我不懂土耳其语，所以我无法告诉您可能会破坏的地方。但是，例如，西班牙语中的字母 N 和Ñ是不相同的。在西班牙语归类中， N 在Ñ之前，但是在一般归类中，它们被视为相同。土耳其语字母的某些方面可能工作原理相同，因此您的 ORDER BY 结果将不正确。

1. Your data will not change (unless you also alter the character set). That is good.
2. Any indexes involving columns with collations will be regenerated.
3. Your comparisons and orderings may change. I don't know Turkish, so I can't tell you what might break. But, for example, in Spanish the letters N and Ñ are not the same. N comes before Ñ in a Spanish collation, but in the general collation they are treated as the same. There may be some aspect of the Turkish alphabet that works the same, so your ORDER BY results will be incorrect.

但是，您可以通过在 ORDER BY 中指定 COLLATE 修饰符来解决此问题

But, you can fix that by specifying a COLLATE modifier in your ORDER BY clause.

ORDER BY (euro.village_name COLLATE utf8_turkish_ci)

这篇关于用utf8_turkish_ci行到utf8_general_ci行更新表的效果？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！