MySQL 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','john.doe@yiibai.com',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   | john.doe@yiibai.com | 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   | john.doe@yiibai.com | 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 | john.doe@yiibai.com | 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 | john.doe@yiibai.com | NULL  |
+----+------------+-----------+------------+---------------------+-------+
1 row in set

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

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

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

+---------------------+----------+
| email               | count(*) |
+---------------------+----------+
| NULL                |        2 |
| john.doe@yiibai.com |        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       | john.doe@yiibai.com |
|  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','thierry.henry@yiibai.com','');

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       | john.doe@yiibai.com |
|  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       | john.doe@yiibai.com      |
|  2 | Lily       | Bush      | (408)-555-1234           |
|  3 | David      | William   | N/A                      |
|  4 | Thierry    | Henry     | thierry.henry@yiibai.com |
+----+------------+-----------+--------------------------+
4 rows in set

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

上一篇： MySQL技巧下一篇： MySQL存储过程

易百教程

MySQL NULL详细和应用

MySQL NULL值简介

UPDATE语句中的MySQL SET NULL值

MySQL ORDER BY为NULL

MySQL NULL和UNIQUE索引

MySQL NULL函数

随机教程

MySQL NULL值简介

UPDATE语句中的MySQL SET NULL值

MySQL ORDER BY为NULL

MySQL NULL和UNIQUE索引

MySQL NULL函数

随机教程

微信关注