MySQL Case 表达式与 Case 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12436859/
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
Case Expression vs Case Statement
提问by mrmryb
What is the difference between a Case Expressionand a Case Statementin MySQL? When can they be used, and what are the benefits of using one over the other?
MySQL 中的Case 表达式和Case 语句有什么区别？什么时候可以使用它们，使用一个比另一个有什么好处？
Case Statement syntax:
Case 语句语法：
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE
Case Expression syntax:
案例表达式语法：
CASE
WHEN [condition] THEN result
[WHEN [condition] THEN result ...]
[ELSE result]
END
These look almost identical, but the initial description for Case Statements is that The CASE statement for stored programs implements a complex conditional construct.
这些看起来几乎相同，但对 Case Statements 的最初描述是 The CASE statement for stored programs implements a complex conditional construct.
So is the significant difference that one is used in stored programs and not usable in normal queries? I tried this out on a query I was playing with and it failed - sqlfiddle. If this is the case though, why not just use the Case Expression in a stored program?
那么在存储程序中使用一个和在普通查询中不可用的显着区别是什么？我在我正在使用的查询中尝试了这个，但它失败了 - sqlfiddle。如果是这种情况，为什么不在存储的程序中使用 Case 表达式呢？
Are there any other syntactical differences to be aware of, since they seem to be identical?
是否还有其他语法差异需要注意，因为它们似乎相同？
回答by lanzz
The CASEexpression evaluates to a value, i.e. it is used to evaluate to one of a set of results, based on some condition.
Example:
该CASE表达式计算为一个值，即它用于根据某些条件计算一组结果中的一个。
例子：
SELECT CASE
WHEN type = 1 THEN 'foo'
WHEN type = 2 THEN 'bar'
ELSE 'baz'
END AS name_for_numeric_type
FROM sometable`
The CASEstatement executes one of a set of statements, based on some condition.
Example:
该CASE语句根据某些条件执行一组语句中的一个。
例子：
CASE
WHEN action = 'update' THEN
UPDATE sometable SET column = value WHERE condition;
WHEN action = 'create' THEN
INSERT INTO sometable (column) VALUES (value);
END CASE
You see how they are similar, but the statement does notevaluate to a value and can be used on its own, while the expression needs to be a part of an expression, e.g. a query or an assignment. You cannot use the statement in a query, since a query cannot contain statements, only expressions that need to evaluate to something (the query itself is a statement, in a way), e.g. SELECT CASE WHEN condition THEN UPDATE table SET something; END CASEmakes no sense.
您会看到它们是如何相似的，但是该语句不会计算为值并且可以单独使用，而表达式需要是表达式的一部分，例如查询或赋值。您不能在查询中使用该语句，因为查询不能包含语句，只能包含需要求值的表达式（在某种程度上，查询本身就是一个语句），例如SELECT CASE WHEN condition THEN UPDATE table SET something; END CASE没有意义。
