MySQL: Использование CASE и IF() в запросах SELECT

В разделе рассматривается MySQL-функции управления потоком, такие как CASE, IF(), IFNULL() и NULLIF(), которые можно использовать в запросах SELECT базы данных MySQL.

Содержание:

• Оператор CASE в запросах к БД MySQL;
• Функция IF() в запросах к БД MySQL;
• Функция IFNULL();
• Функция NULLIF();

Оператор CASE в запросах к БД MySQL.

Синтаксис №1:

CASE value WHEN compare_value THEN result [WHEN compare_value THEN result ...] [ELSE result] END

Первый синтаксис CASE возвращает результат result первого сравнения value=compare_value, которое является истинным.

Синтаксис №2:

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

Второй синтаксис возвращает результат для первого истинного условия. Если ни одно сравнение или условие не истинно, то возвращается результат после ELSE или если нет части ELSE то возвращается NULL.

Примеры использования оператора CASE:

-- общий пример использования CASE
mysql> SELECT CASE 1 WHEN 1 THEN 'one'
    ->     WHEN 2 THEN 'two' ELSE 'more' END;
        -> 'one'
-- в операторе CASE не используется значение (синтаксис №2) 
-- чем-то похоже на if/else в Python
mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
        -> 'true'
-- не используется SQL-инструкция `ELSE`
mysql> SELECT CASE BINARY 'B'
    ->     WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
        -> NULL

Тип возвращаемого результата выражения CASE - это агрегированный тип всех значений результата:

• Если все типы являются числовыми, то агрегированный тип также является числовым:
  • Если хотя бы один аргумент имеет двойную точность, результатом будет двойная точность.
  • В противном случае, если хотя бы один аргумент имеет значение DECIMAL, то результатом будет DECIMAL.
  • В противном случае результатом будет целочисленный тип (с одним исключением):
  • Если все целочисленные типы все знаковые или все беззнаковые, то результатом будет один и тот же знак, а точность будет самой высокой из всех указанных целочисленных типов (т. е. TINYINT, SMALLINT, MEDIUMINT, INT или BIGINT).
  • Если существует комбинация целочисленных типов со знаком и без знака, результат будет со знаком, а точность может быть выше. Например, если типы со знаком имеют INT и без знака INT, то результат будет со знаком BIGINT.
  • Исключением является беззнаковое BIGINT в сочетании с любым целочисленным типом со знаком. Результат DECIMAL с достаточной точностью и масштабом 0.
• Если все типы являются битовыми, результат будет битовым. В противном случае аргументы BIT обрабатываются аналогично BIGINT.
• Если все типы YEAR, то результатом будет YEAR. В противном случае аргументы YEAR обрабатываются аналогично INT.Если все типы представляют собой строку символов (CHAR или VARCHAR), то результатом будет VARCHAR с максимальной длиной, определяемой наибольшей длиной символов операндов.
• Если все типы являются символьными или двоичными строками, то результатом будет VARBINARY.
• типы SET и ENUM обрабатываются аналогично VARCHAR; результат VARCHAR.
• Если все типы являются датой/временем, то результат будет этого типа:
  • Если все типы - DATE, TIME или TIMESTAMP, то результатом будет DATE, TIME или TIMESTAMP соответственно.
  • В противном случае при сочетании типов даты и времени - результатом будет DATETIME.
• Если какой-либо тип - BLOB, результатом будет BLOB.
• Для всех других комбинаций типов результатом является VARCHAR.
• Литеральные операнды NULL при агрегации типов игнорируются .

Примечание. Синтаксис оператора CASE описанный здесь, немного отличается от синтаксиса, который используется внутри хранимых базой данных функций и процедур. Оператор CASE, используемый в хранимых процедурах и функциях БД MySQL дополнительно может иметь предложение ELSE NULL и завершается с помощью END CASE вместо END.

Функция IF() в запросах к БД MySQL.

Синтаксис:

IF(expr1,expr2,expr3)

Если expr1 имеет значение TRUE (т.е. expr1 <> 0 и expr1 IS NOT NULL), то функция IF() возвращает expr2. В противном случае возвращается expr3.

Если только одно из выражений expr2 или expr3 явно равно NULL, то тип результата функции IF() является типом выражения, отличного от NULL.

Примеры использования функции IF()

mysql> SELECT IF(1>2,2,3);
        -> 3
mysql> SELECT IF(1<2,'yes','no');
        -> 'yes'
mysql> SELECT IF(STRCMP('test','test1'),'no','yes');
        -> 'no'

Тип возврата IF() по умолчанию (который может иметь значение, когда он сохраняется во временной таблице) рассчитывается следующим образом:

• Если выражения expr2 или expr3 создают строку, то результатом будет строка.
• Если выражения expr2 и expr3 являются строками, то результат чувствителен к регистру, если любая строка чувствительна к регистру.
• Если выражения expr2 и expr3 производят значение с плавающей запятой, результатом будет значение с плавающей запятой.
• Если выражения expr2 и expr3 дают целое число, результатом будет целое число.

Примечание. Синтаксис оператора IF описанный здесь, отличается от синтаксиса, который используется внутри хранимых базой данных функций и процедур.

Функция IFNULL().

Синтаксис:

IFNULL(expr1,expr2)

Если expr1 не равно NULL, то функция IFNULL() возвращает expr1; в противном случае возвращается expr2.

Примеры использования функции IFNULL()

mysql> SELECT IFNULL(1,0);
        -> 1
mysql> SELECT IFNULL(NULL,10);
        -> 10
mysql> SELECT IFNULL(1/0,10);
        -> 10
mysql> SELECT IFNULL(1/0,'yes');
        -> 'yes'

Тип возвращаемого значения по умолчанию IFNULL(expr1,expr2) является более "общим" из двух выражений в порядке STRING, REAL или INTEGER. Рассмотрим случай таблицы, основанной на выражениях, или где MySQL должен внутренне хранить значение, возвращаемое IFNULL(), во временной таблице:

mysql> CREATE TABLE tmp SELECT IFNULL(1,'test') AS test;
mysql> DESCRIBE tmp;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| test  | varbinary(4) | NO   |     |         |       |
+-------+--------------+------+-----+---------+-------+

В этом примере тестовый столбец имеет тип VARBINARY(4) (строковый тип).

Функция NULLIF().

Синтаксис:

NULLIF(expr1,expr2)

Функция NULLIF() возвращает NULL, если выражение expr1 = expr2 истинно, в противном случае возвращает expr1.

NULLIF() эквивалентна выражению:

CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END

Возвращаемое значение имеет тот же тип, что и первый аргумент.

Примеры использования функции NULLIF():

mysql> SELECT NULLIF(1,1);
        -> NULL
mysql> SELECT NULLIF(1,2);
        -> 1

Примечание. MySQL оценивает expr1 дважды, если аргументы не равны.