如何对 where 子句进行否定?
例如:
DELETE * FROM table WHERE id != 2;
如何对 where 子句进行否定?
例如:
DELETE * FROM table WHERE id != 2;
DELETE FROM table WHERE id NOT IN ( 2 )
或者
DELETE FROM table WHERE id <> 2
正如@Frank Schmitt指出的那样,您可能也需要小心处理NULL值。如果您想删除所有不是2
(包括NULL)的内容,则应将OR id IS NULL
添加到WHERE子句中。
!= NULL
不起作用的人,这里有一个一般性的评论:“您不能使用算术比较运算符(如=、<或<>)来测试NULL。”(来自MySQL文档)。这意味着您必须使用 IS NOT NULL
。 - Byson其他回答者已经回答了你的问题,我只想指出
delete from table where id <> 2
(或其变体,不包括id = 2等)将不会删除id为NULL的行。
如果您还想删除id = NULL的行:
delete from table where id <> 2 or id is NULL
DELETE * FROM table WHERE NOT(id = 2);
delete from table where id <> 2
编辑:为了纠正 MySQL 的语法。
<>
来否定where子句。A & B & (D | E)
可能会有两种方式对其进行否定:
The obvious way:
!( A & B & ( D | E ) )
The above can also be restated, you just need to remember some properties of logical expressions:
!( A & B )
is the equivalent of (!A | !B)
.!( A | B )
is the equivalent of (!A & !B)
.!( !A )
is the equivalent of (A).Distribute the NOT (!) across the entire expression to which it applies, inverting operators and eliminating double negatives as you go along:
!A | !B | ( !D & !E )
select *
from foo
where test-1
and test-2
and ( test-3
OR test-4
)
是
select *
from foo
where NOT( test-1
and test-2
and ( test-3
OR test-4
)
)
或者
select *
from foo
where not test-1
OR not test-2
OR ( not test-3
and not test-4
)
哪个更好?这是一个非常依赖上下文的问题。只有您自己才能决定。
但要注意,使用 NOT 可能会影响优化器的能力。您可能会得到一个不太优化的查询计划。
WHERE id <> 2
应该可以正常工作...这是你想要的吗?
最佳解决方案是使用
DELETE FROM table WHERE id NOT IN ( 2 )
DELETE FROM table WHERE id <> 2
我刚刚解决了这个问题。如果你在一个空变量上使用 <> 或 is not in,它会返回false。所以,你必须像这样检查它,而不是使用 <> 1:
AND (isdelete is NULL or isdelete = 0)