MySQL的二进制比较犯规利用指数 [英] mysql binary comparison doesnt use index

问题描述

我有以下查询：

EXPLAIN EXTENDED SELECT *
FROM (
`photo_data`
)
LEFT JOIN `deleted_photos` ON `deleted_photos`.`photo_id` = `photo_data`.`photo_id`
WHERE photo_data.photo_id = 'UKNn'
AND `deleted_photos`.`photo_id` IS NULL

我很遗憾不得不使用二进制比较这photo_id（正在被交给我从不同的外部服务）。所以，我避免uknn'被拉出datbase而不是'UKNn'的。

I unfortunately have to use binary to compare this photo_id (which is being handed to me from a different outside service). So that I avoid 'uknn' being pulled out of the datbase instead of 'UKNn'.

问题是，当我做了解释，我看到使用二进制不使用索引。如果我拿出二进制它使用photo_id索引。有没有一种方法，以便能够使用二进制选项，并使用一个索引呢？

The problem is that when I do the explain, i see the use of binary doesn't use the index. If I take out binary it uses the index for photo_id. Is there a way to be able to use the binary option and use an index with it?

推荐答案

MySQL使用索引的列的排序规则。用非二进制排序索引不是二进制查找有用的，因为该顺序可能会不同。

MySQL uses the collation of the column for the index. An index with a non-binary collation isn't useful for a binary lookup since the order might be different.

您可以在列本身更改为二进制排序：

You could change the column itself to binary collation:

ALTER TABLE YourTable MODIFY
   YourColumn VARCHAR(4)
   CHARACTER SET latin1
   COLLATE latin1_bin;

那么股指将是一个二进制查找有用的。

Then the index would be useful for a binary lookup.

这篇关于MySQL的二进制比较犯规利用指数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！