MySQL NULL value processing

We already know MySQL using the SQL SELECT command with WHERE clause to read the data in the data table, but the query criteria fields provided when NULL, the command may not work properly when.

To handle this case, MySQL provides three major operators:

• IS NULL: When the value of the column is NULL, the operator returns true.
• IS NOT NULL: When the value of the column is not NULL, the operator returns true.
• <=>: Comparison operators (= different from the operator) and returns true when comparing the two value is NULL.

About NULL condition comparison operation is special. You can not use = NULL or! = NULL find NULL values ​​in the column.

In MySQL, NULL values ​​compare with any other value (even NULL) always returns false, that is, NULL = NULL returns false.

MySQL handling NULL using the IS NULL and IS NOT NULL operator.

NULL values ​​in the command prompt

The following example assumes that the database w3big tables tcount_tbl containing two w3big_author and w3big_count, w3big_count set insert NULL values.

Examples

Try the following examples:

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

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

mysql>

The following example you can see = and = operator does not work!:

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

Find the data table w3big_count whether the column is NULL, you must use the IS NULL and IS NOT NULL, the following examples:

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

Use PHP script processing NULL value

PHP scripts you can if ... else statement to handle if a variable is empty, and generates the corresponding conditional statement.

The following examples set $ w3big_count PHP variable, then use the variable with the data table w3big_count fields comparison:

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

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