【MySQL】CASE Function_mysql function case-优快云博客

本文详细介绍了MySQL的CASE函数，包括其功能、语法、参数、注意事项及适用版本。该函数具有IF - THEN - ELSE语句的功能，可评估条件并返回首个满足条件的值。还给出了包含和不包含表达式的使用示例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

MySQL: CASE Function

This MySQL tutorial explains how to use the MySQL CASE function with syntax and examples.

Description

The MySQL CASE function has the functionality of an IF-THEN-ELSE statement by allowing you to evaluate conditions and return a value when the first condition is met.

Syntax

The syntax for the CASE function in MySQL is:

CASE [ expression ]

   WHEN condition_1 THEN result_1
   WHEN condition_2 THEN result_2
   ...
   WHEN condition_n THEN result_n

   ELSE result

END

Parameters or Arguments

expression

Optional. It is the value that you are comparing to the list of conditions. (ie: condition_1, condition_2, ... condition_n)

condition_1, condition_2, ... condition_n

Evaluated in the order listed. Once a condition is found to be true, the CASE function will return the result and not evaluate the conditions any further.

result_1, result_2, ... result_n

The value returned once a condition is found to be true.

Note

If no condition is found to be true, then the CASE function will return the value in the ELSE clause.
If the ELSE clause is omitted and no condition is found to be true, then the CASE statement will return NULL.

Applies To

The CASE function can be used in the following versions of MySQL:

MySQL 5.7, MySQL 5.6, MySQL 5.5, MySQL 5.1, MySQL 5.0, MySQL 4.1, MySQL 4.0, MySQL 3.23.3

Example - Includes expression

You could use the CASE function in a SQL statement where the expression is included.

SELECT supplier_id,
CASE quantity
  WHEN > 10 THEN 'The quantity is greater than 10'
  WHEN = 10 THEN 'The quantity is 10'
  ELSE 'The quantity is something else'
END
FROM suppliers;

In this CASE function example, the expression is quantity whose value would be compared to each of the conditions until one is met. Then the corresponding value would be returned by the CASE function.

Example - Excludes expression

You could use the CASE function in a SQL statement where the expression is omitted.

SELECT
CASE
  WHEN a < b THEN 1
  WHEN supplier_type = 'clothing' THEN 2
  ELSE 3
END
FROM suppliers;

In this CASE function example, an expression has not been included so each condition is individually evaluated and can be completely different and unique. When a condition is met, the corresponding value would be returned.

[From Google: https://www.techonthenet.com/mysql/functions/case.php]