12.4 Control Flow Functions

Table 12.6 Flow Control Operators

Name	Description
`CASE`	Case operator
`IF()`	If/else construct
`IFNULL()`	Null if/else construct
`NULLIF()`	Return NULL if expr1 = expr2

CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END
The first CASE syntax returns the result for the first value=compare_value comparison that is true. The second syntax returns the result for the first condition that is true. If no comparison or condition is true, the result after ELSE is returned, or NULL if there is no ELSE part.

Note

The syntax of the CASE expr described here differs slightly from that of the SQL CASE statement described in Section 13.6.5.1, “CASE Syntax”, for use inside stored programs. The CASE statement cannot have an ELSE NULL clause, and it is terminated with END CASE instead of END.

The return type of a CASE expression result is the aggregated type of all result values:
- If all types are numeric, the aggregated type is also numeric:
  - If at least one argument is double precision, the result is double precision.
  - Otherwise, if at least one argument is DECIMAL, the result is DECIMAL.
  - Otherwise, the result is an integer type (with one exception):
    
    If all integer types are all signed or all unsigned, the result is the same sign and the precision is the highest of all specified integer types (that is, TINYINT, SMALLINT, MEDIUMINT, INT, or BIGINT).
    If there is a combination of signed and unsigned integer types, the result is signed and the precision may be higher. For example, if the types are signed INT and unsigned INT, the result is signed BIGINT.
    The exception is unsigned BIGINT combined with any signed integer type. The result is DECIMAL with sufficient precision and scale 0.
- If all types are BIT, the result is BIT. Otherwise, BIT arguments are treated similar to BIGINT.
- If all types are YEAR, the result is YEAR. Otherwise, YEAR arguments are treated similar to INT.
- If all types are character string (CHAR or VARCHAR), the result is VARCHAR with maximum length determined by the longest character length of the operands.
- If all types are character or binary string, the result is VARBINARY.
- SET and ENUM are treated similar to VARCHAR; the result is VARCHAR.
- If all types are JSON, the result is JSON.
- If all types are temporal, the result is temporal:
  - If all temporal types are DATE, TIME, or TIMESTAMP, the result is DATE, TIME, or TIMESTAMP, respectively.
  - Otherwise, for a mix of temporal types, the result is DATETIME.
- If all types are GEOMETRY, the result is GEOMETRY.
- If any type is BLOB, the result is BLOB.
- For all other type combinations, the result is VARCHAR.
- Literal NULL operands are ignored for type aggregation.
1. mysql> SELECT CASE 1 WHEN 1 THEN 'one'
2. -> WHEN 2 THEN 'two' ELSE 'more' END;
3. -> 'one'
4. mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
5. -> 'true'
6. mysql> SELECT CASE BINARY 'B'
7. -> WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
8. -> NULL
IF(expr1,expr2,expr3)
If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL), IF() returns expr2. Otherwise, it returns expr3.

Note

There is also an IF statement, which differs from the IF() function described here. See Section 13.6.5.2, “IF Syntax”.

If only one of expr2 or expr3 is explicitly NULL, the result type of the IF() function is the type of the non-NULL expression.
The default return type of IF() (which may matter when it is stored into a temporary table) is calculated as follows:
- If expr2 or expr3 produce a string, the result is a string.
  If expr2 and expr3 are both strings, the result is case-sensitive if either string is case sensitive.
- If expr2 or expr3 produce a floating-point value, the result is a floating-point value.
- If expr2 or expr3 produce an integer, the result is an integer.
1. mysql> SELECT IF(1>2,2,3);
2. -> 3
3. mysql> SELECT IF(1<2,'yes','no');
4. -> 'yes'
5. mysql> SELECT IF(STRCMP('test','test1'),'no','yes');
6. -> 'no'
IFNULL(expr1,expr2)
If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2.
1. mysql> SELECT IFNULL(1,0);
2. -> 1
3. mysql> SELECT IFNULL(NULL,10);
4. -> 10
5. mysql> SELECT IFNULL(1/0,10);
6. -> 10
7. mysql> SELECT IFNULL(1/0,'yes');
8. -> 'yes'
The default return type of IFNULL(expr1,expr2) is the more “general” of the two expressions, in the order STRING, REAL, or INTEGER. Consider the case of a table based on expressions or where MySQL must internally store a value returned by IFNULL() in a temporary table:
1. mysql> CREATE TABLE tmp SELECT IFNULL(1,'test') AS test;
2. mysql> DESCRIBE tmp;
3. +-------+--------------+------+-----+---------+-------+
4. | Field | Type | Null | Key | Default | Extra |
5. +-------+--------------+------+-----+---------+-------+
6. | test | varbinary(4) | NO | | | |
7. +-------+--------------+------+-----+---------+-------+
In this example, the type of the test column is VARBINARY(4) (a string type).
NULLIF(expr1,expr2)
Returns NULL if expr1 = expr2 is true, otherwise returns expr1. This is the same as CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.
The return value has the same type as the first argument.
1. mysql> SELECT NULLIF(1,1);
2. -> NULL
3. mysql> SELECT NULLIF(1,2);
4. -> 1
Note

MySQL evaluates expr1 twice if the arguments are not equal.

Suchen Sie im MySQL-Handbuch

Deutsche Übersetzung

Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.

Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.

Vielen Dank im Voraus.

Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-control-flow-functions.html

Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.

Referenzen

Manuel MySQL : https://dev.mysql.com/

Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor dieser Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.