MySQL NULL 值处理
我们已经知道 MySQL 使用
SELECT
命令及
WHERE
子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时,该命令可能就无法正常工作。
在 MySQL 中,NULL 用于表示缺失的或未知的数据,处理 NULL 值需要特别小心,因为在数据库中它可能会导致不同于预期的结果。
为了处理这种情况,MySQL提供了三大运算符:
-
IS NULL:
当列的值是 NULL,此运算符返回 true。
-
IS NOT NULL:
当列的值不为 NULL, 运算符返回 true。
-
<=>:
比较操作符(不同于 = 运算符),当比较的的两个值相等或者都为 NULL 时返回 true。
关于 NULL 的条件比较运算是比较特殊的。你不能使用 = NULL 或 != NULL 在列中查找 NULL 值 。
在 MySQL 中,NULL 值与任何其它值的比较(即使是 NULL)永远返回 NULL,即 NULL = NULL 返回 NULL 。
MySQL 中处理 NULL 使用 IS NULL 和 IS NOT NULL 运算符。
注意:
select * , columnName1+ifnull(columnName2,0) from tableName;
columnName1,columnName2 为 int 型,当 columnName2 中,有值为 null 时,columnName1+columnName2=null,
ifnull(columnName2,0) 把 columnName2 中 null 值转为 0。
MySQL 中处理 NULL 值的常见注意事项和技巧
1. 检查是否为 NULL:
要检查某列是否为 NULL,可以使用 IS NULL 或 IS NOT NULL 条件。
SELECT * FROM employees WHERE department_id IS NULL;
SELECT * FROM employees WHERE department_id IS NOT NULL;
2. 使用 COALESCE 函数处理 NULL:
COALESCE 函数可以用于替换为 NULL 的值,它接受多个参数,返回参数列表中的第一个非 NULL 值:
SELECT product_name, COALESCE(stock_quantity, 0) AS actual_quantity
FROM products;
以上 SQL 语句中,如果 stock_quantity 列为 NULL,则 COALESCE 将返回 0。
3. 使用 IFNULL 函数处理 NULL:
IFNULL 函数是 COALESCE 的 MySQL 特定版本,它接受两个参数,如果第一个参数为 NULL,则返回第二个参数。
SELECT product_name, IFNULL(stock_quantity, 0) AS actual_quantity
FROM products;
4. NULL 排序:
在使用 ORDER BY 子句进行排序时,NULL 值默认会被放在排序的最后。如果希望将 NULL 值放在最前面,可以使用 ORDER BY column_name ASC NULLS FIRST,反之使用 ORDER BY column_name DESC NULLS LAST。
SELECT product_name, price
FROM products
ORDER BY price ASC NULLS FIRST;
5. 使用 <=> 操作符进行 NULL 比较:
<=> 操作符是 MySQL 中用于比较两个表达式是否相等的特殊操作符,对于 NULL 值的比较也会返回 TRUE。它可以用于处理 NULL 值的等值比较。
SELECT * FROM employees WHERE commission <=> NULL;
6. 注意聚合函数对 NULL 的处理:
在使用聚合函数(如 COUNT, SUM, AVG)时,它们会忽略 NULL 值,因此可能会得到不同于预期的结果。如果希望将 NULL 视为 0,可以使用 COALESCE 或 IFNULL。
SELECT AVG(COALESCE(salary, 0)) AS avg_salary FROM employees;
这样即使 salary 为 NULL,聚合函数也会将其视为 0。
处理 NULL 值时,要特别小心确保查询和操作的语义符合预期。在设计表结构时,也需要考虑 NULL 值的使用场景和合理性。
在命令提示符中使用 NULL 值
以下实例中假设数据库 yssmx 中的表 yssmx_test_tbl 含有两列 yssmx_author 和 yssmx_count, yssmx_count 中设置插入NULL值。
实例
尝试以下实例:
创建数据表 yssmx_test_tbl
root
@
host
Enter
password
:*******
mysql
>
use
yssmx
;
Database
changed
mysql
>
create
table
yssmx_test_tbl
->
(
->
yssmx_author
varchar
(
40
)
NOT
NULL
,
->
yssmx_count
INT
->
)
;
Query
OK
,
rows
affected
(
0.05
sec
)
mysql
>
INSERT
INTO
yssmx_test_tbl
(
yssmx_author
,
yssmx_count
)
values
(
'
yssmx
'
,
20
)
;
mysql
>
INSERT
INTO
yssmx_test_tbl
(
yssmx_author
,
yssmx_count
)
values
(
'
云搜索MX教程
'
,
NULL
)
;
mysql
>
INSERT
INTO
yssmx_test_tbl
(
yssmx_author
,
yssmx_count
)
values
(
'
Google
'
,
NULL
)
;
mysql
>
INSERT
INTO
yssmx_test_tbl
(
yssmx_author
,
yssmx_count
)
values
(
'
FK
'
,
20
)
;
mysql
>
SELECT
*
from
yssmx_test_tbl
;
+---------------+--------------+
|
yssmx_author
_
yssmx_count
|
+---------------+--------------+
|
yssmx
_
20
|
| 云搜索MX教程 _
NULL
|
|
Google
_
NULL
|
|
FK
_
20
|
+---------------+--------------+
4
rows
in
set
(
0.01
sec
)
以下实例中你可以看到 = 和 != 运算符是不起作用的:
mysql
>
SELECT
*
FROM
yssmx_test_tbl
WHERE
yssmx_count
=
NULL
;
Empty
set
(
0.00
sec
)
mysql
>
SELECT
*
FROM
yssmx_test_tbl
WHERE
yssmx_count
!=
NULL
;
Empty
set
(
0.01
sec
)
查找数据表中 yssmx_test_tbl 列是否为 NULL,必须使用
IS NULL
和
IS NOT NULL
,如下实例:
mysql
>
SELECT
*
FROM
yssmx_test_tbl
WHERE
yssmx_count
IS
NULL
;
+---------------+--------------+
|
yssmx_author
_
yssmx_count
|
+---------------+--------------+
| 云搜索MX教程 _
NULL
|
|
Google
_
NULL
|
+---------------+--------------+
2
rows
in
set
(
0.01
sec
)
mysql
>
SELECT
*
from
yssmx_test_tbl
WHERE
yssmx_count
IS
NOT
NULL
;
+---------------+--------------+
|
yssmx_author
_
yssmx_count
|
+---------------+--------------+
|
yssmx
_
20
|
|
FK
_
20
|
+---------------+--------------+
2
rows
in
set
(
0.01
sec
)
使用 PHP 脚本处理 NULL 值
PHP 脚本中你可以在 if...else 语句来处理变量是否为空,并生成相应的条件语句。
以下实例中 PHP 设置了 $yssmx_count 变量,然后使用该变量与数据表中的 yssmx_count 字段进行比较:
MySQL ORDER BY 测试:
<?php
$dbhost
=
'
localhost
'
;
$dbuser
=
'
root
'
;
$dbpass
=
'
123456
'
;
$conn
=
mysqli_connect
(
$dbhost
,
$dbuser
,
$dbpass
)
;
if
(
!
$conn
)
{
die
(
'
连接失败:
'
.
mysqli_error
(
$conn
)
)
;
}
mysqli_query
(
$conn
,
"
set names utf8
"
)
;
if
(
isset
(
$yssmx_count
)
)
{
$sql
=
"
SELECT yssmx_author, yssmx_count
FROM yssmx_test_tbl
WHERE yssmx_count =
$yssmx_count
"
;
}
else
{
$sql
=
"
SELECT yssmx_author, yssmx_count
FROM yssmx_test_tbl
WHERE yssmx_count IS NULL
"
;
}
mysqli_select_db
(
$conn
,
'
yssmx
'
)
;
$retval
=
mysqli_query
(
$conn
,
$sql
)
;
if
(
!
$retval
)
{
die
(
'
无法读取数据:
'
.
mysqli_error
(
$conn
)
)
;
}
echo
'
<h2>云搜索MX教程 IS NULL 测试<h2>
'
;
echo
'
<table border="1"><tr><td>作者</td><td>登陆次数</td></tr>
'
;
while
(
$row
=
mysqli_fetch_array
(
$retval
,
MYSQL_ASSOC
)
)
{
echo
"
<tr>
"
.
"
<td>
{$row['yssmx_author']}
</td>
"
.
"
<td>
{$row['yssmx_count']}
</td>
"
.
"
</tr>
"
;
}
echo
'
</table>
'
;
mysqli_close
(
$conn
)
;
?>
输出结果如下图所示: