用于检查SQLite3中是否存在行的有效查询

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?

还有什么是返回值，是假（bool）还是0（int）还是NULL？

2 个解决方案

#1

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.

虽然文档并不暗示它，但显然主要的sqlite dev（Richard Hipp）已在邮件列表中确认为您提供EXISTS短路。

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().

SQLite中的查询规划器虽然不是很出色，但它足够聪明，只要它看到EXISTS（）内部查询的第一行就可以停止并返回true。

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.

如果您对可移植性感到紧张，可以添加限制。我怀疑大多数DB会为你提供相同的短路。

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.

如果您不需要记录中的某些内容，则选择1是可接受的做法，尽管您选择的内容无论如何都不应该重要。

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.

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

#2

Again, quoting from the documentation:

再次，引用文档：

The EXISTS operator always evaluates to one of the integer values 0 and 1. If executing the SELECT statement specified as the right-hand operand of the EXISTS operator would return one or more rows, then the EXISTS operator evaluates to 1. If executing the SELECT would return no rows at all, then the EXISTS operator evaluates to 0.

EXISTS运算符始终求值为整数值0和1之一。如果执行指定为EXISTS运算符的右侧操作数的SELECT语句将返回一行或多行，则EXISTS运算符的计算结果为1.如果执行SELECT根本不会返回任何行，那么EXISTS运算符的计算结果为0。

As to whether or not using EXISTS is more efficient than, say, using count(*), that may depend on the size of the table and whether the table has an index. Try an EXPLAIN on both queries to benchmark (or just time each of them).

至于使用EXISTS是否比使用count（*）更有效，这可能取决于表的大小以及表是否具有索引。尝试对两个查询进行EXPLAIN以进行基准测试（或者仅对每个查询进行计时）。

#1