Case 表达式与 Case 语句 [英] Case Expression vs Case Statement

问题描述

案例表达和MySQL 中的 Case Statement?什么时候可以使用它们，使用一个比另一个有什么好处?

What is the difference between a Case Expression and a Case Statement in MySQL? When can they be used, and what are the benefits of using one over the other?

Case 语句语法:

CASE
  WHEN search_condition THEN statement_list
  [WHEN search_condition THEN statement_list] ...
  [ELSE statement_list]
END CASE

Case 表达式语法:

Case Expression syntax:

CASE 
  WHEN [condition] THEN result 
  [WHEN [condition] THEN result ...] 
  [ELSE result] 
END

这些看起来几乎相同，但对 Case 语句的最初描述是 存储程序的 CASE 语句实现了一个复杂的条件结构.

These look almost identical, but the initial description for Case Statements is that The CASE statement for stored programs implements a complex conditional construct.

那么在存储程序中使用一个和在普通查询中不使用一个显着区别是什么?我在我正在使用的查询中尝试了这个，但它失败了 - sqlfiddle.如果是这种情况，为什么不在存储的程序中使用 Case 表达式?

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?

是否还有其他语法差异需要注意，因为它们似乎相同?

Are there any other syntactical differences to be aware of, since they seem to be identical?

推荐答案

CASE 表达式求值为一个值，即它用于根据某些条件求值为一组结果中的一个.
示例:

The CASE expression evaluates to a value, i.e. it is used to evaluate to one of a set of results, based on some condition.
Example:

SELECT CASE
    WHEN type = 1 THEN 'foo'
    WHEN type = 2 THEN 'bar'
    ELSE 'baz'
END AS name_for_numeric_type
FROM sometable`

CASE 语句根据某些条件执行一组语句中的一个.
示例:

The CASE statement executes one of a set of statements, based on some condition.
Example:

CASE
    WHEN action = 'update' THEN
        UPDATE sometable SET column = value WHERE condition;
    WHEN action = 'create' THEN
        INSERT INTO sometable (column) VALUES (value);
END CASE

您会看到它们是如何相似的，但是语句不计算为值并且可以单独使用，而表达式需要是表达式的一部分，例如查询或作业.您不能在查询中使用该语句，因为查询不能包含语句，只能包含需要计算结果的表达式(查询本身在某种程度上是一个语句)，例如SELECT CASE WHEN condition THEN UPDATE table SET something;END CASE 没有意义.

You see how they are similar, but the statement does not evaluate 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 CASE makes no sense.

这篇关于Case 表达式与 Case 语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！