MySQL integer comparison ignores trailing alpha characters

So lets just say I have a table with just an ID is an int. I have dis开发者_JAVA技巧covered that running:

SELECT *
FROM table 
WHERE ID = '32anystring';

Returns the row where id = 32. Clearly 32 != '32anystring'.

This seems very strange. Why does it do this? Can it be turned off? What is the best workaround?

It is common behavior in most programming languages to interpret leading numerals as a number when converting a string to a number.

There are a couple of ways to handle this:

Use prepared statements, and define the placeholder where you are putting the value to be of a numeric type. This will prevent strings from being put in there at all.

Check at a higher layer of the application to validate input and make sure it is numeric.

Use the BINARY keyword in mysql (I'm just guessing that this would work, have never actually tried it as I've always just implemented a proper validation system before running a query) -

SELECT *
FROM table 
WHERE BINARY ID = '32anystring';

You need to read this

http://dev.mysql.com/doc/refman/5.1/en/type-conversion.html

1. When you work on, or compare two different types, one of them will be converted to the other.
2. MySQL conversion of string->number (you can do '1.23def' * 2 => 2.46) parses as much as possible of the string as long as it is still a valid number. Where the first letter cannot be part of a number, the result becomes 0