mysql 字符串<>0,mysql：为什么比较一个'字符串'到0给出true？

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?

解决方案

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).