用于检查 SQLite3 中是否存在行的有效查询 [英] Valid query to check if row exists in SQLite3

问题描述

这是检查表中是否存在行的最佳(最有效)方法吗?

SELECT EXISTS(SELECT 1 FROM myTbl WHERE u_tag="tag");//表是...//创建表 myTbl(id INT PRIMARY KEY, u_tag TEXT);

还有它的返回值是什么，是 false(bool) 还是 0(int) 还是 NULL?

解决方案

虽然文档并没有暗示它，但显然主要的 sqlite dev (Richard Hipp) 拥有 在邮件列表中确认 EXISTS 为您短路.><块引用>

SQLite 中的查询计划器虽然并不出色，但足够聪明知道它可以在看到第一行后立即停止并返回 true来自 EXISTS() 内部的查询.

因此您提出的查询将是最有效的:

SELECT EXISTS(SELECT 1 FROM myTbl WHERE u_tag="tag");

如果您对便携性感到紧张，可以添加限制.不过，我怀疑大多数 DB 都会为您提供相同的短路.

SELECT EXISTS(SELECT 1 FROM myTbl WHERE u_tag="tag" LIMIT 1);

如果您不需要记录中的某些内容，则选择 1 是公认的做法，尽管无论如何您选择的内容都无关紧要.

在您的标签字段上放置一个索引.如果不这样做，对不存在的标签的查询将执行全表扫描.

EXISTS 声明它将返回 1 或 0，而不是 null.

Is this the best(most efficient) way to check if a row exists in a table?

SELECT EXISTS(SELECT 1 FROM myTbl WHERE u_tag="tag");
// Table is...
// CREATE TABLE myTbl(id INT PRIMARY KEY, u_tag TEXT);

Also what is the return value for this, is it false(bool) or 0(int) or NULL?

解决方案

Though the documentation does not imply it, apparently the primary sqlite dev (Richard Hipp) has confirmed in the mailing list that EXISTS short circuits for you.

The query planner in SQLite, while not brilliant, is smart enough to know that it can stop and return true as soon as it sees the first row from the query inside of EXISTS().

So the query you proposed will be the most efficient:

SELECT EXISTS(SELECT 1 FROM myTbl WHERE u_tag="tag");

If you were nervous about portability, you could add a limit. I suspect most DBs will offer you the same short circuit however.

SELECT EXISTS(SELECT 1 FROM myTbl WHERE u_tag="tag" LIMIT 1);

Selecting 1 is the accepted practice if you don't need something from the record, though what you select shouldn't really matter either way.

Put an index on your tag field. If you do not, a query for a non-existent tag will do a full table scan.

EXISTS states that it will return 1 or 0, not null.

这篇关于用于检查 SQLite3 中是否存在行的有效查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！