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

我试图找出一行是否存在于一个表中。使用MySQL，是更好的做这样的查询:

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

然后检查总数是否为非零，或者像这样查询是否更好:

SELECT * FROM table1 WHERE ... LIMIT 1

并检查是否返回了任何行?

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

452177 次浏览
小开

COUNT查询更快，虽然可能不是很明显，但只要得到想要的结果，两者都应该足够了。

小开
最佳答案

你也可以尝试EXISTS:

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

并且根据的文档，你可以SELECT任何东西。

传统上，EXISTS子查询以SELECT *开头，但它可以 以SELECT 5或SELECT column n1或任何东西开始。<强> < em > MySQL

.
小开

COUNT(*)在MySQL中进行了优化，所以一般来说，前者查询可能更快。

小开

对于非innodb表，你也可以使用信息模式表:

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

小开

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

小开

我最近在这个问题上做了一些研究。如果字段是一个非唯一的TEXT字段，实现它的方式必须不同。

我已经做了一些测试与文本字段。考虑到我们有一个有1M个条目的表。37个条目等于“某物”:

    <李> SELECT * FROM test WHERE text LIKE '%something%' LIMIT 1 mysql_num_rows(): 0.039061069488525s。李(快) < / > <李> SELECT count(*) as count FROM test WHERE text LIKE '%something%: 李16.028197050095 s。< / > <李> SELECT EXISTS(SELECT 1 FROM test WHERE text LIKE '%something%'): 李0.87045907974243 s。< / >
  • 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 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.0002031326293943s。(快)
小开

建议你不要使用Count，因为count总是为db使用SELECT 1进行额外的加载，如果你的记录在那里，它会返回1，否则它会返回null，你可以处理它。

小开

下面是@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)
小开
或者您可以在条件中插入原始sql部分 所以我有 “条件”= >阵列('成员。id不在(选择会员。member_id FROM memberships AS Membership)')

小开

有时，如果行存在自动递增主键(id)，如果不存在则获取0，这是非常方便的。

以下是如何在单个查询中完成的:

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

在我的研究中，我可以发现结果越来越快。

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)
小开

我觉得值得指出的是，尽管评论中提到了这一点，但在这种情况下:

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换行可能是有意义的。


提交答案