NULL 详细和应用

在本教程中，您将学习如何使用MySQL NULL值。 此外，您将学习一些有用的函数来有效地处理NULL值。

如果不能理解和使用数据库中NULL值，那么可以认为您的数据库学习最多算刚入门水平。

MySQL NULL值简介

在MySQL中，NULL值表示一个未知值。 NULL值不同于0或空字符串''。

NULL值不等于它自身。如果将NULL值与另一个NULL值或任何其他值进行比较，则结果为NULL，因为一个不知道是什么的值(NULL值)与另一个不知道是什么的值(NULL值)比较，其值当然也是一个不知道是什么的值(NULL值)。

通常，使用NULL值来表示数据丢失，未知或不适用的情况。 例如，潜在客户的电话号码可能为NULL，并且可以稍后添加。

创建表时，可以通过使用NOT NULL约束来指定列是否接受NULL值。

例如，以下语句用于创建一张leads表：

USE testdb;
CREATE TABLE leads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    source VARCHAR(255) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(25)
);

因此，id是主键列，它不接受任何NULL值。

first_name，last_name和source列使用NOT NULL约束，因此，不能在这些列中插入任何NULL值，而email和phone列则可接受NULL值。

您可以在INSERT语句中使用NULL值来指定数据丢失。 例如，以下语句将一行插入到线索表中。 因为电话号码丢失，所以使用NULL值。

INSERT INTO leads(first_name,last_name,source,email,phone)
VALUE('John','Doe','Web Search','[e-mail]',NULL);

因为email列的默认值为NULL，可以按照以下方式在INSERT语句中省略电子邮件：

INSERT INTO leads(first_name,last_name,source,phone)
VALUES('Lily','Bush','Cold Calling','(408)-555-1234'),
('David','William','Web Search','(408)-888-6789');

UPDATE语句中的MySQL SET NULL值

要将列的值设置为NULL，可以使用赋值运算符(=)。 例如，要将David William的手机(phone)更新为NULL，请使用以下UPDATE语句：

UPDATE leads 
SET 
    phone = NULL
WHERE
    id = 3;

MySQL ORDER BY为NULL

如果使用ORDER BY子句按升序对结果集进行排序，则MySQL认为NULL值低于其他值，因此，它会首先显示NULL值。

以下查询语句按照电话号码(phone)升序排列。如下所示 -

SELECT 
    *
FROM
    leads
ORDER BY phone;

执行上面查询语句，结果如下 -

+----+------------+-----------+--------------+---------------------+----------------+
| id | first_name | last_name | source       | email               | phone          |
+----+------------+-----------+--------------+---------------------+----------------+
|  1 | John       | Doe       | Web Search   | [e-mail] | NULL           |
|  3 | David      | William   | Web Search   | NULL                | NULL           |
|  2 | Lily       | Bush      | Cold Calling | NULL                | (408)-555-1234 |
+----+------------+-----------+--------------+---------------------+----------------+

如果使用ORDER BY DESC，NULL值将显示在结果集的最后。 请参阅以下示例：

SELECT 
    *
FROM
    leads
ORDER BY phone DESC;

执行上面查询语句，结果如下 -

+----+------------+-----------+--------------+---------------------+----------------+
| id | first_name | last_name | source       | email               | phone          |
+----+------------+-----------+--------------+---------------------+----------------+
|  2 | Lily       | Bush      | Cold Calling | NULL                | (408)-555-1234 |
|  1 | John       | Doe       | Web Search   | [e-mail] | NULL           |
|  3 | David      | William   | Web Search   | NULL                | NULL           |
+----+------------+-----------+--------------+---------------------+----------------+
3 rows in set

要在查询中测试NULL，可以在WHERE子句中使用IS NULL或IS NOT NULL运算符。

例如，要获得尚未提供电话号码的潜在客户，请使用IS NULL运算符，如下所示：

SELECT 
    *
FROM
    leads
WHERE
    phone IS NULL;

执行上面查询语句，结果如下 -

+----+------------+-----------+------------+---------------------+-------+
| id | first_name | last_name | source     | email               | phone |
+----+------------+-----------+------------+---------------------+-------+
|  1 | John       | Doe       | Web Search | [e-mail] | NULL  |
|  3 | David      | William   | Web Search | NULL                | NULL  |
+----+------------+-----------+------------+---------------------+-------+
2 rows in set

可以使用IS NOT运算符来获取所有提供电子邮件地址的潜在客户。

SELECT 
    *
FROM
    leads
WHERE
    email IS NOT NULL;

执行上面查询语句，结果如下 -

