MySQL NULL value processing instance details, mysqlnull

MySQL NULL value processing instance details, mysqlnull

MySQL NULL value processing

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

To handle this situation, MySQL provides three operators:

• IS NULL: If the column value is NULL, this operator returns true.
• IS NOT NULL: Returns true if the column value is not NULL.
• <=>: Comparison operator (different from the = Operator). If the two values for comparison are NULL, true is returned.
  

The condition comparison operation for NULL is special. You cannot use = NULL or! = NULL: searches for NULL values in the column.

In MySQL, if the NULL value is compared with any other value (even if it is NULL), false is always returned. That is, if the NULL value is NULL, false is returned.

MySQL uses the is null and is not null operators to process NULL.

Use the NULL value in the command prompt

In the following example, assume that the table tcount_tbl in the database tutorial_author contains two columns: tutorial_count and tutorial_count.

Instance

Try the following instances:

root@host# mysql -u root -p password;Enter password:*******mysql> use TUTORIALS;Database changedmysql> create table tcount_tbl  -> (  -> tutorial_author varchar(40) NOT NULL,  -> tutorial_count INT  -> );Query OK, 0 rows affected (0.05 sec)mysql> INSERT INTO tcount_tbl  -> (tutorial_author, tutorial_count) values ('mahran', 20);mysql> INSERT INTO tcount_tbl  -> (tutorial_author, tutorial_count) values ('mahnaz', NULL);mysql> INSERT INTO tcount_tbl  -> (tutorial_author, tutorial_count) values ('Jen', NULL);mysql> INSERT INTO tcount_tbl  -> (tutorial_author, tutorial_count) values ('Gill', 20);mysql> SELECT * from tcount_tbl;+-----------------+----------------+| tutorial_author | tutorial_count |+-----------------+----------------+| mahran     |       20 || mahnaz     |      NULL || Jen       |      NULL || Gill      |       20 |+-----------------+----------------+4 rows in set (0.00 sec)mysql>

The = and! = Operator does not work:

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

Check whether the tutorial_count column in the data table is null. The is null and is not null must be used, as shown in the following example:

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

Use PHP scripts to process NULL values

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

In the following example, PHP sets the $ tutorial_count variable and compares it with the tutorial_count field in the data table:

<?php$dbhost = 'localhost:3036';$dbuser = 'root';$dbpass = 'rootpassword';$conn = mysql_connect($dbhost, $dbuser, $dbpass);if(! $conn ){ die('Could not connect: ' . mysql_error());}if( isset($tutorial_count )){  $sql = 'SELECT tutorial_author, tutorial_count      FROM tcount_tbl      WHERE tutorial_count = $tutorial_count';}else{  $sql = 'SELECT tutorial_author, tutorial_count      FROM tcount_tbl      WHERE tutorial_count IS $tutorial_count';}mysql_select_db('TUTORIALS');$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['tutorial_author']} <br> ".     "Count: {$row['tutorial_count']} <br> ".     "--------------------------------<br>";} echo "Fetched data successfully\n";mysql_close($conn);?>

Thank you for reading this article. I hope it will help you. Thank you for your support for this site!