mysql索引int和string区别_MySQL：整数值和字符串字段与索引的比较

Table a_table has index on string_column.

I have a query:

SELECT * FROM a_table WHERE string_column = 10;

I used EXPLAIN to find that no indexes are used.

Why? Could you help me with MySQL documentation link?

解决方案

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.

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';

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;

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: