Обработка значений NULL в MySQL
Мы уже знаем, что MySQL используетSELECTкоманду иWHEREусловие для чтения данных в таблице, но когда указанное поле условия запроса имеет значение NULL, команда может работать неправильно.
В MySQL NULL используется для представления отсутствующих или неизвестных данных. Обработка значений NULL требует особой осторожности, поскольку в базе данных она может привести к результатам, отличным от ожидаемых.
Для обработки этой ситуации MySQL предоставляет три основных оператора:
- IS NULL:Когда значение столбца равно NULL, этот оператор возвращает true.
- IS NOT NULL:Когда значение столбца не равно NULL, оператор возвращает true.
- <=>:Оператор сравнения (в отличие от оператора =), который возвращает true, когда два сравниваемых значения равны или когда оба равны NULL.
Условная операция сравнения для NULL является особой. Нельзя использовать = NULL или != NULL для поиска значений NULL в столбце.
В MySQL сравнение NULL с любым другим значением (даже с NULL) всегда возвращает NULL, то есть NULL = NULL возвращает NULL.
В MySQL используйте операторы IS NULL и IS NOT NULL для работы с NULL.
Примечание:
select * , columnName1+ifnull(columnName2,0) from tableName;columnName1 и columnName2 имеют тип int. Когда columnName2 имеет значение NULL, columnName1 + columnName2 = NULL. ifnull(columnName2,0) преобразует значение NULL в columnName2 в 0.
Общие соображения и советы по обработке значений NULL в MySQL
1. Проверьте, является ли оно NULL:
Чтобы проверить, является ли столбец NULL, можно использовать условия IS NULL или IS NOT NULL.
SELECT * FROM employees WHERE department_id IS NULL; SELECT * FROM employees WHERE department_id IS NOT NULL;
2. Используйте функцию COALESCE для обработки NULL:
Функция COALESCE может использоваться для замены значений NULL. Она принимает несколько аргументов и возвращает первое значение, не равное NULL, в списке аргументов:
SELECT product_name, COALESCE(stock_quantity, 0) AS actual_quantity FROM products;
В приведённой выше инструкции SQL, если столбец stock_quantity имеет значение NULL, COALESCE вернёт 0.
3. Используйте функцию IFNULL для обработки NULL:
Функция IFNULL — это специфичная для MySQL версия COALESCE. Она принимает два аргумента; если первый аргумент равен NULL, она возвращает второй аргумент.
SELECT product_name, IFNULL(stock_quantity, 0) AS actual_quantity FROM products;
4. Сортировка NULL:
При сортировке с помощью предложения ORDER BY значения NULL по умолчанию помещаются в конец сортировки. Если вы хотите поместить значения NULL в начало, используйте ORDER BY column_name ASC NULLS FIRST; наоборот, используйте ORDER BY column_name DESC NULLS LAST.
SELECT product_name, price FROM products ORDER BY price ASC NULLS FIRST;
5. Используйте оператор <=> для сравнения с NULL:
Оператор <=> — это специальный оператор в MySQL, используемый для сравнения двух выражений на равенство. Он также возвращает TRUE для сравнений, включающих значения NULL. Его можно использовать для сравнений на равенство со значениями NULL.
SELECT * FROM employees WHERE commission <=> NULL;
6. Обратите внимание на то, как агрегатные функции обрабатывают NULL:
При использовании агрегатных функций (таких как COUNT, SUM, AVG) они игнорируют значения NULL, что может привести к неожиданным результатам. Если вы хотите рассматривать NULL как 0, можно использовать COALESCE или IFNULL.
SELECT AVG(COALESCE(salary, 0)) AS avg_salary FROM employees;
Таким образом, даже если salary равно NULL, агрегатная функция будет рассматривать его как 0.
При обработке значений NULL будьте особенно внимательны, чтобы семантика запросов и операций соответствовала ожиданиям. При проектировании структуры таблиц также необходимо учитывать сценарии использования и уместность значений NULL.
Использование значений NULL в командной строке
В следующем примере предположим, что таблица example_test_tbl в базе данных EXAMPLE имеет два столбца, example_author и example_count, и в example_count вставлены значения NULL.
Пример
Попробуйте следующий пример:
Создание таблицы данных example_test_tbl
В следующем примере вы можете увидеть, что операторы = и != не работают:
Чтобы определить, является ли столбец example_test_tbl в таблице данных NULL, необходимо использоватьIS NULLиIS NOT NULL, как в следующем:
Использование PHP-скриптов для обработки значений NULL
В PHP-скриптах можно использовать операторы if...else, чтобы определить, является ли переменная пустой, и сгенерировать соответствующие условные операторы.
В следующем примере PHP устанавливает переменную $example_count, а затем использует эту переменную для сравнения с полем example_count в таблице данных:
Тест MySQL ORDER BY:
Результат вывода показан на следующем рисунке: