mysql:为什么将“字符串"与 0 进行比较会给出正确的结果? [英] mysql: why comparing a 'string' to 0 gives true?

问题描述

我正在做一些 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!??!?!

但是，将它与任何其他数字，正数或负数，整数或小数进行比较，会按预期给出 false(当然，除非字符串是将数字表示为字符串)

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

当然，将字符串与 '0' 作为字符串进行比较，如预期的那样给出错误.

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

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

但是为什么它对 'string' = 0 给出 true ?

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

这是为什么?

推荐答案

MySQL 自动将字符串转换为数字:

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)

并且不以数字开头的字符串被评估为 0:

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)

最后一个查询返回 TRUE 因为我们将字符串 '0string' 与数字 0 相加，所以字符串必须转换为数字，它变成 SELECT 0 + 0 = 'string' 和然后再次将字符串 'string' 转换为数字，然后再与 0 进行比较，然后变为 SELECT 0 = 0 为 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.

这也可以:

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

并将返回转换为数字的字符串的总和(在本例中为 1 + 2).

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

这篇关于mysql:为什么将“字符串"与 0 进行比较会给出正确的结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！