为什么“WHERE column"给出的结果与“WHERE column IS NOT NULL AND column <>''`? [英] Why does `WHERE column` give a different result than `WHERE column IS NOT NULL AND column <> ''`?

问题描述

我有两列，area 和 block，其中 area 是 int 类型，block 是 varchar.

I have two columns, area and block, where area is an int type and block is a varchar.

现在我正在编写两个查询:

Now I am writing two queries:

select * from table where area and block;

select * from table where area is not null and area <> ''
                      and block is not null and block <> '';

我在运行这些查询时得到了不同的结果集.它们之间可能有什么区别?

I am getting different result sets on running these queries. What could possibly be the difference between them?

我认为上一个返回 area 和 block 都存在的地方，第二个应该返回相同的东西.

I thought the upper one returns where area and block both are present and the second one should return the same thing.

推荐答案

你的第二个查询的行为是正常的，虽然 is not null 是多余的，因为 任何与 null 的算术比较都会给出 null 作为结果.

The behavior of your second query is normal, though is not null is redundant because any arithmetic comparison with null gives null as the result.

这里的怪癖是在第一个查询中.MySQL 在隐式类型转换方面非常慷慨(无论好坏).它处理没有 比较函数或运算符的裸表达式通过将计算表达式的值隐式转换为布尔值来作为有效谓词 - 在 MySQL 的世界中，只是一个整数.

The quirk here is in the first query. MySQL is pretty generous (for better or for worse) with implicit type conversion. It treats a bare expression with no comparison function or operator as a valid predicate by implicitly converting the value of the evaluated expression to a boolean—which, in the world of MySQL, is just an integer.

您的 block 列是一个 varchar.MySQL 的 关于类型转换的文档页面 显示了如何在这个当作为裸表达式谓词提供时，列将被隐式转换为布尔值(读取:整数):

Your block column is a varchar. MySQL's documentation page on type conversion shows how values in this column will be implicitly converted to boolean (read: integer) values when supplied as a bare expression predicate:

以下示例说明了字符串到数字的转换比较操作:

The following examples illustrate conversion of strings to numbers for comparison operations:

mysql> SELECT 1 > '6x';
        -> 0
mysql> SELECT 7 > '6x';
        -> 1
mysql> SELECT 0 > 'x6';
        -> 0
mysql> SELECT 0 = 'x6';
        -> 1

这里暗示但没有明确说明的是，如果字符串以数字开头，MySQL猜测"必须是您想要的数字；否则，它猜测"零.因此，如果 block 中的值恰好是 'a'，则第一个查询将隐式转换为 0 并过滤掉该行因为 WHERE area and 0 永远不是真的.如果您来自一种在布尔上下文中将非空字符串视为真的语言，这是非常出乎意料的！但是，嘿，它是 MySQL……过了一段时间，你就会学会放弃期望.

What's implied here, but not stated outright, is that if the string starts with a number, MySQL "guesses" that must be the number you wanted; otherwise, it "guesses" zero. So if the value in block happens to be 'a', the first query is going to implicitly cast that to 0 and filter out the row because WHERE area and 0 is never true. This is pretty unexpected if you're coming from a language that treats non-empty strings as true in a boolean context! But hey, it's MySQL... After a while, you learn to let go of expectations.

第二个查询不进行任何隐式转换，因为 <> 运算符实际上是在测试不等式；'a' 显然不等于 ''，也不等于 null，所以两个谓词都为真，行没有从结果集.

The second query doesn't do any implicit casting because the <> operator is actually testing inequality; 'a' is clearly not equal to '', nor is it null, so both predicates are true and the row is not filtered from the result set.

这篇关于为什么“WHERE column"给出的结果与“WHERE column IS NOT NULL AND column <>''`?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！