mysql二进制比较不使用索引
我有以下查询：
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”而不是“UKNn”。
问题是，当我进行解释时，我看到二进制的使用不使用索引。如果我取出二进制文件，它会使用 photo_id 的索引。有没有办法能够使用二元选项并使用索引？
I have the following query:
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
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'.
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
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:
Then the index would be useful for a binary lookup.
如果您只需要以区分大小写的方式比较字段值 - 您可以专门更改该字段的排序规则。
或者
If you need just to compare field value in case-sensitive manner - you could change collation for that field specifically.
or