MySQL NULL value processing example tutorial

MySQL NULLValue processing

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

In order to handle this situation, MySQL provides three major operators:

IS NULL: When the column value is NULL, this operator 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 finds NULL values ​​in the column.

In MySQL, comparison of a NULL value with any other value (even NULL) always returns false, that is, NULL = NULL returns false.

MySQL handles NULL using the IS NULL and IS NOT NULL operators.

Using NULL values ​​in the command prompt

The following example assumes that the table tcount_tbl in the database guide contains two columns, tutorial_author and tutorial_count, and inserts a NULL value in tutorial_count.

Try the following examples:

root @ host＃mysql -u root -p password;
输入密码：*******
mysql> use TUTORIALS;数据库已更改mysql> create table tcount_tbl
    - >（
    - > tutorial_author varchar（40）NOT NULL，
    - > tutorial_count INT
    - >）;
查询OK，0行受影响（0.05秒）
mysql> INSERT INTO tcount_tbl
    - >（tutorial_author，tutorial_count）值（'mahran'，20）;
mysql> INSERT INTO tcount_tbl
    - >（tutorial_author，tutorial_count）values（'mahnaz'，NULL）;
mysql> INSERT INTO tcount_tbl
    - >（tutorial_author，tutorial_count）值（'Jen'，NULL）;
mysql> INSERT INTO tcount_tbl
    - >（tutorial_author，tutorial_count）值（'Gill'，20）;
mysql> select * from tcount_tbl;
+ ----------------- + ---------------- +
| tutorial_author | tutorial_count |
+ ----------------- + ---------------- +
| 马赫兰 20 |
| mahnaz | NULL |
| 仁| NULL |
| 鳃| 20 |
+ ----------------- + ---------------- +
4行（0.00秒）
MySQL的>

In the following examples you can see = and! The = operator does not work

mysql> SELECT * FROM tcount_tbl WHERE tutorial_count = NULL;
空置（0.00秒）
mysql> SELECT * FROM tcount_tbl WHERE tutorial_count！= NULL;
空置（0.01秒）

To query whether the tutorial_count 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 tcount_tbl 
    - > WHERE tutorial_count IS NULL;
+ ----------------- + ---------------- +
| tutorial_author | tutorial_count |
+ ----------------- + ---------------- +
| mahnaz | NULL |
| 仁| NULL |
+ ----------------- + ---------------- +
2行（0.00秒）
mysql> select * from tcount_tbl 
    - > WHERE tutorial_count is NOT NULL;
+ ----------------- + ---------------- +
| tutorial_author | tutorial_count |
+ ----------------- + ---------------- +
| 马赫兰 20 |
| 鳃| 20 |
+ ----------------- + ---------------- +
2行（0.00秒）

Use a PHP script to process NULL values

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

In the following example, PHP sets the $tutorial_count variable, and then uses this variable to compare with the tutorial_count field in the data table:

<？PHP
$ dbhost =&#39;localhost：3036&#39;;
$ dbuser =&#39;root&#39;;
$ dbpass =&#39;rootpassword&#39;;
$ conn = mysql_connect（$ dbhost，$ dbuser，$ dbpass）;
if（！$ conn）
{
  die（&#39;无法连接：&#39;。mysql_error（））;
}
if（isset（$ tutorial_count））
{
   $ sql =&#39;SELECT tutorial_author，tutorial_count
           FROM tcount_tbl
           WHERE tutorial_count = $ tutorial_count&#39;;
}
其他
{
   $ sql =&#39;SELECT tutorial_author，tutorial_count
           FROM tcount_tbl
           WHERE tutorial_count IS $ tutorial_count&#39;;
}
mysql_select_db（ &#39;教程&#39;）;
$ retval = mysql_query（$ sql，$ conn）;
如果（！$ retval）
{
  die（&#39;无法获取数据：&#39;mysql_error（））;
}
while（$ row = mysql_fetch_array（$ retval，MYSQL_ASSOC））
{
    echo“作者：{$ row [&#39;tutorial_author&#39;]} <br>”。
         “Count：{$ row [&#39;tutorial_count&#39;]} <br>”。
         “--------------------------------结果”;
} 
echo“成功获取数据\ n”;
mysql_close（$康恩）;
？>

[Related recommendations]

1. Special recommendation: "php Programmer Toolbox" V0.1 version download

2. Free mysql online video Tutorial

3. Those things about database design

The above is the detailed content of MySQL NULL value processing example tutorial. For more information, please follow other related articles on the PHP Chinese website!