Описание : в этой статье вы узнаете, как работать со значениями NULL в MySQL. Кроме того, вы изучите некоторые полезные функции для эффективной работы со значениями NULL.
Введение в NULL в MySQL
В MySQL значение NULL означает неизвестное. Значение NULL отличается от нуля (0) или пустой строки ».
Значение NULL не равно ни к чему, даже сам по себе. Если вы сравниваете значение NULL с другим значением NULL или любым другим значением, результатом является то, что значение NULL каждого значения NULL неизвестно.
Обычно значение NULL используется для указания того, что данные отсутствуют, неизвестны или неприменимы. Например, номер телефона потенциального клиента может быть NULL и может быть добавлен позже.
Когда вы создаете таблицу, вы можете указать, принимает ли столбец значения NULL или нет, используя ограничение NOT NULL.
Например, следующий оператор создает таблицу leads:
DROP TABLE IF EXISTS leads;
CREATE TABLE leads (
id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
source VARCHAR(255) NOT NULL,
email VARCHAR(100),
phone VARCHAR(25)
);
В таблице leads столбец id является столбцом первичного ключа, поэтому он не принимает никаких значений NULL.
Столбцы first_name, last_name и source используют ограничение NOT NULL, следовательно, вы не можете вставить любые значения NULL в эти столбцы, в то время как emailи phoneстолбцы принимают значения NULL.
Вы можете использовать NULLзначение в выражении INSERT, чтобы указать, что данные отсутствуют. Например, следующий оператор вставляет строку в таблицу leads. Поскольку номер телефона отсутствует, поэтому используется значениеNULL.
INSERT INTO leads(first_name,last_name,source,email,phone)
VALUE('Andrey','Terminator','Web Search','Andrey.Terminator@andreyex.ru',NULL);
Поскольку значение по умолчанию для столбца электронной почты — это NULL, вы можете опустить электронную почту в выражении INSERT следующим образом:
INSERT INTO leads(first_name,last_name,source,phone)
VALUES
('Max','Hard','Cold Calling','(123)-456-789'),
('Lilu','Dalas','Web Search','(987)-654-321');
SET NULL в заявлении UPDATE
Чтобы установить значение столбца в NULL, вы используете оператор присваивания ( =). Например, чтобы обновить телефон Lilu Dalas до NULL, вы используете следующую инструкцию UPDATE:
UPDATE
leads
SET
phone = NULL
WHERE
id = 3;
MySQL ORDER BY NULL
Если вы используете предложение ORDER BY для сортировки результирующего набора в порядке возрастания, MySQL считает, что значения NULL ниже, чем другие значения, поэтому NULL сначала он представляет значения.
Следующее утверждение сортирует клиентов по номеру телефона в порядке возрастания.
SELECT
*
FROM
leads
ORDER BY phone;
Если вы используете ORDER BY DESC, значения NULL появляются в конце набора результатов. Смотрите следующий пример:
SELECT
*
FROM
leads
ORDER BY phone DESC;
Для проверки NULL в запросе вы используете оператор IS NULL или IS NOT NULL в предложении WHERE.
Например, чтобы получить потенциальных клиентов, которые еще не указали номер телефона, вы используете оператора IS NULL следующим образом:
SELECT
*
FROM
leads
WHERE
phone IS NULL;
Вы можете использовать оператора IS NOT, чтобы получить все лиды, которые предоставили адреса электронной почты.
SELECT
*
FROM
leads
WHERE
email IS NOT NULL;
Даже если значение NULL не равно NULL, два значения NULL равны в предложении GROUP BY.
SELECT
id, first_name, last_name, email, phone
FROM
leads
GROUP BY email;
Запрос возвращает только две строки, потому что строки, столбец электронной почты, у которых NULL сгруппирован в одну.
NULL и индекс UNIQUE в MySQL
Когда вы используете ограничение UNIQUE или индекс UNIQUE для столбца, вы можете вставить несколько значений NULL в этот столбец. Это прекрасно, потому что в этом случае MySQL считает, что значения NULL различны.
Давайте проверим этот момент, создав индекс UNIQUE для столбца phone.
CREATE UNIQUE INDEX idx_phone ON leads(phone);
Обратите внимание, что если вы используете механизм хранения BDB, MySQL считает, что значения NULL равны, поэтому вы не можете вставить несколько значений NULL в столбец с уникальным ограничением.
Функция NULL в MySQL
MySQL предоставляет несколько полезных функций, которые эффективно обрабатывают NULL: IFNULL, COALESCE и NULLIF.
Функция IFNULL принимает два параметра. Функция IFNULL возвращает первый аргумент, если он не NULL, в противном случае она возвращает второй аргумент.
Например, следующий оператор возвращает номер телефона, если это не NULL, иначе он возвращает N/A вместо NULL.
SELECT
id,
first_name,
last_name,
IFNULL(phone, 'N/A') phone
FROM
leads;
Функция COALESCE принимает список аргументов и возвращает первый аргумент не-NULL. Например, вы можете использовать функцию COALESCE для отображения контактной информации о потенциальной возможности в зависимости от приоритета информации в следующем порядке: телефон, электронная почта и N/A.
SELECT
id,
first_name,
last_name,
COALESCE(phone, email, 'N/A') contact
FROM
leads;
Функция NULLIF принимает два аргумента. Если два аргумента равны, функция NULLIF возвращает NULL. В противном случае она возвращает первый аргумент.
Функция NULLIF полезна, если у вас есть как NULL и пустые строковые значения в столбце. Например, по ошибке вы вставляете в таблицу leads следующую строку :
INSERT INTO leads(first_name,last_name,source,email,phone)
VALUE('Andreyex','Destroyer','Web Search','Andreyex.Destroyer@example.ru','');
Телефон представляет собой пустую строку вместо NULL.
Если вы хотите получить контактную информацию о потенциальных клиентах, вы получите пустой телефон вместо электронного письма в виде следующего запроса:
SELECT
id,
first_name,
last_name,
COALESCE(phone, email, 'N/A') contact
FROM
leads;
Чтобы исправить это, вы используете функцию NULLIF для сравнения телефона с пустой строкой, если они равны, он возвращает NULL, в противном случае он возвращает номер телефона.
SELECT
id,
first_name,
last_name,
COALESCE(NULLIF(phone, ''), email, 'N/A') contact
FROM
leads;
Из этой статьи вы узнали, как работать с NULL в MySQL и как использовать некоторые удобные функции для обработки запросов NULL.
2020-03-01T16:23:29
База данных MySQL
