WHERE - NULL 在 SQLite 中不起作用吗? [英] WHERE - IS NULL not working in SQLite?
问题描述
这是一个奇怪的:
我可以从 SQLite 过滤 NOT NULLS，但不能过滤 NULLS:
I can filter on NOT NULLS from SQLite, but not NULLS:
这有效:
SELECT * FROM project WHERE parent_id NOT NULL;
这些不会:
SELECT * FROM project WHERE parent_id IS NULL;
SELECT * FROM project WHERE parent_id ISNULL;
SELECT * FROM project WHERE parent_id NULL;
全部返回:
您的查询语法有问题(查询不是执行) ...
There is a problem with the syntax of your query (Query was not executed) ...
更新:
我正在使用 PHP- 通过我的代码与 ezSQl 并使用 PHPLiteAdmin 界面
I am doing this with PHP- through my code with ezSQl and using the PHPLiteAdmin interface
使用 PHPLiteAdmin 演示，这个表达式有效 - 所以现在我怀疑我的 PHP 的 SQLite 存在版本问题?那可能吗?这个表达式不是一直有效吗?
Using the PHPLiteAdmin demo, this expression works- so now I'm suspecting a version issue with my PHP's SQLite? Could that be? Wasn't this expression always valid?
更新 2:
当我使用 ezSQL 从 PHP 运行代码时，PHP 警告是:
When I run the code from PHP using ezSQL, the PHP warning is:
PHP 警告:SQL 逻辑错误或缺少数据库
PHP Warning: SQL logic error or missing database
有没有办法从 PHP 中获取更多信息?这是非常不透明和奇怪的，特别是因为 CLI 中的相同语句工作正常......
Is there a way to get more information out of PHP? This is maddeningly opaque and weird, especially because the same statement in the CLI works fine...
更新 3
我拥有的唯一其他可能的线索是 CLI 无法读取我用 PHP 创建的数据库，反之亦然.我得到:
The only other possible clue I have is that the databases that I create with PHP cannot be read by the CLI, and vice versa. I get:
错误:文件已加密或不是数据库
Error: file is encrypted or is not a database
所以这里肯定有两种 SQlite 口味.(请参阅) 不过，为什么无效的声明??
So there's definitly two SQlite flavors butting heads here. (See this) Still, why the invalid statment??
更新 4
好的，我想我已经将问题追溯到了罪魁祸首，如果不是原因的话——我用 PHP ezSQL 创建的数据库是 IS NULL 语句失败的数据库.如果我使用 PHP 的 SQLite3 类创建数据库，该语句可以正常工作，而且，我可以从 CLI 访问数据库，而 ezSQL 创建的数据库给出了 file is encrypted 错误.
OK I think I've traced the problem to the culprit, if not the reason- The DB I created with PHP ezSQL is the one where the IS NULL statement fails. If I create the DB using PHP's SQLite3 class, the statement works fine, and moreover, I can access the DB from the CLI, whereas ezSQL created DB gave the file is encrypted error.
所以我对 ezSQL 代码进行了一些挖掘 - 我发现它使用 PDO 方法，而不是较新的 SQLite3 类.也许这就是 - 我不会在上面浪费更多时间......
So I did a little digging into ezSQL code- Off the bat I see it uses PDO methods, not the newer SQLite3 class. Maybe that's something- I'm not gonna waste further time on it...
无论如何，我找到了我的解决方案，即避开 ezSQL，而只使用 PHP 的 SQLite3 类.
In any case, I've found my solution, which is to steer clear of ezSQL, and just use PHPs SQLite3 class.
推荐答案
a IS b 和 a IS NOT b 是一般形式，其中 a 和 b 是表达式.
a IS b and a IS NOT b is the general form where a and b are expressions.
这通常只出现在 a IS NULL 和 a IS NOT NULL 情况下.还有 ISNULL 和 NOTNULL(也是 NOT NULL)运算符，它们分别是前面表达式的简写(它们只接受一个单操作数).
This is generally only seen in a IS NULL and a IS NOT NULL cases. There are also ISNULL and NOTNULL (also NOT NULL) operators which are short-hands for the previous expressions, respectively (they only take in a single operand).
SQLite 查询语言:表达式中介绍了 SQLite 表达式中理解的 SQL.
The SQL understood in SQLite expressions is covered in SQLite Query Language: Expressions.
如果使用 CLI，请先确保(之前的)语句以 ; 终止.
Make sure that (previous) statements have been terminated with a ; first if using the CLI.
这些都可以用来否定空匹配":
These are all valid to negate a "null match":
expr NOT NULL
expr NOTNULL
expr IS NOT NULL
这些都是有效的匹配空值":
These are all valid to "match null":
expr ISNULL
expr IS NULL
由于上述所有结构本身都是表达式，因此否定也是有效的(例如 NOT (expr NOT NULL) 等价于 expr IS NULL).
Since all of the above constructs are themselves expressions the negations are also valid (e.g. NOT (expr NOT NULL) is equivalent to expr IS NULL).
快乐编码.
布丁中的证据:
SQLite version 3.7.7.1 2011-06-28 17:39:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table x (y int null);
sqlite> select * from x where y isnull;
sqlite> select * from x where y notnull;
sqlite> select * from x where y not null;
sqlite> select * from x where y is null;
sqlite> select * from x where y is not null;
sqlite>
这篇关于WHERE - NULL 在 SQLite 中不起作用吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
