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

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子句都使用索引。

13 个解决方案

#1

You could also try using

您也可以尝试使用

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

per the documentation

每个文档

Per a comment below:

每一个注释如下:

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

#2

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.

我最近对这个问题做了一些研究。如果字段是一个文本字段，一个非唯一字段，那么实现它的方法必须是不同的。

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':

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

• SELECT * FROM test WHERE texte LIKE '%something%' LIMIT 1 with mysql_num_rows() : 0.039061069488525s. (FASTER)
• 选择* FROM测试，其中texte喜欢'%something%' LIMIT 1 with mysql_num_rows(): 0.039061069488525s。(快)
• SELECT count(*) as count FROM test WHERE text LIKE '%something% : 16.028197050095s.
• 选择count(*)作为count FROM test，其中'%something%: 16.028197050095 5s。
• SELECT EXISTS(SELECT 1 FROM test WHERE text LIKE '%something%') : 0.87045907974243s.
• 选择exist(从test中选择1，其中文本如'%something%'): 0.87045907974243s。
• SELECT EXISTS(SELECT 1 FROM test WHERE text LIKE '%something%' LIMIT 1) : 0.044898986816406s.
• 选择存在(从测试中选择1，其中文本如'% 1 %' '限制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 1 with mysql_num_rows() : 0.0089840888977051s.
• 从test2中选择*，其中id ='321321' LIMIT 1带有mysql_num_rows(): 0.0089840888977051s。
• SELECT count(*) as count FROM test2 WHERE id ='321321' : 0.00033879280090332s.
• 从test2中选择count(*)，其中id ='321321': 0.00033879280090332。
• SELECT EXISTS(SELECT 1 FROM test2 WHERE id ='321321') : 0.00023889541625977s.
• 选择exist(在test2中选择1，其中id ='321321'): 0.00023889541625977s。
• SELECT EXISTS(SELECT 1 FROM test2 WHERE id ='321321' LIMIT 1) : 0.00020313262939453s. (FASTER)
• 选择exist(从test2中选择1，其中id ='321321' LIMIT 1): 0.0002031326292939453s。(快)

#3

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)

#4

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) 

#5

Suggest you not to use Count because count always makes extra loads for db use SELECT 1 and it returns 1 if your record right there otherwise it returns null and you can handle it.

建议您不要使用Count，因为Count总是为db产生额外的负载，选择1，如果您的记录在那里，它返回1，否则它返回null，您可以处理它。

#6

At times it is quite handy to get the auto increment primary key (id) of the row if it exists and 0 if it doesn't.

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

Here's how this can be done in a single query:

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

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

#7

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 WHERE clause). COUNT(1) instead tests the validity of 1, which is always valid and takes a lot less time to test.

我与计数(1)。它比COUNT(*)要快，因为COUNT(*)测试以查看该行中是否至少有一列是!= NULL。你不需要它，特别是因为你已经有了一个条件(WHERE子句)。相反，COUNT(1)测试1的有效性，它总是有效的，测试所需的时间要少得多。

#8

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 LIMIT clause 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.

它向引擎发送相同的信号。

It may make sense to add the EXISTS wrapping is if you require an explicit return when no rows match.

如果在没有行匹配的情况下需要显式返回，那么添加现有包装可能是有意义的。

#9

The performance depends also on your table indexes. Sometimes it is better to use old ways with a proper table index:

性能也取决于表索引。有时，最好使用带有适当表索引的旧方法:

SELECT COUNT(field) FROM table WHERE field = 'something' LIMIT 1  

Have an index "my_index" on "field"

在“字段”上有一个索引“my_index”

Explain will look like this:

Explain是这样的:

id select_type table  type   possible_keys key       key_len  ref   rows Extra
1  SIMPLE      table  ref    my_index      my_index  4        const 1  Using index

#10

For non-InnoDB tables you could also use the information schema tables:

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

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

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

#11

Or you can insert raw sql part to conditions so I have 'conditions'=>array('Member.id NOT IN (SELECT Membership.member_id FROM memberships AS Membership)')

或者可以将原始sql部分插入到条件中，这样就有了“conditions”=>数组(“Member”)。id不在(选择成员资格。会员身份的member_id作为会员)'

#12

A COUNT query is faster, although maybe not noticeably, but as far as getting the desired result, both should be sufficient.

计数查询更快，虽然可能不明显，但只要得到预期的结果，两者都应该足够。

#13

COUNT(*) are optimized in MySQL, so the former query is likely to be faster, generally speaking.

COUNT(*)在MySQL中进行了优化，因此一般来说，前一个查询可能会更快。