MySQL NULL value handling

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, the command may not work properly.

In order 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 column value is not NULL, the operator returns true.

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

In MySQL, comparison of a NULL value with any other value (even NULL) always returns false, that is, NULL = NULL returns false.

MySQL handles NULL using the IS NULL and IS NOT NULL operators.

Use NULL values ​​in the command prompt

In the following example, it is assumed that the table tcount_tbl in the database RUNOOB contains two columns, runoob_author and runoob_count, and a NULL value is set in runoob_count.

Try the following example:

MariaDB [RUNOOB]> select * from tcount_tbl;
+---------------+------------- -+
| runoob_author | runoob_count |
+---------------+--------------+
| mahran | 20 |
| mahran |       NULL |
| Jen                                     1 |
+---------------+---- ----------+
6 rows in set (0.00 sec)


You can see that the = and != operators do not work in the following example:

MariaDB [RUNOOB]> SELECT * FROM tcount_tbl WHERE runoob_count = NULL;Empty set (0.00 sec)

MariaDB [RUNOOB]> SELECT * FROM tcount_tbl WHERE runoob_count != NULL;Empty set (0.00 sec)

Find whether the runoob_count column in the data table is NULL, IS NULL and IS NOT NULL must be used, as shown in the following example:

MariaDB [RUNOOB]> SELECT * FROM tcount_tbl where runoob_count IS NULL;

+--------------------------+- -------------+

| runoob_author | runoob_count |

+---------------+------------ --+
| mahran NULL |
| Jen rows in set (0.00 sec)

MariaDB [RUNOOB]> SELECT * FROM tcount_tbl where runoob_count IS NOT NULL;
+---------------+------ -------+
| runoob_author | runoob_count |
+---------------+--------------+
| mahran |                                                                                                                                                                    ----+
4 rows in set (0.00 sec)


Use PHP script to handle NULL values

In PHP script, you can use if...else statement to process whether the variable is empty and generate the corresponding conditional statement .

In the following example, PHP sets the $runoob_count variable, and then uses this variable to compare with the runoob_count field in the data table:

$dbhost ='localhost:3036';

$dbuser ='root ';

$dbpass ='rootpassword';

$conn = mysql_connect($dbhost, $dbuser, $dbpass);

if(! $conn )

{

die('Could not connect: '. mysql_error());

}

if( isset($runoob_count ))

{

$sql ='SELECT runoob_author, runoob_count

                                                                                     runoob_count IS NOT NULL';

}

else

{

$sql ='SELECT runoob_author, runoob_count

FROM tcount_tbl

WHERE runoob_count IS NULL';

}

mysql _select_db('RUNOOB');

$retval = mysql_query( $sql, $ conn );

if(! $retval )

{

die('Could not get data: '. mysql_error());

}

while($row = mysql_fetch_array($retval, MYSQL_ASSOC))

{

echo "Author:{$row['runoob_author']}
".

"Count: {$row['runoob_count']}
".

"---- ----------------------------
";

}

echo "Fetched data successfullyn";

mysql_close( $conn);

?>

Run result: