Manejo de valores NULL en MySQL

Ya sabemos que MySQL usaSELECTel comando yWHEREcláusula para leer los datos de las tablas, pero cuando el campo de condición de consulta proporcionado es NULL, el comando puede no funcionar correctamente.

En MySQL, NULL se utiliza para representar datos faltantes o desconocidos. El manejo de valores NULL requiere especial cuidado, porque en la base de datos puede producir resultados diferentes a los esperados.

Para manejar esta situación, MySQL proporciona tres operadores principales:

  • IS NULL:Cuando el valor de la columna es NULL, este operador devuelve true.
  • IS NOT NULL:Cuando el valor de la columna no es NULL, el operador devuelve true.
  • <=>:El operador de comparación (diferente del operador =), devuelve true cuando los dos valores comparados son iguales o ambos son NULL.

La comparación condicional con NULL es especial. No puedes usar = NULL o != NULL para buscar valores NULL en una columna.

En MySQL, la comparación de NULL con cualquier otro valor (incluso con NULL) siempre devuelve NULL, es decir, NULL = NULL devuelve NULL.

En MySQL, para manejar NULL se usan los operadores IS NULL e IS NOT NULL.

Nota:

select * , columnName1+ifnull(columnName2,0) from tableName;

columnName1, columnName2 son de tipo int. Cuando en columnName2 hay un valor null, columnName1+columnName2=null; ifnull(columnName2,0) convierte el valor null de columnName2 en 0.

Consideraciones y técnicas comunes para manejar valores NULL en MySQL

1. Verificar si es NULL:

Para verificar si una columna es NULL, puedes usar las condiciones IS NULL o IS NOT NULL.

SELECT * FROM employees WHERE department_id IS NULL;
SELECT * FROM employees WHERE department_id IS NOT NULL;

2. Usar la función COALESCE para manejar NULL:

La función COALESCE se puede usar para reemplazar valores NULL; acepta múltiples parámetros y devuelve el primer valor no NULL de la lista de parámetros:

SELECT product_name, COALESCE(stock_quantity, 0) AS actual_quantity
FROM products;

En la declaración SQL anterior, si la columna stock_quantity es NULL, COALESCE devolverá 0.

3. Usar la función IFNULL para manejar NULL:

La función IFNULL es la versión específica de MySQL de COALESCE; acepta dos parámetros, y si el primer parámetro es NULL, devuelve el segundo parámetro.

SELECT product_name, IFNULL(stock_quantity, 0) AS actual_quantity
FROM products;

4. Ordenamiento de NULL:

Al ordenar con la cláusula ORDER BY, los valores NULL se colocan al final por defecto. Si deseas colocar los valores NULL al principio, puedes usar ORDER BY column_name ASC NULLS FIRST; por el contrario, usa ORDER BY column_name DESC NULLS LAST.

SELECT product_name, price
FROM products
ORDER BY price ASC NULLS FIRST;

5. Usar el operador <=> para comparaciones con NULL:

El operador <=> es un operador especial en MySQL que compara si dos expresiones son iguales; también devuelve TRUE al comparar valores NULL. Puede usarse para realizar comparaciones de igualdad con valores NULL.

SELECT * FROM employees WHERE commission <=> NULL;

6. Ten en cuenta el manejo de NULL en las funciones de agregación:

Al usar funciones de agregación (como COUNT, SUM, AVG), estas ignoran los valores NULL, por lo que los resultados pueden diferir de lo esperado. Si deseas tratar NULL como 0, puedes usar COALESCE o IFNULL.

SELECT AVG(COALESCE(salary, 0)) AS avg_salary FROM employees;

De esta manera, incluso si salary es NULL, la función de agregación lo tratará como 0.

Al manejar valores NULL, debes tener especial cuidado para asegurar que la semántica de las consultas y operaciones sea la esperada. Al diseñar la estructura de la tabla, también hay que considerar la utilidad y razonabilidad del uso de valores NULL.


Usar valores NULL en el símbolo del sistema

En el siguiente ejemplo, supongamos que la tabla ejemplo_test_tbl en la base de datos EJEMPLO contiene dos columnas: ejemplo_author y ejemplo_count, y en ejemplo_count se insertan valores NULL.

Ejemplo

