MySQL NULL value handling

MySQL NULL value processing

We already know that MySQL uses the SQL SELECT command and WHERE clause to read the data in the data table, but when the provided When the query condition field 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 operation symbol returns true.

• IS NOT NULL: When the column value is not NULL, the operator returns true.

• <=>: Comparison operator (different from = operator), returns true when the two compared values ​​are NULL.

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.

The IS NULL and IS NOT NULL operators are used to handle NULL in MySQL.

Note:

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

columnName1, columnName2 are of type int. When there is a value in columnName2 that is null, columnName1+columnName2=null, ifnull(columnName2,0) Convert the null value in columnName2 to 0.

Use NULL values ​​in the command prompt

The following example assumes that the table php_test_tbl in the database php contains two columns php_author and php_count,php_count Insert NULL value in settings.

Examples

Try the following examples:

Create data table php_test_tbl

mysql> use php;
Database changed
mysql> create table php_test_tbl
    -> (
    -> php_author varchar(40) NOT NULL,
    -> php_count  INT
    -> );
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO php_test_tbl (php_author, php_count)
 values ('PHP', 20);
 mysql> INSERT INTO php_test_tbl (php_author, php_count) values ('PHP中文网', NULL);
 mysql> INSERT INTO php_test_tbl (php_author,php_count) values ('Google', NULL);
 mysql> INSERT INTO php_test_tbl (php_author, php_count) values ('FK', 20); 
mysql> SELECT * from php_test_tbl;
+---------------+--------------+
| php_author | php_count |
+---------------+--------------+
| PHP        | 20           |
| php中文网  | NULL         |
| Google        | NULL         |
| FK            | 20           |
+---------------+--------------+
 4 rows in set (0.01 sec)

In the following examples you You can see that the = and != operators do not work:

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

To find whether the php_test_tbl column in the data table is NULL, you must use IS NULL and IS NOT NULL, as shown in the following example:

mysql> SELECT * FROM php_test_tbl WHERE php_count IS NULL;
+---------------+--------------+
| php_author | php_count |
+---------------+--------------+
| PHP中文网  | NULL         |
| Google        | NULL         |
+---------------+--------------+
2 rows in set (0.01 sec)
 mysql> SELECT * from php_test_tbl WHERE php_count IS NOT NULL;
+---------------+--------------+
| php_author | php_count |
+---------------+--------------+
| PHP       | 20           |
| FK            | 20           |
+---------------+--------------+
2 rows in set (0.01 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 $php_count variable, and then uses this variable to compare with the php_count field in the data table: