MySQL：整数值和字符串字段与索引的比较 [英] MySQL: comparison of integer value and string field with index

问题描述

表 a_table 在 string_column 上有索引。

我有一个查询：

Table a_table has index on string_column.
I have a query:

SELECT * FROM a_table WHERE string_column = 10;

我使用 EXPLAIN 来查找没有索引使用。

为什么？你能帮我解决MySQL文档链接吗？

I used EXPLAIN to find that no indexes are used.
Why? Could you help me with MySQL documentation link?

更新：沙箱（SQL小提琴）

推荐答案

关键是如果数据库不能使用索引必须在比较的表侧进行转换。

The essential point is that the index cannot be used if the database has to do a conversion on the table-side of the comparison.

除此之外，数据库总是转换字符串 - >数字，因为这是确定性的方式（否则1可以如评论中所述，转换为'01'，'001'。

Besides that, the DB always coverts Strings -> Numbers because this is the deterministic way (otherwise 1 could be converted to '01', '001' as mentioned in the comments).

因此，如果我们比较两个似乎让您感到困惑的案例：

So, if we compare the two cases that seem to confuse you:

-- index is used
EXPLAIN SELECT * FROM a_table WHERE int_column = '1';

DB将字符串'1'转换为数字1，然后执行查询。它最终在双方都有int，所以它可以使用索引。

The DB converts the string '1' to the number 1 and then executes the query. It finally has int on both sides so it can use the index.

-- index is NOT used. WTF?
EXPLAIN SELECT * FROM a_table WHERE str_column = 1;

再次，它将字符串转换为数字。但是，这次它必须转换存储在表中的数据。实际上，您正在执行搜索，例如 cast（str_column as int）= 1 。这意味着，您不再搜索索引数据，DB 无法使用索引。

Again, it converts the string to numbers. However, this time it has to convert the data stored in the table. In fact, you are performing a search like cast(str_column as int) = 1. That means, you are not searching on the indexed data anymore, the DB cannot use the index.

请进一步查看此内容详情：

Please have a look at this for further details:

• http://use-the-index-luke.com/sql/where-clause/obfuscation/numeric-strings


• http://use-the-index-luke.com/sql/where-clause/函数/不区分大小写搜索

• http://use-the-index-luke.com/sql/where-clause/obfuscation/numeric-strings
• http://use-the-index-luke.com/sql/where-clause/functions/case-insensitive-search

这篇关于MySQL：整数值和字符串字段与索引的比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！