在本教程中,您将学习如何使用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;
执行上面的查询语句后,结果如下所示 -
在本教程中,您已经学习了如何使用MySQLNULL
值以及如何使用一些方便的函数来处理查询中的NULL
值。