oracle 一种测试表行是否存在的有效方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9026184/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
an efficient way to test if a table row exists
提问by dcernahoschi
I'm trying to find the most efficient way to determine if a table row exists.
我试图找到确定表行是否存在的最有效方法。
I have in mind 3 options:
我想到了 3 个选项：
SELECT EXISTS(SELECT 1 FROM table1 WHERE some_condition);
SELECT 1 FROM table1 WHERE some_condition LIMIT 0,1;
SELECT COUNT(1) FROM table1 WHERE some_condition;
SELECT EXISTS(SELECT 1 FROM table1 WHERE some_condition);
SELECT 1 FROM table1 WHERE some_condition LIMIT 0,1;
SELECT COUNT(1) FROM table1 WHERE some_condition;
It seems that for MySQL the first approach is more efficient: Best way to test if a row exists in a MySQL table
对于 MySQL 来说，第一种方法似乎更有效： Best way to test a row exists in a MySQL table
Is it true in general for any database?
对于任何数据库来说，一般情况下都是这样吗？
UPDATE:
更新：
I've added a third option.
我添加了第三个选项。
UPDATE2:
更新2：
Let's assume the database products are mysql, oracle and sql-server.
假设数据库产品是mysql、oracle和sql-server。
回答by Luis
I would do
我会做
SELECT COUNT(1) FROM table 1 WHERE some_condition.
But I don't think it makes a significant difference unless you call it a lot (in which case, I'd probably use a different strategy).
但我不认为它会产生显着差异，除非你经常调用它（在这种情况下，我可能会使用不同的策略）。
回答by tbone
If you mean to use as a test if AT LEAST ONE row exists with some condition (1 or 0, true or false), then:
如果您打算用作测试是否存在具有某种条件（1 或 0，真或假）的至少一行，则：
select count(1) from my_table where ... and rownum < 2;
Oracle can stop counting after it gets a hit.
Oracle 可以在命中后停止计数。
回答by Mike
Exists is faster because it will return the number of results that match the subquery and not the whole result.
Exists 更快，因为它将返回匹配子查询的结果数而不是整个结果。
回答by Eugen Rieck
The different methods have different pros and cons:
不同的方法有不同的优缺点：
SELECT EXISTS(SELECT 1 FROM table1 WHERE some_condition);
might be the fastest on MySQL, but
可能是 MySQL 上最快的，但是
SELECT COUNT(1) FROM table 1 WHERE some_condition
as in @Luis answer gives you the count.
正如@Luis 中的回答给你计数。
More to the point I recommend you take a look at your business logic: Very seldom is it necessary to just see if a row exists, more often you will want to
更重要的是，我建议您查看您的业务逻辑：很少需要只查看一行是否存在，更多时候您会想要
- either usethese rows, so just do the select and handle the 0-rows case
- or you will want to changethese rows, in which case just do your update and check mysql_affected_rows()
- If you want to INSERT a row if it doesn't already exist, take a look at
INSERT .. ON DUPLICATE KEYorREPLACE INTO
- 要么使用这些行，所以只需选择并处理 0 行的情况
- 或者您需要更改这些行，在这种情况下只需进行更新并检查 mysql_affected_rows()
- 如果您想插入不存在的行，请查看
INSERT .. ON DUPLICATE KEY或REPLACE INTO
回答by thermz
The exists function is defined generally in SQL, it isn't only as a MySQL function : http://www.techonthenet.com/sql/exists.phpand I usually use this function to test if a particular row exists.
存在函数通常在 SQL 中定义，它不仅作为 MySQL 函数：http: //www.techonthenet.com/sql/exists.php，我通常使用此函数来测试特定行是否存在。
However in Oracle I've seen many times the other approach suggested before:
但是，在 Oracle 中，我已经多次看到之前建议的其他方法：
SELECT COUNT(1) FROM table 1 WHERE some_condition.
