MySQL showing strange result

Question

I have a table called tabela1512823699024883 that looks like this:

On which I run query like this:

SELECT * FROM tabela1512823699024883 WHERE `age` = 'male'

This query does not make sense, because age column is int type and I'm looking for string value in my query, but MySQL still returns no empty rows whatsoever. Here is what query returned: So age row does not contains male value in neither rows returned. How can this be possible?

Answer 1

Same issue will occur with casting a string to an integer type.

SELECT CAST('male' as SIGNED); #0
#or
SELECT CAST('male' as UNSIGNED); #0

However if a number is supplied within the string.

SELECT CAST('1234male' as UNSIGNED); #1234
#and
SELECT CAST('male1234' as UNSIGNED); #0

To resolve the issue use BINARY on the column, which will also cause the textual value to become case-sensitive.

SELECT * FROM tabela1512823699024883 WHERE BINARY `age` = 'male';

Alternatively to utilize a case-insensitive BINARY criteria CONVERT() the value to the desired character-set and then specify a case-inventive collation. [sic]

SELECT * FROM tabela1512823699024883 WHERE CONVERT(BINARY `age` USING utf8) COLLATE utf8_general_ci = 'male';

Answer 2

Try this simple query:

SELECT 'male' + 0

This seems not to make sense. However the query executes and returns a value of 0. This is because of implicit type conversion. String value 'male' is converted to 0 and then added to 0 and thus 0 is returned .

The same thing happens with your query. 'male' in converted to 0 when compared with a field of type int.