English | 简体中文 | 繁體中文 | Русский язык | Français | Español | Português | Deutsch | 日本語 | 한국어 | Italiano | بالعربية

MySql Tutorial

MySQL NULL Value Handling

We know that MySQL uses the SQL SELECT command and WHERE clause to read data from the data table, but when the query condition field provided is NULL, this command may not work normally.

To handle this situation, MySQL provides three major operators:

  • IS NULL: When the value of the column is NULL, this operator returns true.

  • IS NOT NULL: When the value of the column is not NULL, the operator returns true.

  • <=>:　The comparison operator (different from the = operator) returns true when the two compared values are equal or both are NULL.

The conditional comparison operation of NULL is special. You cannot use = NULL or != NULL to find NULL values in a column.

In MySQL, NULL values are compared with any other values (including NULL) using IS NULL and IS NOT NULL operators.

In MySQL, NULL values compared with any other value (even NULL) always return NULL, that is, NULL = NULL returns NULL.

Note:

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

columnName1, columnName2 is of int type, when columnName2 has a null value, columnName1+columnName2=null, ifnull(columnName2,0) to columnName2 Convert null values to 0.

Use NULL values in the command prompt

In the following example, assume the database w3table w in codebox3codebox_test_tbl contains two columns w3codebox_author and w3codebox_count, w3Set NULL value in codebox_count.

Online example

Try the following example:

root@host# mysql　-u root　-p password;
Enter password:*******
mysql> use w3codebox;
Database changed
mysql> create table w3codebox_test_tbl
　　　　-> (
　　　　-> w3codebox_author varchar(40) NOT NULL,
　　　　-> w3codebox_count INT
　　　　-> );
Query OK, 0 rows affected (0.05　sec)
mysql> INSERT INTO w3codebox_test_tbl (w3codebox_author,3codebox_count) values ('w3codebox',　20);
mysql> INSERT INTO w3codebox_test_tbl (w3codebox_author,3codebox_count) values ('基础教程网', NULL);
mysql> INSERT INTO w3codebox_test_tbl (w3codebox_author,3codebox_count) values ('Google', NULL);
mysql> INSERT INTO w3codebox_test_tbl (w3codebox_author,3codebox_count) values ('FK',　20);
　
mysql> SELECT　*　from w3codebox_test_tbl;
+---------------+--------------+
| w3codebox_author                                 | w3codebox_count                                 |
+---------------+--------------+
| w3codebox                                     |　20                                             |
| 基础教程网                                     | NULL                                         |
| Google                                         | NULL                                         |
| FK                                             |　20                                             |
+---------------+--------------+
4　rows in set (0.01　sec)

In the following examples, you can see that the = and != operators are not working:

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

to find data in the w3to check if the codebox_test_tbl column is NULL, you must use IS NULL and IS NOT NULLAs shown in the following example:

mysql> SELECT　*　FROM w3codebox_test_tbl WHERE w3codebox_count IS NULL;
+---------------+--------------+
| w3codebox_author                                 | w3codebox_count                                 |
+---------------+--------------+
| 基础教程网                                     | NULL                                         |
| Google                                         | NULL                                         |
+---------------+--------------+
2　rows in set (0.01　sec)
　
mysql> SELECT　*　from w3codebox_test_tbl WHERE w3codebox_count IS NOT NULL;
+---------------+--------------+
| w3codebox_author                                 | w3codebox_count                                 |
+---------------+--------------+
| w3codebox                                     |　20                                             |
| FK                                             |　20                                             |
+---------------+--------------+
2　rows in set (0.01　sec)

Use PHP script to handle NULL values

In PHP scripts, you can use if...else statements to handle whether variables are empty and generate corresponding conditional statements.

In the following example, PHP sets $w3codebox_count variable, and then use this variable to compare with the w3codebox_count field comparison:

<?php
$dbhost = 'localhost';　　//　MySQL server host address
$dbuser = 'root';　　　　　　　　　　　　//　MySQL username
$dbpass = '123456;　　　　　　　　　　//　MySQL username and password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if (!$conn)
{
　　　　die('Connection failed: ' . mysqli_error($conn));
}
//　Set encoding to prevent Chinese garbled characters
mysqli_query($conn, "set names utf8;
　
if(isset($w3codebox_count))
{
　　　$sql = "SELECT3codebox_author,3codebox_count
　　　　　　　　　　　FROM3codebox_test_tbl
　　　　　　　　　　　WHERE w3codebox_count = $w3codebox_count";
}
else
{
　　　$sql = "SELECT3codebox_author,3codebox_count
　　　　　　　　　　　FROM3codebox_test_tbl
　　　　　　　　　　　WHERE w3codebox_count IS NULL";
}
mysqli_select_db($conn, 'w3codebox' );
$retval = mysqli_query($conn, $sql);
if(! $retval)
{
　　　　die('Unable to read data: ' . mysqli_error($conn));
}
echo '<h2Basic Tutorial(oldtoolbag.com) IS NULL Test<h2">';
echo '<table border="1><tr><td>Author</td><td>Login Times</td></tr>';
while($row = mysqli_fetch_array($retval))
{
　　　　echo "<tr>".
　　　　　　　　　"<td>{$row['w3codebox_author']} </td> ".
　　　　　　　　　"<td>{$row['w3codebox_count'} </td> ".
　　　　　　　　　"</tr>";
}
echo '</table>';
mysqli_close($conn);
?>

The output result is shown as follows:


MySQL Regular ExpressionsMySQL Use of Joins