Why MySQL "WHERE" clause approximation: retrieves values even the condition is not met

2259 views mysql
5

I have a table which primary key is numeric and auto-incremented.

When I run a query such as:

SELECT * FROM my_table where id = '1a';

The query returns the row with the primary key set to "1".

I was not aware of this behavior, is it possible to prevent it? I was expecting this WHERE clause to retrieve nothing since the id is "1" and not "1a". It is behaving like it was a LIKE clause.

answered question

It is because MySQL does implicit typecasting of '1a' to integer when comparing to an integer column/field

2 Answers

2

automatically converts strings to numbers, and just takes the leading characters that are digits. You could instead explicitly cast the ID to a string:

SELECT * FROM my_table where CAST(id AS CHAR) = '1a';

posted this
8

MySQL implicitly converts a String literal to int while comparing with an int column.

You should really fix your application code (eg: PHP), and properly typecast to (int) before using them in a query. Ideally, your application should not have been inputting string values to compare against an integer field.

Now still, if you don't have control over input value, an approach can be to check if the value is numeric or not, and use it accordingly for comparison. Adapting a sargable approach from https://dba.stackexchange.com/q/89760/160363

SELECT * FROM my_table 
WHERE id = CASE WHEN CONCAT('','1a'*1) = '1a' THEN '1a' ELSE NULL END;

posted this

Have an answer?

JD

Please login first before posting an answer.