返回 登录
0

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_namesource列使用NOT NULL约束,因此,不能在这些列中插入NULL值。

emailphone列是可以接受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 NULLIS 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值:ISNULLCOALESCENULLIF
ISNULL函数接受两个参数。 如果它不为NULLIFNULL函数返回第一个参数,否则返回第二个参数。

例如,以下语句显示电话号码,如果暂时不清楚则显示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;

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

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

评论