Searching for an integer value in a varchar field in MySQL

Question

I have a table named user like below:

id || email
---------------------------------
1  || [email protected]
---------------------------------
2  || [email protected]
---------------------------------

Why is this:

[email protected]

result of this search?

SELECT * FROM user WHERE email = 1

Answer 1

Because MySQL decides to convert the email to an integer. The rules are to convert leading characters to a number, until the characters are not valid numbers.

Here is a simple example:

select (case when '1abc' = 1 then 'a' else 'b' end)