+----+------------+-----------+------------+---------------------+-------+
| id | first_name | last_name | source     | email               | phone |
+----+------------+-----------+------------+---------------------+-------+
|  1 | John       | Doe       | Web Search | [e-mail] | NULL  |
+----+------------+-----------+------------+---------------------+-------+
1 row in set

即使NULL不等于NULL，GROUP BY子句中视两个NULL值相等。

SELECT 
    email, count(*)
FROM
    leads
GROUP BY email;

该查询只返回两行，因为其邮箱(email)列为NULL的行被分组为一行，结果如下所示 -

+---------------------+----------+
| email               | count(*) |
+---------------------+----------+
| NULL                |        2 |
| [e-mail] |        1 |
+---------------------+----------+
2 rows in set

MySQL NULL和UNIQUE索引

在列上使用唯一约束或UNIQUE索引时，可以在该列中插入多个NULL值。这是非常好的，因为在这种情况下，MySQL认为NULL值是不同的。

我们通过为phone列创建一个UNIQUE索引来验证这一点。

CREATE UNIQUE INDEX idx_phone ON leads(phone);

请注意，如果使用BDB存储引擎，MySQL认为NULL值相等，因此您不能将多个NULL值插入到具有唯一约束的列中。

MySQL NULL函数

MySQL提供了几个有用的功能，很好地处理空值：IFNULL，COALESCE和NULLIF。

IFNULL函数接受两个参数。 如果IFNULL函数不为NULL，则返回第一个参数，否则返回第二个参数。

例如，如果不是NULL，则以下语句返回电话号码(phone)，否则返回N/A，而不是NULL。

SELECT 
    id, first_name, last_name, IFNULL(phone, 'N/A') phone
FROM
    leads;

执行上面查询语句，得到以下结果 -

+----+------------+-----------+----------------+
| id | first_name | last_name | phone          |
+----+------------+-----------+----------------+
|  1 | John       | Doe       | N/A            |
|  2 | Lily       | Bush      | (408)-555-1234 |
|  3 | David      | William   | N/A            |
+----+------------+-----------+----------------+
3 rows in set

COALESCE函数接受参数列表，并返回第一个非NULL参数。 例如，可以使用COALESCE函数根据信息的优先级按照以下顺序显示线索的联系信息：phone, email和N/A。

SELECT 
    id,
    first_name,
    last_name,
    COALESCE(phone, email, 'N/A') contact
FROM
    leads;

执行上面查询语句，得到以下代码 -

+----+------------+-----------+---------------------+
| id | first_name | last_name | contact             |
+----+------------+-----------+---------------------+
|  1 | John       | Doe       | [e-mail] |
|  2 | Lily       | Bush      | (408)-555-1234      |
|  3 | David      | William   | N/A                 |
+----+------------+-----------+---------------------+
3 rows in set

NULLIF函数接受两个参数。如果两个参数相等，则NULLIF函数返回NULL。 否则，它返回第一个参数。

在列中同时具有NULL和空字符串值时，NULLIF函数很有用。 例如，错误地，您将以下行插入到leads表中：

INSERT INTO leads(first_name,last_name,source,email,phone)
VALUE('Thierry','Henry','Web Search','[e-mail]','');

phone是一个空字符串:''，而不是NULL。

如果您想获得潜在客户的联系信息，则最终得到空phone，而不是电子邮件，如下所示：

SELECT 
    id,
    first_name,
    last_name,
    COALESCE(phone, email, 'N/A') contact
FROM
    leads;

执行上面查询语句，得到以下代码 -

+----+------------+-----------+---------------------+
| id | first_name | last_name | contact             |
+----+------------+-----------+---------------------+
|  1 | John       | Doe       | [e-mail] |
|  2 | Lily       | Bush      | (408)-555-1234      |
|  3 | David      | William   | N/A                 |
|  4 | Thierry    | Henry     |                     |
+----+------------+-----------+---------------------+

要解决这个问题，您可以使用NULLIF函数将电话与空字符串('')进行比较，如果相等，则返回NULL，否则返回电话号码。

SELECT 
    id,
    first_name,
    last_name,
    COALESCE(NULLIF(phone, ''), email, 'N/A') contact
FROM
    leads;

执行上面查询语句，得到以下代码 -

+----+------------+-----------+--------------------------+
| id | first_name | last_name | contact                  |
+----+------------+-----------+--------------------------+
|  1 | John       | Doe       | [e-mail]      |
|  2 | Lily       | Bush      | (408)-555-1234           |
|  3 | David      | William   | N/A                      |
|  4 | Thierry    | Henry     | [e-mail] |
+----+------------+-----------+--------------------------+
4 rows in set

在本教程中，您已经学习了如何使用MySQL NULL值，以及如何使用一些方便的函数来处理查询中的NULL。