blocks|key|3223114|text|最好使用以下两种方法之一：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3223115|--+Method+1.
SELECT+1
FROM+table_name
WHERE+unique_key+=+value;

--+Method+2.
SELECT+COUNT(1)
FROM+table_name
WHERE+unique_key+=+value;|code-block|syntax|javascript|3223116|第一个选择应该是没有结果或一个结果，第二个计数应该是0或1。|3223117|您使用的文档有多老？尽管您已经阅读了很好的建议，但最新的关系型数据库中的大多数查询优化器都会优化SELECT+COUNT(*)，所以尽管在理论上(和较旧的数据库)存在差异，但在实践中您应该不会注意到任何差异。|offset|length|style|CODE|3223118|entityMap^0|0|0|0|1C|F|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|T|8|@$K|U|L|V|M|N]]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

It's better to use either of the following:

<pre><code>-- Method 1.
SELECT 1
FROM table_name
WHERE unique_key = value;

-- Method 2.
SELECT COUNT(1)
FROM table_name
WHERE unique_key = value;
</code></pre>

The first alternative should give you no result or one result, the second count should be zero or one.

How old is the documentation you're using? Although you've read good advice, most query optimizers in recent RDBMS's optimize <code>SELECT COUNT(*)</code> anyway, so while there is a difference in theory (and older databases), you shouldn't notice any difference in practice.

blocks|key|293283|text|我希望根本不使用Count函数：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|293284|IF+[NOT]+EXISTS+(+SELECT+1+FROM+MyTable+WHERE+...+)
+++++<do+smth>|code-block|syntax|javascript|293285|293286|例如，如果您想在将用户插入数据库之前检查用户是否存在，则查询可能如下所示：|293287|IF+NOT+EXISTS+(+SELECT+1+FROM+Users+WHERE+FirstName+=+'John'+AND+LastName+=+'Smith'+)
BEGIN
++++INSERT+INTO+Users+(FirstName,+LastName)+VALUES+('John',+'Smith')
END|293288|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|O|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|P|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|Q|8|@]|9|@]|A|$]]|$1|J|3|K|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|L|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|M|$]]

I would prefer not use Count function at all:

<pre><code>IF [NOT] EXISTS ( SELECT 1 FROM MyTable WHERE ... )
 &lt;do smth&gt;
</code></pre>

<hr>

For example if you want to check if user exists before inserting it into the database the query can look like this:

<pre><code>IF NOT EXISTS ( SELECT 1 FROM Users WHERE FirstName = 'John' AND LastName = 'Smith' )
BEGIN
 INSERT INTO Users (FirstName, LastName) VALUES ('John', 'Smith')
END
</code></pre>

blocks|key|3223045|text|您可以使用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3223046|SELECT+1+FROM+MyTable+WHERE+<MyCondition>|code-block|syntax|javascript|3223047|如果没有符合条件的记录，则结果记录集为空。|3223048|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

You can use:

<pre><code>SELECT 1 FROM MyTable WHERE &lt;MyCondition&gt;
</code></pre>

If there is no record matching the condition, the resulted recordset is empty.

blocks|key|3223092|text|其他答案都很好，但添加LIMIT+1+(或the+equivalent，以防止检查不必要的行)也很有用。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3223093|entityMap|0|LINK|mutability|MUTABLE|url|http://en.wikipedia.org/wiki/Select_%2528SQL%2529#Limiting_result_rows^0|B|7|L|E|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@$9|O|A|P|B|C]]|D|@$9|Q|A|R|1|S]]|E|$]]|$1|F|3|-4|5|6|7|T|8|@]|D|@]|E|$]]]|G|$H|$5|I|J|K|E|$L|M]]]]

The other answers are quite good, but it would also be useful to add <code>LIMIT 1</code> (or <a href="http://en.wikipedia.org/wiki/Select_%28SQL%29#Limiting_result_rows" rel="noreferrer">the equivalent</a>, to prevent the checking of unnecessary rows.

blocks|key|288257|text|SELECT+COUNT(1)+FROM+MyTable+WHERE+...|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|288258|将遍历所有记录。这就是为什么它不适合用于记录存在的原因。|unstyled|288259|我会用|288260|SELECT+TOP+1+*+FROM+MyTable+WHERE+...|288261|在找到1条记录后，它将终止循环。|288262|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|P|8|@]|9|@]|A|$]]|$1|G|3|H|5|F|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$B|C]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|F|7|T|8|@]|9|@]|A|$]]]|N|$]]

<pre><code>SELECT COUNT(1) FROM MyTable WHERE ...
</code></pre>

will loop thru all the records. This is the reason it is bad to use for record existence.

I would use

