SQLite处理NULL

SELECT * FROM tableName WHERE colName != 'contents'

现在与我的预期相反,这似乎与其内容为“NULL”的colName不匹配.看看how SQLite handles NULLs并且特别是这个条件“null OR true”对于SqlLite是正确的我认为我的查询足以为colName选择NULL为NULL的行.我必须错误地理解这一点.任何人都可以为我阐明这一点吗？我可以用

SELECT * FROM tableName WHERE (colName != 'contents' OR colName IS NULL)

但我不认为我必须这样做.

谢谢

解决方法

The IS and IS NOT operators work like = and != except when one or both of the operands are NULL. In this case,if both operands are NULL,then the IS operator evaluates to 1 (true) and the IS NOT operator evaluates to 0 (false). If one operand is NULL and the other is not,then the IS operator evaluates to 0 (false) and the IS NOT operator is 1 (true). It is not possible for an IS or IS NOT expression to evaluate to NULL. Operators IS and IS NOT have the same precedence as =.

我自己测试了这个：

sqlite> create table temp( dataColumn,nullColumn );
sqlite> insert into temp( dataColumn,nullColumn ) values ( 'test',NULL );
sqlite> select * from temp where nullColumn != 'test';
sqlite> select * from temp where nullColumn IS NOT 'test';
test|
sqlite> select * from temp where dataColumn IS NOT 'test';
sqlite>

从更多的玩法来看,当你使用它来比较NULL的东西时,似乎！=运算符将评估为NULL：

sqlite> select ( 'contents' != NULL );

sqlite> select ( 'contents' == NULL );

sqlite> select ( 'contents' IS NULL );
0
sqlite> select ( 'contents' IS NOT NULL );
1

大概这就是为什么你没有得到你期望的行为 – 你实际上是在说WHERE NULL！=’contents’,它的计算结果为NULL,并且不满足WHERE子句

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!