Prueba el siguiente ejemplo:

Crear la tabla de datos ejemplo_test_tbl

root@host# mysql -u root -p password; Enter password:******* mysql> use EJEMPLO; Database changed mysql> create table ejemplo_test_tbl -> ( -> ejemplo_author varchar(40) NOT NULL, -> ejemplo_count INT -> ); Query OK, 0 rows affected (0.05 sec) mysql> INSERT INTO ejemplo_test_tbl (ejemplo_author, ejemplo_count) values ('EJEMPLO', 20); mysql> INSERT INTO ejemplo_test_tbl (ejemplo_author, ejemplo_count) values ('Tutorial para novatos', NULL); mysql> INSERT INTO ejemplo_test_tbl (ejemplo_author, ejemplo_count) values ('Google', NULL); mysql> INSERT INTO ejemplo_test_tbl (ejemplo_author, ejemplo_count) values ('FK', 20); mysql> SELECT * from ejemplo_test_tbl; +---------------+--------------+ | ejemplo_author | ejemplo_count | +---------------+--------------+ | EJEMPLO | 20| | Tutorial para novatos |NULL | | Google | NULL | | FK | 20 | +---------------+--------------+ 4 rows in set (0.01 sec)

En el siguiente ejemplo puedes ver que los operadores = y != no funcionan:

mysql> SELECT * FROM ejemplo_test_tbl WHERE ejemplo_count = NULL; Empty set (0.00 sec) mysql> SELECT * FROM ejemplo_test_tbl WHERE ejemplo_count != NULL; Empty set (0.01 sec)

Para buscar si la columna ejemplo_test_tbl en la tabla de datos es NULL, debes usarIS NULLyIS NOT NULL, como en el siguiente ejemplo:

mysql> SELECT * FROM ejemplo_test_tbl WHERE ejemplo_count IS NULL; +---------------+--------------+ | ejemplo_author | ejemplo_count| +---------------+--------------+ | Tutorial para novatos |NULL | | Google | NULL | +---------------+--------------+ 2 rows in set (0.01 sec) mysql> SELECT * from ejemplo_test_tbl WHERE ejemplo_count IS NOT NULL; +---------------+--------------+ | ejemplo_author | ejemplo_count | +---------------+--------------+ | EJEMPLO | 20 | | FK | 20 | +---------------+--------------+ 2 rows in set (0.01 sec)

Manejar valores NULL usando scripts PHP

En los scripts PHP, puedes usar la sentencia if...else para verificar si una variable está vacía y generar la declaración condicional correspondiente.

En el siguiente ejemplo, PHP establece la variable $ejemplo_count y luego la usa para compararla con el campo ejemplo_count de la tabla de datos:

Prueba de ORDER BY en MySQL:

<?php $dbhost = 'localhost'; //Dirección del host del servidor MySQL $dbuser = 'root'; //Nombre de usuario de MySQL $dbpass = '123456'; //Contraseña del usuario de MySQL $conn = mysqli_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die('Error de conexión:' . mysqli_error($conn)); } //Configurar la codificación para evitar caracteres chinos distorsionados mysqli_query($conn , "set names utf8"); if( isset($ejemplo_count )) { $sql = "SELECT ejemplo_author, ejemplo_count FROM ejemplo_test_tbl WHERE ejemplo_count = $ejemplo_count"; } else { $sql = "SELECT ejemplo_author, ejemplo_count FROM ejemplo_test_tbl WHERE ejemplo_count IS NULL"; } mysqli_select_db( $conn, 'EJEMPLO' ); $retval = mysqli_query( $conn, $sql ); if(! $retval ) { die('No se pueden leer los datos:' . mysqli_error($conn)); } echo '<h2>Tutorial para novatos Prueba IS NULL<h2>'; echo '<table border="1"><tr><td>Autor</td><td>Número de inicios de sesión</td></tr>'; while($row = mysqli_fetch_array($retval, MYSQL_ASSOC)) { echo "<tr>". "<td>{$row['ejemplo_author']} </td> ". "<td>{$row['ejemplo_count']} </td> ". "</tr>"; } echo '</table>'; mysqli_close($conn); ?>

El resultado de salida se muestra en la siguiente figura:

Otras extensiones