MySQL: comparison of integer value and string fiel

问题:

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?

Updated: Sandbox (SQL Fiddle)

回答1:

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:

• 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