<pre><code>SELECT TOP 1 * FROM MyTable WHERE ...
</code></pre>

After finding 1 record, it will terminate the loop.

blocks|key|3223162|text|您可以使用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3223163|SELECT+1+FROM+MyTable+WHERE...+LIMIT+1|code-block|syntax|javascript|3223164|使用select+1防止检查不必要的字段。|offset|length|style|CODE|3223165|使用LIMIT+1防止检查不必要的行。|3223166|entityMap^0|0|0|2|8|0|2|7|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|R|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|S|8|@$I|T|J|U|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|V|8|@$I|W|J|X|K|L]]|9|@]|A|$]]|$1|O|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|P|$]]

You can use:

<pre><code>SELECT 1 FROM MyTable WHERE... LIMIT 1
</code></pre>

Use <code>select 1</code> to prevent the checking of unnecessary fields.

Use <code>LIMIT 1</code> to prevent the checking of unnecessary rows.

blocks|key|3009013|text|您可以使用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3009014|SELECT+COUNT(1)+FROM+MyTable+WHERE+...+|code-block|syntax|javascript|3009015|或|3009016|WHERE+[NOT]+EXISTS+
(+SELECT+1+FROM+MyTable+WHERE+...+)|3009017|这将比SELECT+*更有效，因为您只需为每行选择值1，而不是选择所有字段。|offset|length|style|CODE|3009018|COUNT(*)和COUNT(列名)之间也有细微的区别：|3009019|3009020|COUNT(*)将计算所有行，包括nulls|unordered-list-item|3009021|COUNT(column+name)将仅计算列名为|3009022|的非null事件|3009023|entityMap^0|0|0|0|0|3|8|0|0|0|0|8|0|0|I|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|14|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|15|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|16|8|@$M|17|N|18|O|P]]|9|@]|A|$]]|$1|Q|3|R|5|6|7|19|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|1A|8|@]|9|@]|A|$]]|$1|T|3|U|5|V|7|1B|8|@$M|1C|N|1D|O|P]]|9|@]|A|$]]|$1|W|3|X|5|V|7|1E|8|@$M|1F|N|1G|O|P]]|9|@]|A|$]]|$1|Y|3|Z|5|6|7|1H|8|@]|9|@]|A|$]]|$1|10|3|-4|5|6|7|1I|8|@]|9|@]|A|$]]]|11|$]]

You can use:

<pre><code>SELECT COUNT(1) FROM MyTable WHERE ... 
</code></pre>

or

<pre><code>WHERE [NOT] EXISTS 
( SELECT 1 FROM MyTable WHERE ... )
</code></pre>

This will be more efficient than <code>SELECT *</code> since you're simply selecting the value 1 for each row, rather than all the fields.

There's also a subtle difference between COUNT(*) and COUNT(column name):

<ul>
<li><code>COUNT(*)</code> will count all rows, including nulls</li>
<li><code>COUNT(column name)</code> will only count non null occurrences of column name</li>
</ul>

blocks|key|3009214|text|其他选项：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3009215|SELECT+CASE
++++WHEN+EXISTS+(
++++++++SELECT+1
++++++++FROM+[MyTable]+AS+[MyRecord])
++++THEN+CAST(1+AS+BIT)+ELSE+CAST(0+AS+BIT)
END|code-block|syntax|javascript|3009216|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Other option:

<pre><code>SELECT CASE
 WHEN EXISTS (
 SELECT 1
 FROM [MyTable] AS [MyRecord])
 THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END
</code></pre>

blocks|key|288279|text|我是这样使用的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|288280|IF+(EXISTS+(SELECT+TOP+1+FROM+Users+WHERE+FirstName+=+'John'),+1,+0)+AS+DoesJohnExist|code-block|syntax|javascript|288281|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I'm using this way:
<pre><code>IF (EXISTS (SELECT TOP 1 FROM Users WHERE FirstName = 'John'), 1, 0) AS DoesJohnExist
</code></pre>

While reading some SQL Tuning-related documentation, I found this:

<code>SELECT COUNT(*)</code> :

<ul>
<li>Counts the number of rows.</li>
<li>Often is improperly used to verify the existence of a record.</li>
</ul>

Is <code>SELECT COUNT(*)</code> really that bad? 

What's the proper way to verify the existence of a record?

SQL: How to properly check if a record exists

在阅读一些与SQL调优相关的文档时，我发现了以下内容：SELECT COUNT(*)：计算行数。经常被错误地用于验证记录的存在。SELECT COUNT(*)真的那么糟糕吗？验证记录存在的正确方法是什么？

问SQL:如何正确检查记录是否存在
EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL:如何正确检查记录是否存在EN

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL:如何正确检查记录是否存在
EN