mysql: why comparing a 'string' to 0 gives true?

Question

I was doing some MySQL test queries, and realized that comparing a string column with 0 (as a number) gives TRUE!

select 'string' = 0 as res; -- res = 1 (true), UNexpected! why!??!?!

however, comparing it to any other number, positive or negative, integer or decimal, gives false as expected (of course unless the string is the representation of the number as string)

select 'string' = -12 as res; -- res = 0 (false), expected
select 'string' = 3131.7 as res; -- res = 0 (false), expected
select '-12' = -12 as res; -- res = 1 (true), expected

Of course comparing the string with '0' as string, gives false, as expected.

select 'string' = '0' as res; -- res = 0 (false), expected

but why does it give true for 'string' = 0 ?

why is that?

Answer 1

MySQL automatically casts a string to a number:

SELECT '1string' = 0 AS res; -- res = 0 (false)
SELECT '1string' = 1 AS res; -- res = 1 (true)
SELECT '0string' = 0 AS res; -- res = 1 (true)

and a string that does not begin with a number is evaluated as 0:

SELECT 'string' = 0 AS res;  -- res = 1 (true)

Of course, when we try to compare a string with another string there's no conversion:

SELECT '0string' = 'string' AS res; -- res = 0 (false)

but we can force a conversion using, for example, a + operator:

SELECT '0string' + 0 = 'string' AS res; -- res = 1 (true)

last query returns TRUE because we ar summing a string '0string' with a number 0, so the string has to be converted to a number, it becomes SELECT 0 + 0 = 'string' and then again the string 'string' is converted to a number before being compared to 0, and it then becomes SELECT 0 = 0 which is TRUE.

This will also work:

SELECT '1abc' + '2ef' AS total; -- total = 1+2 = 3

and will return the sum of the strings converted to numbers (1 + 2 in this case).