Значения NULL

NULL представляет неизвестное значение. Это не пустая строка (по умолчанию) или нулевое значение. Все эти значения являются допустимыми и не являются значениями NULL.

При создании или изменении формата таблицы столбцы могут быть указаны как принимающие значения NULL или не принимающие их с помощью соответственно NULL и NOT NULL положений.

Например, таблица клиентов может содержать даты рождения. Для некоторых клиентов эта информация неизвестна, поэтому значение может быть NULL.

Система может выделять идентификатор клиента для каждой записи клиента, и в этом случае значение NULL не допускается.

CREATE TABLE customer (
 id INT NOT NULL, 
 date_of_birth DATE NULL
...
)

Переменные, определенные пользователем, имеют значение NULL до явного присвоения значения.

Сохраненные процедуры параметры и локальные переменные всегда могут быть установлены в NULL. Если для локальной переменной не указано значение DEFAULT, ее начальное значение будет NULL. Если параметру OUT в хранимой процедуре не присвоено значение, по окончании процедуры ему присваивается NULL.

Синтаксис

Случай NULL не имеет значения. \N (заглавными буквами) является псевдонимом для NULL.

Оператор IS принимает UNKNOWN как псевдоним для NULL, который предназначен для булевых контекстов.

Операторы сравнения

Значения NULL не могут быть использованы со многими операторами сравнения. Например, =, >, >=, <=, < или != не могут быть использованы, так как любое сравнение со значением NULL всегда возвращает значение NULL, а не true (1) или false (0).

SELECT NULL = NULL;
+-------------+
| NULL = NULL |
+-------------+
|        NULL |
+-------------+

SELECT 99 = NULL;
+-----------+
| 99 = NULL |
+-----------+
|      NULL |
+-----------+

Для преодоления этого, некоторые операторы специально разработаны для работы со значениями NULL. Для проверки равенства двух значений, которые могут содержать NULL, есть оператор <=>, безопасное сравнение на равенство с учетом NULL.

SELECT 99 <=> NULL, NULL <=> NULL;
+-------------+---------------+
| 99 <=> NULL | NULL <=> NULL |
+-------------+---------------+
|           0 |             1 |
+-------------+---------------+

Другие операторы для работы со значениями NULL включают IS NULL и IS NOT NULL, ISNULL (для проверки выражения) и COALESCE (для возвращения первого не-NULL параметра).

Сортировка

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

SELECT col1 FROM tab ORDER BY ISNULL(col1), col1;

Сортировка в порядке убывания, с NULL значениями в начале:

SELECT col1 FROM tab ORDER BY IF(col1 IS NULL, 0, 1), col1 DESC;

Все значения NULL также считаются эквивалентными для целей инструкций DISTINCT и GROUP BY.

Функции

В большинстве случаев функции вернут NULL, если любой из параметров имеет значение NULL. Существуют также функции, специально предназначенные для обработки значений NULL. К ним относятся IFNULL(), NULLIF() и COALESCE().

SELECT IFNULL(1,0); 
+-------------+
| IFNULL(1,0) |
+-------------+
|           1 |
+-------------+

SELECT IFNULL(NULL,10);
+-----------------+
| IFNULL(NULL,10) |
+-----------------+
|              10 |
+-----------------+

SELECT COALESCE(NULL,NULL,1);
+-----------------------+
| COALESCE(NULL,NULL,1) |
+-----------------------+
|                     1 |
+-----------------------+

Функции агрегирования, такие как SUM и AVG, игнорируют значения NULL.

CREATE TABLE t(x INT);

INSERT INTO t VALUES (1),(9),(NULL);

SELECT SUM(x) FROM t;
+--------+
| SUM(x) |
+--------+
|     10 |
+--------+

SELECT AVG(x) FROM t;
+--------+
| AVG(x) |
+--------+
| 5.0000 |
+--------+

Исключение составляет COUNT(*), которая подсчитывает строки и не проверяет, является ли значение NULL или нет. Сравните, например, COUNT(x), которая игнорирует NULL, и COUNT(*), которая его учитывает:

SELECT COUNT(x) FROM t;
+----------+
| COUNT(x) |
+----------+
|        2 |
+----------+

SELECT COUNT(*) FROM t;
+----------+
| COUNT(*) |
+----------+
|        3 |
+----------+

