MySQL显示奇怪的结果

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?

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';

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.

"male" is 0! So you keep getting records where age is 0. To really understand what's happening, just do:

select user_name, age, age='male' from tabela1512823699024883;

Your 3rd column will be 1 for the records containing '0' age, and '0' for the records containing non-zero age.

The Problem here is that whenever you try to compare a string to integer column, the value will be cast to the data type of column. Same case will happen with float , big int, tiny int etc.

What ever value is passed in comparison first being cast for that particular data type by mysql engine

Here is the test result of the query casting few values.

select cast('' AS UNSIGNED), cast('anything' AS UNSIGNED), cast(NULL AS UNSIGNED), cast(35.56 AS UNSIGNED), cast(NOW() AS UNSIGNED), cast(1 AS UNSIGNED), cast('Test' AS BINARY), cast('' AS BINARY);

SELECT * FROM `test_type` WHERE flt = 'test'

the flt is of float type column.