测试 MySQL 表中是否存在行的最佳方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1676551/
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
Best way to test if a row exists in a MySQL table
提问by Bernard Chen
I'm trying to find out if a row exists in a table. Using MySQL, is it better to do a query like this:
我试图找出表中是否存在一行。使用 MySQL，是否更好地执行这样的查询：
SELECT COUNT(*) AS total FROM table1 WHERE ...
and check to see if the total is non-zero or is it better to do a query like this:
并检查总数是否非零，或者最好执行如下查询：
SELECT * FROM table1 WHERE ... LIMIT 1
and check to see if any rows were returned?
并检查是否返回了任何行？
In both queries, the WHERE clause uses an index.
在这两个查询中，WHERE 子句都使用索引。
回答by Chris Thompson
You could also try EXISTS:
你也可以尝试EXISTS：
SELECT EXISTS(SELECT * FROM table1 WHERE ...)
and per the documentation, you can SELECTanything.
根据文档，你可以做SELECT任何事情。
Traditionally, an EXISTS subquery starts with SELECT *, but it could begin with SELECT 5 or SELECT column1 or anything at all. MySQL ignores the SELECT list in such a subquery, so it makes no difference.
传统上，EXISTS 子查询以 SELECT * 开头，但它可以以 SELECT 5 或 SELECT column1 或任何内容开头。MySQL 会忽略此类子查询中的 SELECT 列表，因此没有区别。
回答by Laurent W.
I have made some researches on this subject recently. The way to implement it has to be different if the field is a TEXT field, a non unique field.
我最近在这方面做了一些研究。如果字段是 TEXT 字段、非唯一字段，则实现它的方式必须有所不同。
I have made some tests with a TEXT field. Considering the fact that we have a table with 1M entries. 37 entries are equal to 'something':
我用 TEXT 字段做了一些测试。考虑到我们有一个包含 1M 个条目的表。37 个条目等于“某物”：
SELECT * FROM test WHERE texte LIKE '%something%' LIMIT 1withmysql_num_rows(): 0.039061069488525s. (FASTER)SELECT count(*) as count FROM test WHERE text LIKE '%something%: 16.028197050095s.SELECT EXISTS(SELECT 1 FROM test WHERE text LIKE '%something%'): 0.87045907974243s.SELECT EXISTS(SELECT 1 FROM test WHERE text LIKE '%something%' LIMIT 1): 0.044898986816406s.
SELECT * FROM test WHERE texte LIKE '%something%' LIMIT 1与mysql_num_rows()：0.039061069488525s。（快点）SELECT count(*) as count FROM test WHERE text LIKE '%something%: 16.028197050095s。SELECT EXISTS(SELECT 1 FROM test WHERE text LIKE '%something%')：0.87045907974243s。SELECT EXISTS(SELECT 1 FROM test WHERE text LIKE '%something%' LIMIT 1): 0.044898986816406s。
But now, with a BIGINT PK field, only one entry is equal to '321321' :
但是现在，对于 BIGINT PK 字段，只有一个条目等于 '321321' ：
SELECT * FROM test2 WHERE id ='321321' LIMIT 1withmysql_num_rows(): 0.0089840888977051s.SELECT count(*) as count FROM test2 WHERE id ='321321': 0.00033879280090332s.SELECT EXISTS(SELECT 1 FROM test2 WHERE id ='321321'): 0.00023889541625977s.SELECT EXISTS(SELECT 1 FROM test2 WHERE id ='321321' LIMIT 1): 0.00020313262939453s. (FASTER)
SELECT * FROM test2 WHERE id ='321321' LIMIT 1与mysql_num_rows()：0.0089840888977051s。SELECT count(*) as count FROM test2 WHERE id ='321321': 0.00033879280090332s。SELECT EXISTS(SELECT 1 FROM test2 WHERE id ='321321'): 0.00023889541625977s。SELECT EXISTS(SELECT 1 FROM test2 WHERE id ='321321' LIMIT 1): 0.00020313262939453s。（快点）
回答by jaltek
A short example of @ChrisThompson's answer
@ChrisThompson 回答的简短示例
Example:
例子：
mysql> SELECT * FROM table_1;
+----+--------+
| id | col1 |
+----+--------+
| 1 | foo |
| 2 | bar |
| 3 | foobar |
+----+--------+
3 rows in set (0.00 sec)
mysql> SELECT EXISTS(SELECT 1 FROM table_1 WHERE id = 1);
+--------------------------------------------+
| EXISTS(SELECT 1 FROM table_1 WHERE id = 1) |
+--------------------------------------------+
| 1 |
+--------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT EXISTS(SELECT 1 FROM table_1 WHERE id = 9);
+--------------------------------------------+
| EXISTS(SELECT 1 FROM table_1 WHERE id = 9) |
+--------------------------------------------+
| 0 |
+--------------------------------------------+
1 row in set (0.00 sec)
Using an alias:
使用别名：
mysql> SELECT EXISTS(SELECT 1 FROM table_1 WHERE id = 1) AS mycheck;
+---------+
| mycheck |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)
回答by shihab mm
In my research, I can find the result getting on following speed.
在我的研究中，我可以找到跟随速度的结果。
select * from table where condition=value
(1 total, Query took 0.0052 sec)
select exists(select * from table where condition=value)
(1 total, Query took 0.0008 sec)
select count(*) from table where condition=value limit 1)
(1 total, Query took 0.0007 sec)
select exists(select * from table where condition=value limit 1)
(1 total, Query took 0.0006 sec)
回答by Arth
I feel it is worth pointing out, although it was touched on in the comments, that in this situation:
我觉得值得指出的是，尽管在评论中有所提及，但在这种情况下：
SELECT 1 FROM my_table WHERE *indexed_condition* LIMIT 1
Is superior to:
优于：
SELECT * FROM my_table WHERE *indexed_condition* LIMIT 1
This is because the first query can be satisfied by the index, whereas the second requires a row look up (unless possibly all the table's columns are in the index used).
这是因为索引可以满足第一个查询，而第二个查询需要行查找（除非可能所有表的列都在使用的索引中）。
Adding the LIMITclause allows the engine to stop after finding any row.
添加LIMIT子句允许引擎在找到任何行后停止。
The first query should be comparable to:
第一个查询应该类似于：
SELECT EXISTS(SELECT * FROM my_table WHERE *indexed_condition*)
Which sends the same signals to the engine (1/* makes no difference here), but I'd still write the 1 to reinforce the habit when using EXISTS:
它向引擎发送相同的信号（1/* 在这里没有区别），但我仍然会写 1 以加强使用时的习惯EXISTS：
SELECT EXISTS(SELECT 1 FROM my_table WHERE *indexed_condition*)
It may make sense to add the EXISTSwrapping if you require an explicit return when no rows match.
EXISTS如果在没有行匹配时需要显式返回，则添加包装可能是有意义的。
回答by Fatih Karatana
Suggest you not to use Countbecause count always makes extra loads for db use SELECT 1and it returns 1if your record right there otherwise it returns null and you can handle it.
建议您不要使用，Count因为 count 总是会为数据库使用带来额外的负载，如果您的记录就在那里SELECT 1，它会返回1，否则它会返回 null 并且您可以处理它。
回答by Zaxter
At times it is quite handy to get the auto increment primary key (id) of the row if it exists and 0if it doesn't.
有时，获取行的自动递增主键 ( id)非常方便（如果存在，0如果不存在）。
Here's how this can be done in a single query:
以下是如何在单个查询中完成此操作：
SELECT IFNULL(`id`, COUNT(*)) FROM WHERE ...
回答by jaywon
回答by davek
For non-InnoDB tables you could also use the information schema tables:
对于非 InnoDB 表，您还可以使用信息模式表：
回答by Felix
I'd go with COUNT(1). It is faster than COUNT(*)because COUNT(*)tests to see if at least one column in that row is != NULL. You don't need that, especially because you already have a condition in place (the WHEREclause). COUNT(1)instead tests the validity of 1, which is always valid and takes a lot less time to test.
我会去的COUNT(1)。它比COUNT(*)因为COUNT(*)测试该行中是否至少有一列是 != NULL更快。你不需要那个，特别是因为你已经有了一个条件（WHERE子句）。COUNT(1)而是测试 的有效性1，它总是有效的，而且测试的时间要少得多。
