SQL:如何正确检查记录是否存在 [英] Sql: How to properly check if a record exists

问题描述

阅读一些 SQL Tuning 文档，我发现了这一点:

Select count(*):
-计算行数
-通常不正确地用于验证记录的存在

Select count(*)真的那么糟糕吗?

验证记录是否存在的正确方法是什么?

解决方案

最好使用以下两种方法之一:

-- Method 1.
SELECT 1
FROM table_name
WHERE unique_key = value;

-- Method 2.
SELECT COUNT(1)
FROM table_name
WHERE unique_key = value;

第一个选择应该没有结果或一个结果，第二个计数应该为零或一个.

您使用的文档多大了?尽管您已经阅读了很好的建议，但是无论如何，最新的RDBMS的大多数查询优化器仍然会优化SELECT COUNT(*)，因此，尽管理论(和较旧的数据库)有所不同，但您在实践中应该不会注意到任何差异.

Reading some SQL Tuning documentation I found this:

Select count(*) :
- Counts the number of rows
- Often is improperly used to verify the existence of a record

Is Select count(*) really that bad?

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

解决方案

It's better to use either of the following:

-- Method 1.
SELECT 1
FROM table_name
WHERE unique_key = value;

-- Method 2.
SELECT COUNT(1)
FROM table_name
WHERE unique_key = value;

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 SELECT COUNT(*) anyway, so while there is a difference in theory (and older databases), you shouldn't notice any difference in practice.

这篇关于SQL:如何正确检查记录是否存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！