MySQL 查询“不等于”不起作用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8994408/
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
query "not equal" doesn't work
提问by Linas
I have very simple query like this:
我有这样的非常简单的查询：
SELECT * FROM `all_conversations` WHERE `deleted_1` != '1';
And my deleted_1be default is nullor some user id, but for some reason this query always returns me 0 rows, i also tried <>but still no luck what could be wrong?
我的deleted_1默认值是null或某个用户 ID，但由于某种原因，此查询总是返回 0 行，我也尝试过，<>但仍然没有运气，这可能是什么问题？
EDTISo after running more querys i find out that my problems was default value of deleted_1field, it was NULLso i modified my query and now it works fine:
EDTI所以在运行更多查询后，我发现我的问题是deleted_1字段的默认值，NULL所以我修改了我的查询，现在它工作正常：
SELECT *
FROM `all_conversations`
WHERE `deleted_1` != 'NULL'
AND `deleted_1` != 23
回答by TimWolla
SELECT * FROM all_conversations WHERE deleted_1 <> 1 OR deleted_1 IS NULL
NULL values need special treatment: http://dev.mysql.com/doc/refman/5.1/en/working-with-null.html
NULL 值需要特殊处理：http: //dev.mysql.com/doc/refman/5.1/en/working-with-null.html
I'd suggest using the diamond operator (<>) in favor of !=as the first one is valid SQL and the second one is a MySQL addition.
我建议使用菱形运算符 ( <>) 来支持，!=因为第一个是有效的 SQL，第二个是 MySQL 的补充。
回答by Viktor Zeman
I recommend to use NULL-safe operator and negation
我建议使用 NULL-safe 运算符和否定
SELECT * FROM `all_conversations` WHERE NOT(`deleted_1` <=> '1');
回答by greut
Can you try this: deleted_1 is not null and deleted_1 != '1'?
你可以试试这个：deleted_1 is not null and deleted_1 != '1'？
mysql> select 0 is not null and 0 != '1', 1 is not null and 1 != '1', null is not null and null != '1';
+----------------------------+----------------------------+----------------------------------+
| 0 is not null and 0 != '1' | 1 is not null and 1 != '1' | null is not null and null != '1' |
+----------------------------+----------------------------+----------------------------------+
| 1 | 0 | 0 |
+----------------------------+----------------------------+----------------------------------+
Or this deleted_1 is null or deleted_1 != '1':
或者这个deleted_1 is null or deleted_1 != '1'：
mysql> select 0 is null or 0 != '1', 1 is null or 1 != '1', null is null or null != '1';
+-----------------------+-----------------------+-----------------------------+
| 0 is null or 0 != '1' | 1 is null or 1 != '1' | null is null or null != '1' |
+-----------------------+-----------------------+-----------------------------+
| 1 | 0 | 1 |
+-----------------------+-----------------------+-----------------------------+
It really depends on what you wanna get back.
这真的取决于你想拿回什么。
回答by indika
Try This.. Hope It will work for you
试试这个..希望它对你有用
SELECT *
FROM `all_conversations`
WHERE `deleted_1` IS NOT NULL
AND `deleted_1` <> 23
回答by Mohideen bin Mohammed
You cannot use arithmetic comparison operators such as =, <, or <> to test for NULL.
不能使用算术比较运算符（例如 =、< 或 <>）来测试 NULL。
To demonstrate this for yourself, try the following query:
要亲自演示这一点，请尝试以下查询：
mysql> SELECT 1 = NULL, 1 <> NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 <> NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
| NULL | NULL | NULL | NULL |
+----------+-----------+----------+----------+
so you have to say,
所以你必须说，
SELECT * FROM `all_conversations` WHERE `deleted_1` <> '1' and `deleted_1` is null;
your_field ISNULL explicitly since NULLcant be club with values using arithmetic operators and it has own value BOOLEAN
your_field明确为 NULL，因为NULL不能与使用算术运算符的值一起使用，并且它有自己的值BOOLEAN
回答by T Zengerink
How about removing the single quotes around the 1?
如何删除 1 周围的单引号？
SELECT * FROM `all_conversations` WHERE `deleted_1` != 1;
