MySQL中的NULL带来的惊喜
`NULL'是关系型数据库中的一个东西，它可以带来很多惊喜，永远不会让你失望。
`NULL'值可能会让人感到惊讶，直到你习惯它。而在你习惯了之后。而且它将永远令人惊讶。`NULL'是关系型数据库中的一个东西，它可以带来很多惊喜，永远不会让你失望。他们中的大多数人都不讨人喜欢，但只把NULL想成是没有价值的，就很容易理解他们。
NULL的历史
NULL被引入关系数据库，作为描述缺失或不适用的信息的一种方式。这是一种说 "我不知道 "或 "我不关心 "或 "我没有这些信息 "的方式。而这是一个非常好的方法。让我们看一下一个简单的例子。
纯文本
| id | name | amount_of_money |
| --- | ---- | --------------- |
| 1 | John | 1000.00 |
| 2 | Jane | NULL |
| 3 | Jack | 2000.00 |
| 4 | Jill | -1.00 |
在这个表格中，我们有一个人的名单和他们的钱数。我们可以看到，Jane在金额一栏有`NULL`。这并不意味着简没有钱。我们根本不知道她有多少钱。有时开发者会用-1来表示没有值，但这对我们的情况不起作用。负数是一个有效值，它意味着一个人有债务。
数据存储层面上的NULL
NULL经常被批评为占用了数据库的空间。NULL确实会占用空间，但它并不像看起来那么糟糕。当然，他们也应该占用一些空间，因为关于没有信息的信息本身就是信息。大多数关系型数据库为每个可以为空的字段添加一个位。但是，实际上，这并不是那么简单。不同的存储引擎可以用不同的方式解决这个问题。例如，如果表定义中包含任何允许NULL的列，NDB存储引擎为每行保留4个字节，最多保留32个NULL列。因此，如果表有1到32个空列，无论如何，每行都需要4个字节。考虑到所有这些，甚至可以通过使用NULLs来节省一些存储空间。
索引中的NULL
在唯一索引中允许有多个NULL。让我们看一下下面的例子。
ǞǞǞ
CREATE TABLE users (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) DEFAULT NULL,
amount_of_money INT(11) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY email (email)
)
ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4
COLLATE = utf8mb4_unicode_ci;
INSERT
INTO
users (name, email, amount_of_money)
VALUES
('John', 'John@example.com', 10);
INSERT
INTO
users (name)
VALUES
('Jane');
INSERT
INTO
users (name, amount_of_money)
VALUES
('Ben', -5);
SELECT *
FROM
users;
纯文本
+----+------+------------------+-----------------+
| id | name | email | amount_of_money |
+----+------+------------------+-----------------+
| 1 | John | John@example.com | 10 |
| 2 | Jane | NULL | NULL |
| 3 | Ben | NULL | -5 |
+----+------+------------------+-----------------+
3 rows in set (0.00 sec)
正如你所看到的，我们有两个拥有相同电子邮件的用户。它是允许的，因为NULL不等于NULL。这是一个特殊的情况。NULL不等于任何东西，包括NULL。所以，我们可以在唯一索引中出现多个NULL。但是，当然，我们不能在主键中出现多个NULL。这是不允许的，因为主键是用来识别一行的。而且，如果我们有两行具有相同的主键，我们将无法识别它们。所以，我们不能在主键中出现多个NULL。但是我们可以在唯一索引中出现多个NULL。这是被允许的，因为唯一索引不被用来识别行。它用于通过一个特定的值来寻找一行。而且，如果我们有两行在唯一索引中具有相同的值，我们仍然可以通过主键找到它们。
与NULL比较
让我们与NULL做一些比较。
ǞǞǞ
SELECT NULL = NULL, NULL != NULL, NULL > NULL, NULL < NULL;
纯文本
+-------------+--------------+-------------+-------------+
| NULL = NULL | NULL != NULL | NULL > NULL | NULL < NULL |
+-------------+--------------+-------------+-------------+
| NULL | NULL | NULL | NULL |
+-------------+--------------+-------------+-------------+
ǞǞǞ
SELECT NULL IS NULL, NULL IS NOT NULL;
纯文本
+--------------+------------------+
| NULL IS NULL | NULL IS NOT NULL |
+--------------+------------------+
| 1 | 0 |
+--------------+------------------+
NULL不等于任何东西，甚至不等于NULL。这很容易理解，但它会带来一些惊喜。例如，让我们从我们的用户表中选择电子邮件不等于的用户。
ǞǞǞ
SELECT *
FROM
users
WHERE
email <> 'John@example.com';
而结果是
纯文本
Empty set (0.00 sec)
即使我们有几个用户的电子邮件为`NULL'，他们也没有被选中。通常情况下，这不是我们想要的结果。但这就是MySQL 的工作方式。
`select null <> 'John@example.com'`将返回`null`。
为了使其按照我们的要求工作，我们可以使用`IS NULL`操作符或`<=>`NULL-安全的等价操作符。
ǞǞǞ
SELECT *
FROM
users
WHERE
email <> 'John@example.com'
OR email IS NULL;
SELECT *
FROM
users
WHERE
NOT email <=> 'John@example.com';
纯文本
+----+------+-------+
| id | name | email |
+----+------+-------+
| 2 | Jane | NULL |
| 3 | Ben | NULL |
+----+------+-------+
NULL和算术运算
有NULL的数学并不有趣。无论你做什么，结果都会是NULL。
加法和减法。
ǞǞǞ
SELECT
NULL + 10,
10 + NULL,
NULL - 10,
10 - NULL;
纯文本
+-----------+-----------+-----------+-----------+
| NULL + 10 | 10 + NULL | NULL - 10 | 10 - NULL |
+-----------+-----------+-----------+-----------+
| NULL | NULL | NULL | NULL |
+-----------+-----------+-----------+-----------+
除法和乘法。
ǞǞǞ
SELECT
10 * NULL,
NULL * 10,
NULL / 10,
10 / NULL,
0 / NULL,
NULL / 0;
纯文本
+-----------+-----------+-----------+-----------+----------+----------+
| 10 * NULL | NULL * 10 | NULL / 10 | 10 / NULL | 0 / NULL | NULL / 0 |
+-----------+-----------+-----------+-----------+----------+----------+
| NULL | NULL | NULL | NULL | NULL | NULL |
+-----------+-----------+-----------+-----------+----------+----------+
模块和电源。
ǞǞǞ
SELECT
NULL % 10,
10 % NULL,
POWER(10, NULL),
POWER(NULL, 10);
纯文本
+-----------+-----------+-----------------+-----------------+
| NULL % 10 | 10 % NULL | POWER(10, NULL) | POWER(NULL, 10) |
+-----------+-----------+-----------------+-----------------+
| NULL | NULL | NULL | NULL |
+-----------+-----------+-----------------+-----------------+
集团通过
让我们看一下下面的例子。
ǞǞǞ
SELECT email FROM users GROUP BY email;
SELECT DISTINCT email FROM users;
这两个查询的结果是一样的。
纯文本
+------------------+
| email |
+------------------+
| NULL |
| John@example.com |
+------------------+
尽管NULL等于无，但在这里它却等于它自己。我想说这是规则的一个例外，允许我们按NULL分组。
NULL和布尔运算符
对于布尔运算符，NULL的行为就像没有值一样。所以在每一个有不确定性的情况下，结果都会是空的。
或
ǞǞǞ
SELECT
NULL OR TRUE,
TRUE OR NULL,
NULL OR FALSE,
FALSE OR NULL;
纯文本
+--------------+--------------+---------------+---------------+
| NULL OR TRUE | TRUE OR NULL | NULL OR FALSE | FALSE OR NULL |
+--------------+--------------+---------------+---------------+
| 1 | 1 | NULL | NULL |
+--------------+--------------+---------------+---------------+
和
ǞǞǞ
SELECT
NULL AND TRUE,
TRUE AND NULL,
NULL AND FALSE,
FALSE AND NULL;
纯文本
+---------------+---------------+----------------+----------------+
| NULL AND TRUE | TRUE AND NULL | NULL AND FALSE | FALSE AND NULL |
+---------------+---------------+----------------+----------------+
| NULL | NULL | 0 | 0 |
+---------------+---------------+----------------+----------------+
不
ǞǞǞ
SELECT
NOT NULL;
纯文本
+----------+
| NOT NULL |
+----------+
| NULL |
+----------+
未知数
ǞǞǞ
SELECT NULL IS UNKNOWN, NULL IS NOT UNKNOWN;
纯文本
+-----------------+---------------------+
| NULL IS UNKNOWN | NULL IS NOT UNKNOWN |
+-----------------+---------------------+
| 1 | 0 |
+-----------------+---------------------+
是真的，是假的
ǞǞǞ
SELECT
NULL IS TRUE,
NULL IS NOT TRUE,
NULL IS FALSE,
NULL IS NOT FALSE;
ǞǞǞ
+--------------+------------------+---------------+-------------------+
| NULL IS TRUE | NULL IS NOT TRUE | NULL IS FALSE | NULL IS NOT FALSE |
+--------------+------------------+---------------+-------------------+
| 0 | 1 | 0 | 1 |
+--------------+------------------+---------------+-------------------+
NULL和字符串函数
让我们来看看一些字符串函数。
串联和组串联在空值的情况下会有不同的表现。
ǞǞǞ
SELECT CONCAT('hello', NULL, 'world');
纯文本
+--------------------------------+
| concat('hello', null, 'world') |
+--------------------------------+
| NULL |
+--------------------------------+
ǞǞǞ
SELECT
GROUP_CONCAT(email)
FROM
users;
纯文本
+---------------------+
| GROUP_CONCAT(email) |
+---------------------+
| John@example.com |
+---------------------+
`CONCAT_WS`将直接忽略NULL值。
ǞǞǞ
SELECT CONCAT_WS(',', 'Hello', NULL, ' world');
纯文本
+-----------------------------------------+
| CONCAT_WS(',', 'Hello', NULL, ' world') |
+-----------------------------------------+
| Hello, world |
+-----------------------------------------+
ǞǞǞ
SELECT CONCAT_WS(NULL, 'Hello', NULL, ' world');
纯文本
+------------------------------------------+
| CONCAT_WS(NULL, 'Hello', NULL, ' world') |
+------------------------------------------+
| NULL |
+------------------------------------------+
`ELT`将返回空元素的位置。
ǞǞǞ
SELECT ELT(3, null, 'Bb', null, 'Dd');
纯文本
+--------------------------------+
| ELT(3, null, 'Bb', null, 'Dd') |
+--------------------------------+
| NULL |
+--------------------------------+
`FIELD`不会找到空元素。
ǞǞǞ
SELECT FIELD(null, 'Aa', 'Bb', 'Cc', 'Dd', 'Ff', null);
纯文本
+-------------------------------------------------+
| FIELD(null, 'Aa', 'Bb', 'Cc', 'Dd', 'Ff', null) |
+-------------------------------------------------+
| 0 |
+-------------------------------------------------+
`INSERT`对null不起作用。
ǞǞǞ
SELECT INSERT('Quadratic', 3, 4, null);
纯文本
+---------------------------------+
| INSERT('Quadratic', 3, 4, null) |
+---------------------------------+
| NULL |
+---------------------------------+
`REPEAT`对null不起作用。
ǞǞǞ
SELECT REPEAT('MySQL', NULL), REPEAT(NULL, 3);
纯文本
+-----------------------+----------------------------------+
| REPEAT('MySQL', NULL) | REPEAT(NULL, 3) |
+-----------------------+----------------------------------+
| NULL | NULL |
+-----------------------+----------------------------------+
NULL和聚合函数
`COUNT`将忽略空值。
ǞǞǞ
SELECT
COUNT(email),
COUNT(*)
FROM
users;
纯文本
+--------------+----------+
| count(email) | count(*) |
+--------------+----------+
| 1 | 3 |
+--------------+----------+
`COUNT(DISTINCT)`将以同样的方式表现，即使`SELECT DISTINCT`将返回空值。
ǞǞǞ
SELECT
COUNT(DISTINCT email)
FROM
users;
纯文本
+-----------------------+
| COUNT(DISTINCT email) |
+-----------------------+
| 1 |
+-----------------------+
与 "AVG "的情况相同。它将忽略空值。
ǞǞǞ
SELECT
AVG(amount_of_money)
FROM
users;
纯文本
+----------------------+
| AVG(amount_of_money) |
+----------------------+
| 2.5000 |
+----------------------+
但是根据你的需要，你可以使用`COALESCE`来替换空值。
ǞǞǞ
SELECT
AVG(COALESCE(amount_of_money, 0))
FROM
users;
纯文本
+-----------------------------------+
| AVG(COALESCE(amount_of_money, 0)) |
+-----------------------------------+
| 1.6667 |
+-----------------------------------+
总结
关系型数据库中的关系型主题中的NULL。如果你认为你对它了解很多，那就要小心了。这个兔子洞很深。我希望这篇文章能帮助你更好地理解NULL。谢谢你的阅读。
