在MySQL表中测试行是否存在的最佳方法

Question

我正在尝试找出表中是否存在某行。使用MySQL，以下哪种查询方式更好：

SELECT COUNT(*) AS total FROM table1 WHERE ...

检查总数是否为非零，还是最好像这样查询：

SELECT * FROM table1 WHERE ... LIMIT 1

并且检查是否返回了任何行？

在两个查询中，WHERE子句使用了索引。

- Bernard Chen

Answer 1

你也可以尝试使用EXISTS：

SELECT EXISTS(SELECT * FROM table1 WHERE ...)

根据文档所述，您可以在 EXISTS 子查询中使用任何 SELECT 语句。

传统上，EXISTS 子查询以 SELECT * 开始，但它也可以以 SELECT 5 或 SELECT column1 或任何内容开头。 MySQL 忽略此类子查询中的 SELECT 列表，因此不会产生任何影响。

- Chris Thompson

---

测试语句为 ...EXISTS( SELECT 1/0 FROM someothertable)。对于 SQL Server 和 Oracle，使用 *、1 或 NULL 没有区别，因为 EXISTS 只是基于 WHERE 条件匹配的布尔值测试。 - OMG Ponies

伙计们，在这个答案中链接的文档中，第二段就说了，“传统上，EXISTS子查询以SELECT *开头，但它也可以以SELECT 5或SELECT column1或任何内容开头。MySQL会忽略这种子查询中的SELECT列表，所以没有任何影响。”请注意。 - mpen

执行该语句会发生什么？我的意思是结果集包含什么？ - Ashwin

@Ashwin，它包含一个0（不存在）或1（存在）。 - fedorqui

我认为你的查询是多余的。我测试了一下，这个查询SELECT 1 FROM table1 WHERE col = $var LIMIT 1比你的查询更快。那么你的查询有什么优势呢？ - Shafizadeh

Answer 2

最近我对这个主题进行了一些研究。如果字段是 TEXT 字段，非唯一字段，则实现方式必须不同。

我对 TEXT 字段进行了一些测试。考虑到我们有一个包含 1M 条记录的表。其中有 37 个条目等于 'something'：

• SELECT * FROM test WHERE text LIKE '%something%' LIMIT 1 with 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。

但是，用 BIGINT PK 字段时，只有一项等于 '321321'：

• SELECT * FROM test2 WHERE id ='321321' LIMIT 1 with 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。（更快）

- Laurent W.

---

谢谢您提供的额外答案。您是否发现对于TEXT字段，两个最快选项之间的时间差异非常一致？差异似乎不大，而且在这两种情况下使用SELECT EXISTS(SELECT 1 ... LIMIT 1)似乎都很好。 - Bernard Chen

你是对的，相对于文本字段的其他结果而言，这种差异并不那么重要。然而，也许使用 SELECT 1 FROM test WHERE texte LIKE '%something%' LIMIT 1 查询会更好。 - Laurent W.

我在mysql上尝试过，如果你使用select 1 ... limit 1，那么用exists包围是没有意义的。 - Adrien Horgnies

@LittleNooby，两者有所不同。SELECT EXISTS ...返回真或假值（1或0），而SELECT 1 ...要么返回1，要么为空。根据你的情况，假值和空集之间存在微妙的差别。 - Quickpick

你使用了哪个版本的MySQL？至少在5.5+版本中，EXISTS (SELECT ...)和EXISTS (SELECT ... LIMIT 1)没有区别。MySQL足够聪明，会自动插入LIMIT 1，因为这就是EXISTS的工作原理：当找到至少一个结果时，它就停止。 - Ruslan Stelmachenko

Answer 3

@ChrisThompson的答案的简短示例 示例：

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)

使用别名：

mysql> SELECT EXISTS(SELECT 1 FROM table_1 WHERE id = 1) AS mycheck;
+---------+
| mycheck |
+---------+
|       1 |
+---------+
1 row in set (0.00 sec)

- jaltek

---

虽然被接受的答案很有帮助，但我很欣赏别名语法。谢谢！ - krummens

Answer 4

在我的研究中，我发现以下速度的结果。

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) 

- shihab mm

---

除非你完全掌控宇宙，否则这些数字毫无意义。首先尝试以相反的顺序进行计算。当然，如果你的观点是没有区别，那么你可能是正确的。 - theking2

Answer 5

我觉得值得指出的是，在评论中曾经提到过，就是在这种情况下：

SELECT 1 FROM my_table WHERE *indexed_condition* LIMIT 1

优于：

SELECT * FROM my_table WHERE *indexed_condition* LIMIT 1

这是因为第一个查询可以通过索引满足，而第二个查询需要进行行查找（除非可能使用的索引中包含了表的所有列）。

添加LIMIT子句可以让引擎在找到任何一行后停止。

第一个查询应该与以下查询类似：

SELECT EXISTS(SELECT * FROM my_table WHERE *indexed_condition*)

虽然在这里(1/*)没有区别，但它向引擎发送相同的信号，但我仍然会写1来加强使用EXISTS时的习惯：

SELECT EXISTS(SELECT 1 FROM my_table WHERE *indexed_condition*)

如果您需要在没有匹配行时显式返回，则添加EXISTS包装可能是有意义的。

- Arth

Answer 6

建议您不要使用Count，因为每次使用count都会为数据库增加额外负载。相反，可以使用SELECT 1，如果查询到记录就返回1，否则返回null，从而达到更好的效果。

- fth

Answer 7

有时候，如果存在行的自增主键（id），则获取它非常方便；如果不存在，则返回0。

以下是如何在单个查询中完成此操作：

SELECT IFNULL(`id`, COUNT(*)) FROM WHERE ...

- Zaxter

---

为什么不在这里使用IFNULL(id, 0)而不是COUNT(*)？ - Ethan Hohensee

Answer 8

使用COUNT查询更快，虽然可能不太明显，但就获取所需结果而言，两种方法都足够。

- jaywon

---

然而，这是针对特定数据库的。在PostgreSQL中，COUNT(*)已知速度较慢。更好的做法是选择主键列并查看是否返回任何行。 - BalusC

InnoDB 中 COUNT(*) 操作速度较慢。 - Will

Answer 9

对于非InnoDB表，您也可以使用信息模式表：

http://dev.mysql.com/doc/refman/5.1/en/tables-table.html

- davek

Answer 10

我会选择使用COUNT(1)。它比COUNT(*)更快，因为COUNT(*)会测试该行中至少一个列是否!= NULL。你不需要这个，特别是因为你已经有了一个条件（WHERE子句）。相反，COUNT(1)测试的是1的有效性，它始终是有效的，并且测试时间要少得多。

- Felix

---

这是错误的。COUNT(*) 不会查看列的值 - 它只会计算行数。请参见我在这里的回答：https://dev59.com/K3E85IYBdhLWcg3wOw_s - Mark Byers

EXISTS 比 COUNT() 更快，因为 EXISTS 只要找到一条匹配的记录就可以立即返回。 - Will