MySQL IF 条件执行查询，否则执行其他查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17199604/
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
IF Condition Perform Query, Else Perform Other Query
提问by Howard Zoopaloopa
It feels pretty straightforward in anything but MySQL.
除了 MySQL 之外的任何东西都感觉很简单。
Basically I need to switch what type of index I am using along with a few other conditions based on how many results a particular term returns.
基本上，我需要根据特定术语返回的结果数量来切换我使用的索引类型以及其他一些条件。
Something to the effect of:
有以下作用：
IF (SELECT COUNT(*) FROM table WHERE term LIKE "term") > 4000
EXECUTE QUERY A
ELSE
EXECUTE QUERY B
Is this possible in a MySQL statement?
这在 MySQL 语句中是可能的吗？
EDIT:
编辑：
Query A:
查询一：
SELECT id
FROM table_a
FORCE INDEX(id)
JOIN table_b ON table_a.id = table_b.id
WHERE term LIKE "term"
ORDER BY date
LIMIT 100;
Query B:
查询 B：
SELECT id
FROM table_a
FORCE INDEX(term)
JOIN table_b ON table_a.id = table_b.id
WHERE term LIKE "term"
GROUP BY term # These lines would be included for a few conditions not mentioned above.. but are necessary
HAVING COUNT = 1 # same...
ORDER BY date
LIMIT 100;
The reason for the query switch is I get dramatically different result times based on the popularity of the "term".
查询切换的原因是根据“术语”的流行程度，我得到了截然不同的结果时间。
采纳答案by James Green
EDIT:What I said below about requiring a stored procedure is NOT TRUE. Try this:
编辑：我在下面所说的关于需要存储过程的内容不正确。尝试这个：
SELECT CASE WHEN ( (SELECT COUNT(*) FROM table WHERE term LIKE "term") > 4000 )
THEN <QUERY A>
ELSE <QUERY B>
END
This is, indeed, a case expression, and it works fine outside a stored proc :-)
这确实是一个 case 表达式，它在存储过程之外工作正常:-)
For instance:
例如：
mysql> SELECT CASE WHEN ( 5 > 4 ) THEN ( SELECT 'foo' ) ELSE ( SELECT 'bar' ) END;
+---------------------------------------------------------------------+
| CASE WHEN ( 5 > 4 ) THEN ( SELECT 'foo' ) ELSE ( SELECT 'bar' ) END |
+---------------------------------------------------------------------+
| foo |
+---------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> SELECT CASE WHEN ( 3 > 4 ) THEN ( SELECT 'foo' ) ELSE ( SELECT 'bar' ) END;
+---------------------------------------------------------------------+
| CASE WHEN ( 3 > 4 ) THEN ( SELECT 'foo' ) ELSE ( SELECT 'bar' ) END |
+---------------------------------------------------------------------+
| bar |
+---------------------------------------------------------------------+
1 row in set (0.00 sec)
Old answer below for historical interest, since it already gather upvotes:
以下是历史兴趣的旧答案，因为它已经收集了投票：
You can use the below I think, but only inside a stored procedure:
我认为您可以使用以下内容，但只能在存储过程中使用：
CASE (SELECT COUNT(*) FROM table WHERE term LIKE "term") > 4000
WHEN 1 THEN <QUERY A>
ELSE <QUERY B>
END CASE
This is a CASEstatement, as distinct from a CASEexpression... https://dev.mysql.com/doc/refman/5.0/en/case.htmlhas more gory details.
这是一个CASE声明，与CASE表达式不同...... https://dev.mysql.com/doc/refman/5.0/en/case.html有更多血腥的细节。
Actually, I suspect in general if you want to execute different queries conditionally, you're going to need to look toward stored procedures -- I could be wrong, but that's my gut feeling at this point. If you can do it, it'll probably be with CASE expressions!
实际上，我通常怀疑如果您想有条件地执行不同的查询，您将需要查看存储过程——我可能是错的，但这是我此时的直觉。如果你能做到，它可能会使用 CASE 表达式！
One last edit: in any real world example, I'd probably do the conditional bit in my application, and just hand off to SQL (or to an ORM which would generate my SQL) once I'd decided what to search for.
最后一个编辑：在任何现实世界的例子中，我可能会在我的应用程序中做条件位，然后在我决定要搜索的内容后交给 SQL（或将生成我的 SQL 的 ORM）。
回答by James Green
Try:
尝试：
select coalesce(i.id, t.id) id
from (SELECT COUNT(*) countterm FROM table WHERE term LIKE "term") c
left join
(SELECT id, date
FROM table_a
FORCE INDEX(id)
JOIN table_b ON table_a.id = table_b.id
WHERE term LIKE "term") i on countterm > 4000
left join
(SELECT id, date
FROM table_a
FORCE INDEX(term)
JOIN table_b ON table_a.id = table_b.id
WHERE term LIKE "term"
GROUP BY term
HAVING COUNT = 1) t on countterm <= 4000
ORDER BY coalesce(i.date, t.date)
LIMIT 100;
