mysql where exact match

Question

When i write this query

SELECT cd.title, cd.city FROM coupon_detail cd WHERE cd.id = 260;

return

title            city  
---------------  ------
Butterfly world  Mohali

and when i write

SELECT cd.title, cd.city FROM coupon_detail cd WHERE cd.id = '260abcxyz';

return

title            city  
---------------  ------
Butterfly world  Mohali

see the second one is wrong. i want to get the value only IF ID EXACTLY MATCH ... how to solve this. thanks for your help.

Answer 1

You could convert the id to a string so the comparison will be done exactly. You could use LIKE to cause an implicit conversion

SELECT cd.title, cd.city FROM coupon_detail cd WHERE cd.id LIKE '260abcxyz';

or alternative, you can perform the cast explicitly

SELECT cd.title, cd.city FROM coupon_detail cd WHERE CAST(cd.id AS CHAR) = '260abcxyz';

However, if all your IDs are integers, it's probably more appropriate to check these values before you try to query the database. (If the ID you're querying with isn't a number, there can't be a match anyway.)

Answer 2

This is happening because of Type Conversion in MySql. My sql treats '260abcxyz' as an integer in your query and AFAIK because first char is number MySql casts it to a number and it becomes only 260 and that's why you are getting result. If you write character first like 'abcxyz260' it will successes the comparison.

More explanation available here: Type Conversion in MySql

As a solution: you should take care that only numbers are passed in comparison and not combination of string and number.