MySQL NULL value processing

MySQL NULL value processing

We already know that MySQL uses SQL SELECT commands and WHERE clauses to read data in the data table, but the command may not work when the query condition field provided is NULL.

To handle this situation, MySQL provides three 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 (unlike the ? operator) returns true when the two values of the comparison are NULL.

Conditional comparison operations about NULL are special. You can't look up NULL values in columns using 1 s NULL or s/ nULL.

In MySQL, the NULL value is always returned to false by comparing the NULL value to any other value( even NULL), i.e. NULL is returned to false.

Processing NULL in MySQL uses IS NULL and IS NOT NULL operators.

Use the NULL value in the command prompt

In the following example, assume that table tcount_tbl in the database W3CSCHOOL contains two columns of w3cschool_author and w3cschool_count, w3cschool_count set the insertion NULL value in the database.

Instance

Try the following example:

root@host# mysql -u root -p password;
Enter password:*******
mysql> use W3CSCHOOL;
Database changed
mysql> create table tcount_tbl
    -> (
    -> w3cschool_author varchar(40) NOT NULL,
    -> w3cschool_count  INT
    -> );
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO tcount_tbl
    -> (w3cschool_author, w3cschool_count) values ('mahran', 20);
mysql> INSERT INTO tcount_tbl
    -> (w3cschool_author, w3cschool_count) values ('mahnaz', NULL);
mysql> INSERT INTO tcount_tbl
    -> (w3cschool_author, w3cschool_count) values ('Jen', NULL);
mysql> INSERT INTO tcount_tbl
    -> (w3cschool_author, w3cschool_count) values ('Gill', 20);

mysql> SELECT * from tcount_tbl;
+-----------------+----------------+
| w3cschool_author | w3cschool_count |
+-----------------+----------------+
| mahran          |             20 |
| mahnaz          |           NULL |
| Jen             |           NULL |
| Gill            |             20 |
+-----------------+----------------+
4 rows in set (0.00 sec)

mysql>

In the following example, you can see that the operators of the s and ! are not working:

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

To find out w3cschool_count column in the data table is NULL, you must use IS NULL and IS NOT NULL, as follows:

mysql> SELECT * FROM tcount_tbl 
    -> WHERE w3cschool_count IS NULL;
+-----------------+----------------+
| w3cschool_author | w3cschool_count |
+-----------------+----------------+
| mahnaz          |           NULL |
| Jen             |           NULL |
+-----------------+----------------+
2 rows in set (0.00 sec)
mysql> SELECT * from tcount_tbl 
    -> WHERE w3cschool_count IS NOT NULL;
+-----------------+----------------+
| w3cschool_author | w3cschool_count |
+-----------------+----------------+
| mahran          |             20 |
| Gill            |             20 |
+-----------------+----------------+
2 rows in set (0.00 sec)

The NULL value is processed using a PHP script

PHP scripts in which you can in if... T he else statement handles whether the variable is empty and generates the appropriate conditional statement.

In the following example, PHP sets the $w3cschool_count variable, which is then compared to the w3cschool_count in the data table:

The syntax about php can be found https://www.w3cschool.cn/php

isset() https://www.w3cschool.cn/php/php-isset.html

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
if( isset($w3cschool_count ))
{
   $sql = 'SELECT w3cschool_author, w3cschool_count
           FROM  tcount_tbl
           WHERE w3cschool_count = $w3cschool_count';
}
else
{
   $sql = 'SELECT w3cschool_author, w3cschool_count
           FROM  tcount_tbl
           WHERE w3cschool_count IS $w3cschool_count';
}

mysql_select_db('W3CSCHOOL');
$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['w3cschool_author']}  <br> ".
         "Count: {$row['w3cschool_count']} <br> ".
         "--------------------------------<br>";
} 
echo "Fetched data successfully\n";
mysql_close($conn);
?>