mysql有关null（IFNULL，COALESCE和NULLIF）的知识各位看官要不要了解下

在MySQL中，

NULL

值表示一个未知值，它不同于

或空字符串

''

，并且不等于它自身。

我们如果将

NULL

值与另一个

NULL

值或任何其他值进行比较，则结果为

NULL

，因为一个不知道是什么的值(

NULL

值)与另一个不知道是什么的值(

NULL

值)比较，其值当然也是一个不知道是什么的值(

NULL

值)。

然而我们通常，使用

NULL

值来表示数据丢失，未知或不适用的情况。例如，潜在客户的电话号码可能为

NULL

，并且可以稍后添加。所以我们创建表时，可以通过使用

NOT NULL

约束来指定列是否接受

NULL

值。接下来，我们来创建一张

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)
);

我们可以看出来，

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','[email protected]',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');

完事如果我们要将列的值设置为

NULL

，可以使用赋值运算符(

)。例如，要将

David William

的手机(

phone

)更新为

NULL

，请使用以下

UPDATE

语句：

UPDATE leads 
SET 
    phone = NULL
WHERE
    id = 3;

但是如果使用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   | [email protected] | 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   | [email protected] | 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 | [email protected] | 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 | [email protected] | NULL  |
+----+------------+-----------+------------+---------------------+-------+
1 row in set

然而，即使

NULL

不等于

NULL

，

GROUP BY

子句中视两个

NULL

值相等，来看下sql实例：

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

该查询只返回两行，因为其邮箱(

email

)列为

NULL

的行被分组为一行，结果如下所示：

+---------------------+----------+
| email               | count(*) |
+---------------------+----------+
| NULL                |        2 |
| [email protected] |        1 |
+---------------------+----------+
2 rows in set

我们要知道在列上使用唯一约束或

UNIQUE

索引时，可以在该列中插入多个

NULL

值，在这种情况下，MySQL认为

NULL

值是不同的。接下来我们通过为

phone

列创建一个

UNIQUE

索引来验证这一点：

CREATE UNIQUE INDEX idx_phone ON leads(phone);

这里我们要注意，如果使用BDB存储引擎的话，mysql会认为

NULL

值相等，因此我们不能将多个

NULL

值插入到具有唯一约束的列中。

既然知道了null的好处和坏处，我们就来看下在mysql中应该如何处理它吧。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       | [email protected] |
|  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','[email protected]','');

因为

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       | [email protected] |
|  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       | [email protected]      |
|  2 | Lily       | Bush      | (408)-555-1234           |
|  3 | David      | William   | N/A                      |
|  4 | Thierry    | Henry     | [email protected] |
+----+------------+-----------+--------------------------+
4 rows in set

好啦，本次记录就到这里了。如果感觉不错的话，请多多点赞支持哦。。。

mysql有关null（IFNULL，COALESCE和NULLIF）的知识各位看官要不要了解下

继续阅读

转换字符串成ＩＮＴ６４

c写文件

mysql优化（sql优化）

数据迁移方法数据迁移原则数据迁移之双写方案数据迁移之级联同步方案

redis集群数据一致性_RedisRaft为Redis集群带来强大的数据一致性

宝塔面板mysql恢复2018.1.8更新

Centos7 MySQL 5.7 安装MySQL 5.7 安装

查找入职员工时间排名倒数第三的员工所有信息

Hibernate使用Hibernate的“3个准备，7个步骤”Hibernate API简介操作实体对象对象识别

云计算面试题——mysql/存储引擎/备份

SQL语言基础：常用的数据查询语句

Android – ListView 中添加按钮，动态删除添加ItemView的操作

Ubuntu16.04安装Apache+MySQL+PHP1. 安装Apache2. 安装MySQL3. 安装PHP4. 安装phpMyAdmin

MySQL的4种隔离级别？出现问题

neo4j之cypher使用文档

mysql使用source命令导入.sql文件