MySQL: 在查询中使用Collate - 会有任何副作用吗？

11 浏览
0 Comments

MySQL: 在查询中使用Collate - 会有任何副作用吗？

我的OpenCart表排序是utf8_bin，不幸的是，我无法搜索带有重音的产品名称。我在Google上搜索了一下，发现排序必须是utf8_general_ci，才能进行重音兼容和不区分大小写的搜索。

如果我在搜索查询中添加排序声明会怎么样？

SELECT * 
FROM  `address` 
COLLATE utf8_general_ci
LIMIT 0 , 30

这会有什么（不好的）副作用吗？我看到有关索引和性能的问题。还是完全安全的？

0

3 答案

0 Comments

MySQL中使用COLLATE在查询中的副作用是什么？

COLLATE是用于字符串比较的排序规则，与存储数据所使用的字符编码（character encoding）几乎没有关系，主要作用是定义字符集中字符的排序和比较顺序。有几点需要注意：

1. 改变COLLATE可能会导致字符编码的改变，MySQL会正确地重新编码值以适应新的字符集，但是超过列大小的值将会被截断。

2. 二进制排序规则的实际优点是速度快，字符串比较非常简单/快速。虽然在一般情况下，使用二进制排序规则的索引可能无法产生预期的排序结果，但对于精确匹配可能会有用。

3. 当有多个操作数时，可能会存在歧义。例如，对于以下查询语句：SELECT x FROM T WHERE x = 'Y'; 应该使用列x的排序规则，还是字符串字面量'Y'的排序规则？由于x和'Y'都有排序规则，那么哪个排序规则优先？

4. 如果更改字段的排序规则，ORDER BY（也包括WHERE）无法使用索引，因此可能效率低下。

5. 强制指定的排序规则与列的默认排序规则相同，不会有任何性能影响。然而，如果强制指定的排序规则与列的字符集不同，MySQL将不得不对列的值进行转码，这会影响性能。

在使用COLLATE时需要注意上述问题，特别是对于索引和排序的影响，以及字符编码的变化可能导致的副作用。

0
0 Comments

MySQL在使用不同排序规则的查询时，可能会影响查询性能，尤其是那些使用索引的查询。下面是一个简单的测试：

mysql> create table aaa (a1 varchar(100) collate latin1_general_ci, tot int);
insert into aaa values('test1',3) , ('test2',4), ('test5',5);
mysql> create index aindex on aaa (a1);

你可以看到，当使用另一种排序规则搜索a1时，MySQL停止使用a1上的索引，这对你来说可能是一个很大的问题。

为了确保索引在查询中被使用，你可能需要将列的排序规则更改为最常用的排序规则。

这种情况只会发生在使用不同排序规则的查询上。如果你的查询不使用任何（默认）排序规则，我认为MySQL仍然能够使用索引。

0
0 Comments

当在MySQL查询中使用Collate时，可能会出现一些副作用。为了解决这个问题，可以考虑改变列的定义，使用ALTER TABLE语句修改列的字符集和排序规则。如果有多个列需要修改，可以在同一个ALTER语句中进行操作，以提高效率。

如果由于某种原因无法使用ALTER语句进行修改，可以通过在SELECT语句中使用Collate来改变排序规则。在没有过滤条件的情况下，通过改变测试用例来说明问题。例如，如果原始查询是查找城市为'San Jose'的记录，那么通过使用Collate utf8_general_ci可以找到'San José'的记录。

如果可能存在"combining accents"，可以考虑使用utf8_unicode_ci排序规则。这样做可以避免一些特殊字符的问题。需要注意的是，在使用Collate后可能会影响到索引的使用。在上述第二个SELECT语句中，使用INDEX(city)将无效。因此，通过一次性的ALTER操作来修改列的排序规则可以避免在每次SELECT时的性能损失，但是ALTER本身的操作可能会比较耗时。

通过修改列的定义或使用Collate来改变排序规则可以解决MySQL查询中Collate可能带来的副作用问题。

0