检查 MySQL 表是否为空：COUNT(*) 为零与 LIMIT(0,1) 有结果吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23063327/
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
Check if MySQL Table is empty: COUNT(*) is zero vs. LIMIT(0,1) has a result?
提问by flungo
This is a simple question about efficiency specifically related to the MySQL implementation. I want to just check if a table is empty (and if it is empty, populate it with the default data). Would it be best to use a statement like SELECT COUNT(*) FROM `table`and then compare to 0, or would it be better to do a statement like SELECT `id` FROM `table` LIMIT 0,1then check if any results were returned (the result set has next)?
这是一个与 MySQL 实现特别相关的关于效率的简单问题。我只想检查一个表是否为空（如果为空，则用默认数据填充它）。最好使用类似SELECT COUNT(*) FROM `table`然后比较 0的语句，还是执行类似SELECT `id` FROM `table` LIMIT 0,1then 检查是否返回任何结果（结果集有下一个）这样的语句会更好？
Although I need this for a project I am working on, I am also interested in how MySQL works with those two statements and whether the reason people seem to suggest using COUNT(*)is because the result is cached or whether it actually goes through every row and adds to a count as it would intuitively seem to me.
虽然我正在从事的项目需要这个，但我也对 MySQL 如何处理这两个语句感兴趣，以及人们似乎建议使用的原因COUNT(*)是因为结果被缓存还是它实际上遍历每一行并添加到在我看来，这是一个直观的计数。
采纳答案by Seoester
You should definitely go with the second query rather than the first.
您绝对应该使用第二个查询而不是第一个。
When using COUNT(*), MySQL is scanning at least an index and counting the records. Even if you would wrap the call in a LEAST()(SELECT LEAST(COUNT(*), 1) FROM table;) or an IF(), MySQL will fully evaluate COUNT()before evaluating further. I don't believe MySQL caches the COUNT(*)result when InnoDB is being used.
使用 时COUNT(*)，MySQL 至少扫描一个索引并计算记录数。即使您将调用包装在LEAST()( SELECT LEAST(COUNT(*), 1) FROM table;) 或 an 中IF()，MySQL 也会COUNT()在进一步评估之前进行全面评估。我不相信 MySQLCOUNT(*)在使用 InnoDB 时会缓存结果。
Your second query results in only one row being read, furthermore an index is used (assuming id is part of one). Look at the documentation of your driver to find out how to check whether any rows have been returned.
By the way, the idfield may be omitted from the query (MySQL will use an arbitrary index):
您的第二个查询导致仅读取一行，此外还使用了索引（假设 id 是其中的一部分）。查看驱动程序的文档以了解如何检查是否已返回任何行。顺便说一句，该id字段可以从查询中省略（MySQL 将使用任意索引）：
SELECT 1 FROM table LIMIT 1;
However, I think the simplest and most performant solution is the following (as indicated in Gordon's answer):
但是，我认为最简单和最高效的解决方案如下（如戈登的回答所示）：
SELECT EXISTS (SELECT 1 FROM table);
EXISTSreturns 1if the subquery returns anyrows, otherwise 0. Because of this semantic MySQL can optimize the execution properly.
Any fields listed in the subquery are ignored, thus 1or *is commonly written.
EXISTS返回1如果子查询返回任何行，否则0。由于这种语义，MySQL 可以适当地优化执行。子查询中列出的任何字段都将被忽略，因此通常会写入1或*。
See the MySQL Manualfor more info on the EXISTSkeyword and its use.
有关关键字及其使用的更多信息，请参阅MySQL 手册EXISTS。
回答by Gordon Linoff
It is better to do the second method or just exists. Specifically, something like:
最好是做第二种方法或者只是exists。具体来说，类似于：
if exists (select id from table)
should be the fastest way to do what you want. You don't need the limit; the SQL engine takes care of that for you.
应该是做你想做的最快的方式。你不需要limit; SQL 引擎会为您处理这些。
By the way, never put identifiers (table and column names) in single quotes.
顺便说一句，永远不要将标识符（表名和列名）放在单引号中。
