从表中计数，但是不要在某个数字上计数

Is there a way in MySQL to COUNT(*) from a table where if the number is greater than x, it will stop counting there? Basically, I only want to know if the number of records returned from a query is more or less than a particular number. If it's more than that number, I don't really care how many rows there are, if it's less, tell me the count.

MySQL中是否有一种方法可以从一个表中计数(*)，如果这个表的数量大于x，那么它将停止计数?基本上，我只想知道查询返回的记录数是否大于或小于一个特定的数字。如果它大于这个数，我不在乎有多少行，如果小于，告诉我数。

I've been able to fudge it like this:

我可以这样捏造:

-- let x be 100

SELECT COUNT(*) FROM (
    SELECT `id` FROM `myTable`
    WHERE myCriteria = 1
    LIMIT 100
) AS temp

...but I was wondering if there was some handy built-in way to do this?

…但我想知道是否有什么简便的内置方法可以做到这一点?

Thanks for the suggestions, but I should have been more clear about the reasons behind this question. It's selecting from a couple of joined tables, each with tens of millions of records. Running COUNT(*) using an indexed criteria still takes about 80 seconds, running one without an index takes about 30 minutes or so. It's more about optimising the query rather than getting the correct output.

谢谢你的建议，但是我应该更清楚的知道这个问题背后的原因。它从几个连接的表中进行选择，每个表都有数千万条记录。使用索引条件运行COUNT(*)仍然需要大约80秒，没有索引的运行COUNT需要大约30分钟。它更多的是优化查询，而不是获得正确的输出。

3 个解决方案

#1

SELECT * FROM WhateverTable WHERE WhateverCriteria
LIMIT 100, 1

LIMIT 100, 1 returns 101th record, if there is one, or no record otherwise. You might be able to use the above query as a sub-query in EXIST clauses, if that helps.

限制100,1返回第101条记录，如果有，或者没有记录。如果有帮助，您可以将上面的查询用作EXIST子查询。

#2

I can't think of anything. It looks to me like what you're doing exactly fulfils the purpose, and SQL certainly doesn't seem to go out of its way to make it easy for you to do this more succinctly.

我想不出什么了。在我看来，您所做的事情完全符合目的，而且SQL似乎并没有使您更容易地更简洁地完成这项工作。

Consider this: What you're trying to do doesn't make sense in a strict context of set arithmetic. Mathematically, the answer is to count everything and then take the MIN() with 100, which is what you're (pragmatically and with good reason) trying to avoid.

考虑到这一点:在严格的集合算术环境中，您试图做的事情没有意义。从数学上来说，答案是把每件事都数一遍，然后用100分，这就是你想要避免的(务实的和有充分理由的)。

#3

This works:

如此:

select count(*) from ( select * from  stockinfo s limit 100 ) s

but was not any faster (that I could tell) from just:

但是，从刚才的情况来看，我并没有更快的速度(我可以这么说):

select count(*) from stockinfo

which returned 5170965.

它返回5170965。

#1

SELECT * FROM WhateverTable WHERE WhateverCriteria
LIMIT 100, 1