无操作符的Where子句 [英] Where clause without operator

问题描述

尝试回答此问题，我发现了我认为错误的语法

While trying to answer this question, I found a syntax which I would have considered wrong

select * from table where area and block

我尝试了一个sqlFiddle，发现它是有效的.

I tried an sqlFiddle, and found it was valid.

但是我一直无法找到它的工作方式(我发现的所有文档都包括运算符)...

But I've been unable to find how it works (all the doc I found was including operators)...

尝试一些sql小提琴此处

Made some try on an sql fiddle here

对于可为null的int字段，它似乎消除"了null和0个结果，对于不可为null的int字段似乎为"0"结果.

It seems to "eliminate" null and 0 results for an nullable int field, or 0 result for a non nullable int field.

它看起来还保留着varchar值...其中包含int值(而不是'0')！

It also looks like it keeps varchar values... which contains int values (but not '0') !

即使我不认为使用这样的语法也是一个好主意，但如果有人对它的管理方式有任何解释，我将不胜感激.

Even if I don't think it's a good idea (at all) to use such syntax, I would appreciate if someone had any explanation on how this is managed...

推荐答案

从MySQL 逻辑运算符的文档:

MySQL将任何非零，非NULL值评估为TRUE.例如，以下语句均评估为TRUE:

MySQL evaluates any nonzero, non-NULL value to TRUE. For example, the following statements all assess to TRUE:

mysql> SELECT 10 IS TRUE;<br/>
-> 1
mysql> SELECT -10 IS TRUE;<br/>
-> 1
mysql> SELECT 'string' IS NOT NULL;<br/>
-> 1

似乎它将任何非零的int字段都评估为TRUE.同样，任何包含数字的varchar字段都将被视为数字字段.我检查了您的小提琴，发现'0'的varchar字段未评估为TRUE.除非它是IS NOT NULL表达式的一部分，否则任何不能转换为数字的varchar字段都不会求值为TRUE.因此:

It seems that it evaluates any non-zero int field as TRUE. Also, any varchar field which contains a number gets treated as numeric field. I checked in your fiddle that a varchar field of '0' does not evaluate to TRUE. And any varchar field which cannot be converted to a number does not evaluate to TRUE unless it is part of an IS NOT NULL expression. Hence:

mysql> SELECT 'string' IS TRUE;<br/>
-> 0

通过测试您的小提琴，我发现以下两个查询的行为相同:

From testing your fiddle, I found the following 2 queries to behave identically:

select * from test where ttt;
select * from test where ttt IS TRUE;

我没有任何确定的证明或源代码，但是看来MySQL实际上在上面的第一个查询中实际上是在上使用运算符，即使它被省略了.

I don't have any definitive proof or source code, but it appears that MySQL actually is using an operator on the first query above even if it be omitted.

这篇关于无操作符的Where子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！