UTF-8 vs Latin1 mysql，未在utf-8上使用的索引 [英] UTF-8 vs Latin1 mysql, indexes not used on utf-8

问题描述

我尝试用UTF-8和Latin1字符集创建我的mysql表.当我使用Latin1时，将使用我的索引，而当我选择/限制记录时，则不使用UTF-8索引.字符集是否缺少导致这种情况发生的东西?

I have tried creating my mysql tables with both UTF-8 and Latin1 Char sets. When I use Latin1 my indexes are used, when I use UTF-8 indexes are not used when selecting/limiting records. Is there something I am missing with the Char sets that causes this to happen?

欢呼

柯

推荐答案

仅当表达式的排序规则与索引列的排序规则匹配时，才可以使用索引.

Indexes can be used only when the expression's collation matches that on the indexed column.

如果表达式的COERCIBILITY低于列的表达式(即2)，则将列的排序规则强制转换为表达式的排序规则，并且不使用索引.

If the expression's COERCIBILITY is lower than that of the column (that is 2), the column's collation is casted to that of the expression, and the index is not used.

通常，文字具有4的COERCIBILITY和具有3的用户变量，所以这应该不是问题.

Normally, literals have COERCIBILITY of 4 and user variables that of 3, so this should be not a problem.

但是，如果在JOIN或UNION中混合使用不同的排序规则，则无法保证转换顺序.

However, if you mix different collations in a JOIN or UNION, the cast order is not guaranteed.

在这种情况下，您应该为要转换的列提供显式排序规则(很可能是要将latin1转换为UTF8)，这应该是要转换为的列的排序规则:

In this case you should provide explicit collation to the column your are casting (most probably, you want to cast latin1 to UTF8), and this should be the collation of the column you are casting to:

SELECT  *
FROM    utf_table
JOIN    latin_table
ON      utf_column = latin_column COLLATE UTF8_GENERAL_CI

这篇关于UTF-8 vs Latin1 mysql，未在utf-8上使用的索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！