mysql: why comparing a 'string' to 0 gives true?
I was doing some MySQL test queries, and realized that comparing a string column with
0 (as a number) gives
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?
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).