MySQL SQL WHERE 条件不等于？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6156979/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL WHERE condition is not equal to?
提问by Frank Vilea
Is it possible to negate a where clause?
是否可以否定 where 子句？
e.g.
例如
DELETE * FROM table WHERE id != 2;
回答by Praveen Lobo
You can do like this
你可以这样做
DELETE FROM table WHERE id NOT IN ( 2 )
OR
或者
DELETE FROM table WHERE id <> 2
As @Frank Schmitt noted, you might want to be careful about the NULL values too. If you want to delete everything which is not 2(including the NULLs) then add OR id IS NULLto the WHERE clause.
正如@Frank Schmitt 所指出的，您可能也想小心 NULL 值。如果您想删除所有不是2（包括 NULL）的所有内容，请添加OR id IS NULL到 WHERE 子句中。
回答by Frank Schmitt
Your question was already answered by the other posters, I'd just like to point out that
其他发帖人已经回答了您的问题，我只想指出
delete from table where id <> 2
(or variants thereof, not id = 2 etc) will not delete rows where id is NULL.
（或其变体，不是 id = 2 等）不会删除 id 为 NULL 的行。
If you also want to delete rows with id = NULL:
如果您还想删除 id = NULL 的行：
delete from table where id <> 2 or id is NULL
回答by Fosco
delete from table where id <> 2
edit: to correct syntax for MySQL
编辑：纠正 MySQL 的语法
回答by Magicianeer
You could do the following:
您可以执行以下操作：
DELETE * FROM table WHERE NOT(id = 2);
回答by Brandon
Use <>to negate the where clause.
使用<>否定where子句。
回答by Nicholas Carey
Look back to formal logic and algebra. An expression like
回顾形式逻辑和代数。像这样的表达
A & B & (D | E)
may be negated in a couple of ways:
可以通过几种方式否定：
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 )
显而易见的方法：
!( A & B & ( D | E ) )上面也可以重述，你只需要记住逻辑表达式的一些属性：
!( A & B )相当于(!A | !B)。!( A | B )相当于(!A & !B)。!( !A )相当于 (A)。
将 NOT (!) 分布在它适用的整个表达式中，反转运算符并消除双重否定：
!A | !B | ( !D & !E )
So, in general, any where clause may be negated according to the above rules. The negation of this
所以，一般来说，根据上述规则，任何 where 子句都可以否定。否定这个
select *
from foo
where test-1
and test-2
and ( test-3
OR test-4
)
is
是
select *
from foo
where NOT( test-1
and test-2
and ( test-3
OR test-4
)
)
or
或者
select *
from foo
where not test-1
OR not test-2
OR ( not test-3
and not test-4
)
Which is better? That's a very context-sensitive question. Only you can decide that.
哪个更好？这是一个非常上下文敏感的问题。只有你能决定。
Be aware, though, that the use of NOT can affect what the optimizer can or can't do. You might get a less than optimal query plan.
但是请注意，NOT 的使用会影响优化器能做什么或不能做什么。您可能会得到一个不太理想的查询计划。
回答by JNK
WHERE id <> 2should work fine...Is that what you are after?
WHERE id <> 2应该工作正常......这是你所追求的吗？
回答by dkruythoff
Yes. If memory serves me, that should work. Our you could use:
是的。如果没记错的话，那应该可行。我们你可以使用：
DELETE FROM table WHERE id <> 2
回答by trusha
Best solution is to use
最好的解决方案是使用
DELETE FROM table WHERE id NOT IN ( 2 )
回答by user2956206
I was just solving this problem. If you use <> or is not in on a variable, that is null, it will result in false. So instead of <> 1, you must check it like this:
我只是在解决这个问题。如果您使用 <> 或不在变量中，即为 null，则结果为 false。因此，您必须像这样检查它，而不是 <> 1：
AND (isdelete is NULL or isdelete = 0)
