MySQL NULL的理解和应用

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

MySQL NULL值简介

在MySQL中，NULL值意味着未知值。 NULL值不是零或空字符''值。 NULL值不等于其自身值。 如果将NULL值与另一个NULL值或任何其他值进行比较，则结果为NULL，因为每个NULL值的值都是未知的。

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

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

例如，以下语句创建leads表：

CREATE TABLE studymysql.leads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(50) NOT NULL,
    source VARCHAR(255) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(25)
);

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

customer_name和source列使用NOT NULL约束，因此，不能在这些列中插入NULL值。

email和phone列是可以接受NULL值的。 您可以在INSERT语句中使用NULL值来指定缺少数据。例如，以下语句在leads表中插入一行。因为客户的电话号码丢失，因此这里使用了NULL值。

INSERT INTO leads(customer_name,source,email,phone)
VALUE('苏小鲁','Web Search','Xiaolu.su@studymysql.com',NULL);

由于电子邮件列(email)的默认值为NULL，因此可以在INSERT语句中省略电子邮件的值，如下所示：

INSERT INTO leads(customer_name,source,phone)
VALUES('王小丫','Cold Calling','13800138000'),
('王力宏','Web Search','13800138001');

接下来查询表中的数据，结果如下所示-

在UPDATE语句中使用MySQL SET NULL

要将列的值设置为NULL，请使用赋值运算符（=）。 例如，要将王小丫的电话更新为NULL，请使用以下UPDATE语句：

UPDATE leads 
SET 
    phone = NULL
WHERE
    id = 2;

MySQL ORDER BY NULL

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

以下语句按电话号码(phone)升序排列 leads 表中的数据。

SELECT 
    *
FROM
    leads
ORDER BY phone;

执行上面的查询语句后，结果如下所示 -

在使用ORDER BY DESC的情况下，NULL值出现在结果集的最后。 请参见以下示例：

SELECT 
    *
FROM
    leads
ORDER BY phone DESC;

执行上面的查询语句后，结果如下所示 -

要在查询中找出NULL值，请在WHERE子句中使用IS NULL或IS NOT NULL运算符。

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

SELECT 
    *
FROM
    leads
WHERE
    phone IS NULL;

执行上面的查询语句后，结果如下所示 -

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

SELECT 
    *
FROM
    leads
WHERE
    email IS NOT NULL;

执行上面的查询语句后，结果如下所示 -

即使NULL不等于NULL，但是在GROUP BY子句中的两个NULL值也是相等的。

SELECT 
    id, customer_name, email, phone
FROM
    leads
GROUP BY email;

执行上面的查询语句后，结果如下所示 -

查询只返回两行，因为电子邮件(email)列为NULL的行被分组为一行。

MySQL NULL和UNIQUE索引

在列上使用唯一约束或UNIQUE索引时，可以向该列插入多个NULL值。 这是完全正常的，因为在这种情况下MySQL认为NULL值是不同的。 让我们通过在phone列上添加一个UNIQUE索引来验证这一点。

CREATE UNIQUE INDEX idx_phone ON leads(phone);

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

MySQL NULL函数

MySQL提供了几个有用的函数来处理NULL值：ISNULL，COALESCE和NULLIF。 ISNULL函数接受两个参数。 如果它不为NULL，IFNULL函数返回第一个参数，否则返回第二个参数。

例如，以下语句显示电话号码，如果暂时不清楚则显示N/A，而不是NULL。

SELECT 
    id, customer_name, IFNULL(phone, 'N/A') phone
FROM
    studymysql.leads;

执行上面的查询语句后，结果如下所示 -

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

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

执行上面的查询语句后，结果如下所示 -

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

当列中的值为NULL和空字符串时，NULLIF函数非常有用。 例如，错误地在leads表中插入以下行：

INSERT INTO leads(customer_name,source,email,phone)
VALUE('苏小撸','Web Search','xlu.su@studymysql.com','');

电话(phone)是空字符串，而不是NULL值。 如果您想获取潜在客户的联系信息，则最终得到一个空的电话，而不是电子邮件，如以下查询：

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

执行上面的查询语句后，结果如下所示 -

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

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

执行上面的查询语句后，结果如下所示 -

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

转载于:https://my.oschina.net/u/2263383/blog/761909