❮ Mysql Handling Duplicates Mysql Create Database ❯

MySQL NULL Value Handling

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

To handle this situation, MySQL provides three operators:

IS NULL: This operator returns true when the column value is NULL.
IS NOT NULL: This operator returns true when the column value is not NULL.
<=>: This comparison operator (different from the = operator) returns true when the two values being compared are either equal or both are NULL.

Comparisons involving NULL are special. You cannot use = NULL or != NULL to find NULL values in a column.

In MySQL, a comparison between NULL and any other value (even NULL) always returns NULL, i.e., NULL = NULL returns NULL.

To handle NULL in MySQL, use the IS NULL and IS NOT NULL operators.

Note:

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

columnName1 and columnName2 are of type int. When columnName2 contains a NULL value, columnName1 + columnName2 = NULL. The ifnull(columnName2,0) function converts NULL values in columnName2 to 0.

Using NULL Values in the Command Prompt

The following example assumes the database tutorialpro contains a table tutorialpro_test_tbl with two columns, tutorialpro_author and tutorialpro_count, where tutorialpro_count is set to accept NULL values.

Example

Try the following example:

Create the tutorialpro_test_tbl table

root@host# mysql -u root -p password;
Enter password:*******
mysql> use tutorialpro;
Database changed
mysql> create table tutorialpro_test_tbl
    -> (
    -> tutorialpro_author varchar(40) NOT NULL,
    -> tutorialpro_count  INT
    -> );
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO tutorialpro_test_tbl (tutorialpro_author, tutorialpro_count) values ('tutorialpro', 20);
mysql> INSERT INTO tutorialpro_test_tbl (tutorialpro_author, tutorialpro_count) values ('tutorialpro.org', NULL);
mysql> INSERT INTO tutorialpro_test_tbl (tutorialpro_author, tutorialpro_count) values ('Google', NULL);
mysql> INSERT INTO tutorialpro_test_tbl (tutorialpro_author, tutorialpro_count) values ('FK', 20);

mysql> SELECT * from tutorialpro_test_tbl;
+---------------+--------------+
| tutorialpro_author | tutorialpro_count |
+---------------+--------------+
| tutorialpro        | 20           |
| tutorialpro.org  | NULL         |
| Google        | NULL         |
| FK            | 20           |
+---------------+--------------+
4 rows in set (0.01 sec)

The following example shows that the = and != operators do not work:

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

To find if a column in the tutorialpro_test_tbl table is NULL, you must use IS NULL and IS NOT NULL, as shown in the following example:

mysql> SELECT * FROM tutorialpro_test_tbl WHERE tutorialpro_count IS NULL;
+---------------+--------------+
| tutorialpro_author | tutorialpro_count |
+---------------+--------------+
| tutorialpro.org  | NULL         |
| Google        | NULL         |
+---------------+--------------+
2 rows in set (0.01 sec)

mysql> SELECT * from tutorialpro_test_tbl WHERE tutorialpro_count IS NOT NULL;
+---------------+--------------+
| tutorialpro_author | tutorialpro_count |
+---------------+--------------+
| tutorialpro        | 20           |
| FK            | 20           |
+---------------+--------------+
2 rows in set (0.01 sec)

Handling NULL Values with PHP Scripts

In PHP scripts, you can use if...else statements to handle whether a variable is empty and generate corresponding conditional statements.

The following example sets the $tutorialpro_count variable in PHP and then compares it with the tutorialpro_count field in the table:

MySQL ORDER BY Test:

<?php
$dbhost = 'localhost';  // mysql server host address
$dbuser = 'root';       // mysql username
$dbpass = '123456';     // mysql user password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die('Connection failed: ' . mysqli_error($conn));
}
// Set encoding to prevent Chinese乱码
mysqli_query($conn , "set names utf8");

if( isset($tutorialpro_count ))
{
   $sql = "SELECT tutorialpro_author, tutorialpro_count
           FROM  tutorialpro_test_tbl
           WHERE tutorialpro_count = $tutorialpro_count";
}
else
{
   $sql = "SELECT tutorialpro_author, tutorialpro_count
           FROM  tutorialpro_test_tbl
           WHERE tutorialpro_count IS NULL";
}
mysqli_select_db( $conn, 'tutorialpro' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
    die('Could not read data: ' . mysqli_error($conn));
}
echo '<h2>tutorialpro.org IS NULL Test<h2>';
echo '<table border="1"><tr><td>Author</td><td>Login Count</td></tr>';
while($row = mysqli_fetch_array($retval, MYSQL_ASSOC))
{
    echo "<tr>".
         "<td>{$row['tutorialpro_author']} </td> ".
         "<td>{$row['tutorialpro_count']} </td> ".
         "</tr>";
}
echo '</table>';
mysqli_close($conn);
?>

The output result is shown in the following figure:

❮ Mysql Handling Duplicates Mysql Create Database ❯