如果条件执行查询，否则执行其他查询 [英] IF Condition Perform Query, Else Perform Other Query

问题描述

在MySQL以外的任何事物中，感觉都非常简单.

It feels pretty straightforward in anything but 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.

以下方面的作用

IF (SELECT COUNT(*) FROM table WHERE term LIKE "term") > 4000
   EXECUTE QUERY A
ELSE
   EXECUTE QUERY B

在MySQL语句中有可能吗?

Is this possible in a MySQL statement?

查询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;

查询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".

推荐答案

编辑:我在下面所说的关于要求存储过程的说法是不正确的.试试这个:

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

这确实是一个case表达式，并且在存储的proc之外可以正常工作:-)

This is, indeed, a case expression, and it works fine outside a stored proc :-)

例如:

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)

下面是历史问题的旧答案，因为它已经获得了好评:

我可以使用以下内容，但只能在存储过程中使用

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

这是一个CASE语句，与CASE表达式不同... https://dev.mysql.com/doc/refman/5.0/zh-CN/case.html 具有更多详细信息.

This is a CASE statement, as distinct from a CASE expression... https://dev.mysql.com/doc/refman/5.0/en/case.html has more gory details.

实际上，我总体上怀疑如果您想有条件地执行其他查询，您将需要考虑存储过程-我可能是错的，但这是我的直觉.如果可以的话，可能是CASE表达式！

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!

最后一个在任何现实世界的示例中，我可能都会在应用程序中做条件位处理，一旦我决定要搜索什么，就移交给SQL(或传递给生成我的SQL的ORM)

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.

这篇关于如果条件执行查询，否则执行其他查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！