AUTO_INCREMENT, TIMESTAMP и виртуальные столбцы

MariaDB обрабатывает значения NULL особым образом, если поле является AUTO_INCREMENT, TIMESTAMP или виртуальным столбцом. Вставка значения NULL в числовой столбец AUTO_INCREMENT приведет к вставке следующего числа в последовательности AUTO_INCREMENT вместо него. Этот метод часто используется с полями AUTO_INCREMENT, которые самостоятельно обрабатывают эту ситуацию.

CREATE TABLE t2(id INT PRIMARY KEY AUTO_INCREMENT, letter CHAR(1));

INSERT INTO t2(letter) VALUES ('a'),('b');

SELECT * FROM t2;
+----+--------+
| id | letter |
+----+--------+
|  1 | a      |
|  2 | b      |
+----+--------+

Аналогично, если значение NULL присваивается полю TIMESTAMP, вместо него присваивается текущая дата и время.

CREATE TABLE t3 (x INT, ts TIMESTAMP);

INSERT INTO t3(x) VALUES (1),(2);

После паузы,

INSERT INTO t3(x) VALUES (3);

SELECT* FROM t3;
+------+---------------------+
| x    | ts                  |
+------+---------------------+
|    1 | 2013-09-05 10:14:18 |
|    2 | 2013-09-05 10:14:18 |
|    3 | 2013-09-05 10:14:29 |
+------+---------------------+

Если NULL присваивается столбцу VIRTUAL или PERSISTENT, вместо этого присваивается значение по умолчанию.

CREATE TABLE virt (c INT, v INT AS (c+10) PERSISTENT) ENGINE=InnoDB;

INSERT INTO virt VALUES (1, NULL);

SELECT c, v FROM virt;
+------+------+
| c    | v    |
+------+------+
|    1 |   11 |
+------+------+

Во всех этих особых случаях, NULL эквивалентно ключевому слову DEFAULT.

Вставка

Если значение NULL вставляется в строку в столбец, объявленный как NOT NULL, будет возвращено сообщение об ошибке. Однако, если режим SQL не является строгим (по умолчанию до MariaDB 10.2.3), если значение NULL вставляется в несколько строк в столбец, объявленный как NOT NULL, неявное значение по умолчанию для типа столбца будет вставлено (а не значение по умолчанию в определении таблицы). Неявные значения по умолчанию - пустая строка для строковых типов и нулевое значение для числовых, даты и времени.

С MariaDB 10.2.4 по умолчанию оба случая приведут к ошибке.

Примеры

CREATE TABLE nulltest (
  a INT(11), 
  x VARCHAR(10) NOT NULL DEFAULT 'a', 
  y INT(11) NOT NULL DEFAULT 23
);

Вставка в одну строку:

INSERT INTO nulltest (a,x,y) VALUES (1,NULL,NULL);
ERROR 1048 (23000): Column 'x' cannot be null

Вставка в несколько строк с режимом SQL не строгим (по умолчанию до MariaDB 10.2.3):

show variables like 'sql_mode%';
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| sql_mode      | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+

INSERT INTO nulltest (a,x,y) VALUES (1,NULL,NULL),(2,NULL,NULL); 
Query OK, 2 rows affected, 4 warnings (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 4

Указанные значения по умолчанию не использовались; вместо этого были вставлены неявные значения по умолчанию для типа столбца

SELECT * FROM nulltest;
+------+---+---+
| a    | x | y |
+------+---+---+
|    1 |   | 0 |
|    2 |   | 0 |
+------+---+---+

Первичные ключи и уникальные индексы

Уникальные индексы могут содержать несколько значений NULL.

Первичные ключи никогда не могут быть NULL.

IF a=10 THEN NULL; ELSE NULL; END IF

SET sql_mode=EMPTY_STRING_IS_NULL;
SELECT '' IS NULL; -- returns TRUE
INSERT INTO t1 VALUES (''); -- inserts NULL

См. также

• Первичные ключи со столбцами, допускающими значения NULL
• Оператор IS NULL
• Оператор IS NOT NULL
• Функция ISNULL
• Функция COALESCE
• Функция IFNULL
• Функция NULLIF
• Типы данных CONNECT
• Режим Oracle с MariaDB 10.3