Обработка значений 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

root@host# mysql -u root -p password; Введите пароль:******* mysql> use EXAMPLE; База данных изменена mysql> create table example_test_tbl -> ( -> example_author varchar(40) NOT NULL, -> example_count INT -> ); Запрос OK, 0 строк затронуто (0.05 сек) mysql> INSERT INTO example_test_tbl (example_author, example_count) values ('EXAMPLE', 20); mysql> INSERT INTO example_test_tbl (example_author, example_count) values ('Example Tutorial', NULL); mysql> INSERT INTO example_test_tbl (example_author, example_count) values ('Google', NULL); mysql> INSERT INTO example_test_tbl (example_author, example_count) values ('FK', 20); mysql> SELECT * from example_test_tbl; +---------------+--------------+ | example_author | example_count | +---------------+--------------+ | ПРИМЕР | 20| | Пример руководства |NULL | | Google | NULL | | FK | 20 | +---------------+--------------+ 4 строк в наборе (0.01 sec)

В следующем примере вы можете увидеть, что операторы = и != не работают:

mysql> SELECT * FROM example_test_tbl WHERE example_count = NULL; Пустой набор (0.00 sec) mysql> SELECT * FROM example_test_tbl WHERE example_count != NULL; Пустой набор (0.01 sec)

Чтобы определить, является ли столбец example_test_tbl в таблице данных NULL, необходимо использоватьIS NULLиIS NOT NULL, как в следующем:

mysql> SELECT * FROM example_test_tbl WHERE example_count IS NULL; +---------------+--------------+ | example_author | example_count| +---------------+--------------+ | Пример руководства |NULL | | Google | NULL | +---------------+--------------+ 2 строк в наборе (0.01 sec) mysql> SELECT * FROM example_test_tbl WHERE example_count IS NOT NULL; +---------------+--------------+ | example_author | example_count | +---------------+--------------+ | EXAMPLE | 20 | | FK | 20 | +---------------+--------------+ 2 строк в наборе (0.01 sec)

Использование PHP-скриптов для обработки значений NULL

В PHP-скриптах можно использовать операторы if...else, чтобы определить, является ли переменная пустой, и сгенерировать соответствующие условные операторы.

В следующем примере PHP устанавливает переменную $example_count, а затем использует эту переменную для сравнения с полем example_count в таблице данных:

Тест MySQL ORDER BY:

<?php $dbhost = 'localhost'; //адрес хоста сервера MySQL $dbuser = 'root'; //имя пользователя MySQL $dbpass = '123456'; //пароль пользователя MySQL $conn = mysqli_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die('Ошибка соединения:' . mysqli_error($conn)); } //Установите кодировку для предотвращения искажения китайских символов mysqli_query($conn , "set names utf8"); if( isset($example_count )) { $sql = "SELECT example_author, example_count FROM example_test_tbl WHERE example_count = $example_count"; } else { $sql = "SELECT example_author, example_count FROM example_test_tbl WHERE example_count IS NULL"; } mysqli_select_db( $conn, 'EXAMPLE' ); $retval = mysqli_query( $conn, $sql ); if(! $retval ) { die('Не удалось прочитать данные:' . mysqli_error($conn)); } echo '<h2>Пример руководства: тест IS NULL<h2>'; echo '<table border="1"><tr><td>Автор</td><td>Число входов</td></tr>'; while($row = mysqli_fetch_array($retval, MYSQL_ASSOC)) { echo "<tr>". "<td>{$row['example_author']} </td> ". "<td>{$row['example_count']} </td> ". "</tr>"; } echo '</table>'; mysqli_close($conn); ?>

Результат вывода показан на следующем рисунке:

Другие расширения