摘要:在本教程中,您将学习如何使用 MySQL NULL
值。此外,您还将学习一些有用的函数来有效地处理NULL
值。
MySQL NULL
值简介
在 MySQL 中, NULL
值意味着未知。 NULL
值不同于零 ( 0
) 或空字符串''
。
NULL
值不等于任何东西,甚至不等于它本身。如果将NULL
值与另一个NULL
值或任何其他值进行比较,结果将为NULL
,因为每个NULL
值的值都是未知的。
通常,您使用NULL
值来指示数据丢失、未知或不适用。例如,潜在客户的电话号码可能为NULL
,可以稍后添加。
创建表时,可以使用NOT NULL
约束指定列是否接受NULL
值。
例如,以下语句创建leads
表:
DROP TABLE IF EXISTS leads;
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)
);
Code language: SQL (Structured Query Language) (sql)
在这个leads
表中, id
列是主键列,因此,它不接受任何NULL
值。
first_name
、 last_name
和source
列使用NOT NULL
约束,因此,您不能将任何NULL
值插入到这些列中,而email
和phone
列接受 NULL 值。
您可以在INSERT
语句中使用NULL
值来指定数据丢失。例如,以下语句将一行插入到leads
表中。由于缺少电话号码,因此使用NULL
值。
INSERT INTO leads(first_name,last_name,source,email,phone)
VALUE('John','Doe','Web Search','john.doe@acme.com',NULL);
Code language: SQL (Structured Query Language) (sql)
由于 email 列的默认值为NULL
,因此您可以在INSERT
语句中省略 email ,如下所示:
INSERT INTO leads(first_name,last_name,source,phone)
VALUES
('Lily','Bush','Cold Calling','(408)-555-1234'),
('David','William','Web Search','(408)-888-6789');
Code language: SQL (Structured Query Language) (sql)
MySQL 在UPDATE
语句中SET NULL
要将列的值设置为NULL
,请使用赋值运算符 ( =
)。例如,要将David William
的电话更新为NULL
,您可以使用以下UPDATE
语句:
UPDATE leads
SET
phone = NULL
WHERE
id = 3;
Code language: SQL (Structured Query Language) (sql)
MySQL ORDER BY
和NULL
如果使用ORDER BY
子句对结果集进行升序排序,MySQL认为NULL
值低于其他值,因此,它首先显示NULL
值。
以下语句按电话号码升序对潜在客户进行排序。
SELECT
*
FROM
leads
ORDER BY phone;
Code language: SQL (Structured Query Language) (sql)
如果您使用ORDER BY DESC
,则NULL
值将出现在结果集的最后。请参见以下示例:
SELECT
*
FROM
leads
ORDER BY phone DESC;
Code language: SQL (Structured Query Language) (sql)
要测试查询中的NULL
,可以在WHERE
子句中使用IS NULL
或IS NOT NULL
运算符。
例如,要获取尚未提供电话号码的潜在客户,请使用IS NULL
运算符,如下所示:
SELECT
*
FROM
leads
WHERE
phone IS NULL;
Code language: SQL (Structured Query Language) (sql)
您可以使用IS NOT
运算符来获取提供了电子邮件地址的所有潜在客户。
SELECT
*
FROM
leads
WHERE
email IS NOT NULL;
Code language: SQL (Structured Query Language) (sql)
尽管NULL
不等于NULL
,但GROUP BY
子句中的两个NULL
值是相等的。
SELECT
id, first_name, last_name, email, phone
FROM
leads
GROUP BY email;
Code language: SQL (Structured Query Language) (sql)
该查询仅返回两行,因为电子邮件列为NULL
的行被分为一组。
MySQL NULL
和UNIQUE
索引
当您在列上使用UNIQUE 约束或UNIQUE 索引时,可以将多个NULL
值插入到该列中。这完全没问题,因为在这种情况下,MySQL 认为NULL
值是不同的。
让我们通过为phone
列创建一个UNIQUE
索引来验证这一点。
CREATE UNIQUE INDEX idx_phone ON leads(phone);
Code language: SQL (Structured Query Language) (sql)
请注意,如果您使用 BDB存储引擎,MySQL 会认为NULL
值相等,因此您不能将多个NULL
值插入到具有唯一约束的列中。
MySQL NULL
函数
MySQL 提供了几个有效处理 NULL 的有用函数: IFNULL
、 COALESCE
和NULLIF
。
IFNULL
函数接受两个参数。如果IFNULL
函数不为NULL
,则返回第一个参数,否则返回第二个参数。
例如,以下语句如果电话号码不为NULL
则返回电话号码,否则返回N/A
而不是NULL
。
SELECT
id,
first_name,
last_name,
IFNULL(phone, 'N/A') phone
FROM
leads;
Code language: SQL (Structured Query Language) (sql)
COALESCE
函数接受参数列表并返回第一个非 NULL 参数。例如,您可以使用COALESCE
功能根据信息的优先级按以下顺序显示潜在客户的联系信息:电话、电子邮件和 N/A。
SELECT
id,
first_name,
last_name,
COALESCE(phone, email, 'N/A') contact
FROM
leads;
Code language: SQL (Structured Query Language) (sql)
NULLIF
函数接受两个参数。如果两个参数相等,则NULLIF
函数返回NULL
。否则,它返回第一个参数。
当列中同时具有NULL
和空字符串值时, NULLIF
函数非常有用。例如,您错误地将以下行插入到leads
表中:
INSERT INTO leads(first_name,last_name,source,email,phone)
VALUE('Thierry','Henry','Web Search','thierry.henry@example.com','');
Code language: SQL (Structured Query Language) (sql)
电话是一个空字符串而不是NULL
。
如果您想获取潜在客户的联系信息,您最终会得到一个空电话而不是电子邮件,如以下查询所示:
SELECT
id,
first_name,
last_name,
COALESCE(phone, email, 'N/A') contact
FROM
leads;
Code language: SQL (Structured Query Language) (sql)
要解决此问题,您可以使用NULLIF
函数将电话与空字符串进行比较,如果它们相等,则返回NULL
,否则返回电话号码。
SELECT
id,
first_name,
last_name,
COALESCE(NULLIF(phone, ''), email, 'N/A') contact
FROM
leads;
Code language: SQL (Structured Query Language) (sql)
在本教程中,您学习了如何使用 MySQL NULL
以及如何使用一些方便的函数来处理查询中的NULL
。