MySQL中NULL的惊喜

发表于： 2022-09-13 23:32:33
分类：数据库文章分享
浏览：浏览(97)

“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来表示缺少值，但这在我们的情况下不起作用。负金额的货币是有效价值，这意味着一个人有债务。

数据存储级别为空

空值经常因占用数据库空间而受到批评。空值确实占用了空间，但并不像看上去那么糟糕。当然，它们应该占据一些空间，因为关于信息缺失的信息本身就是信息。大多数关系数据库为每个可以为空的字段添加一位。但事实上，这并不是那么简单。不同的存储引擎可以以不同的方式解决此问题。例如，如果表定义包含任何允许空的列，NDB存储引擎将为每行保留4个字节，最多32个空列。因此，如果该表有1到32个可空列，则无论发生什么情况，每行都将占用4个字节。考虑到所有这些，甚至可以通过使用空值来节省一些存储空间。

索引中为空

允许在唯一索引中有多个空值。让我们看一下下面的例子。

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进行一些比较。

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)

尽管我们有几个用户的电子邮件为“空”，但他们没有被选中。通常，这不是我们想要的。但MySQL就是这样工作的。

`选择null<>John@example.com‘‘将返回‘null‘。

为了使它按我们所希望的方式工作，我们可以使用“IS NULL”运算符或“NULL-safe-equal”运算符。

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和算术运算

带空值的数学不有趣。无论你做什么，结果都是空的。

加法和减法。

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 |
+-----------+-----------+-----------------+-----------------+

Group By

让我们看一下下面的例子。

SELECT email FROM users GROUP BY email;
SELECT DISTINCT email FROM users;

两个查询返回相同的结果。

+------------------+
| email            |
+------------------+
| NULL             |
| John@example.com |
+------------------+

即使NULL等于no-nothing，这里它也等于它本身。我认为这是规则中的一个例外，允许我们按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 |
+--------------+--------------+---------------+---------------+

AND

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 |
+---------------+---------------+----------------+----------------+

NOT

SELECT
    NOT NULL;

+----------+
| NOT NULL |
+----------+
|     NULL |
+----------+

IS UNKNOWN

SELECT NULL IS UNKNOWN, NULL IS NOT UNKNOWN;

+-----------------+---------------------+
| NULL IS UNKNOWN | NULL IS NOT UNKNOWN |
+-----------------+---------------------+
|               1 |                   0 |
+-----------------+---------------------+

IS TRUE, IS FALSE

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将简单地忽略空值。

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                             |
+-----------------------+----------------------------------+

空函数和聚合函数

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 |
+----------------------+

但根据您的需要，您可以使用“合并”将空值替换为其他值。

SELECT
    AVG(COALESCE(amount_of_money, 0))
FROM
    users;

+-----------------------------------+
| AVG(COALESCE(amount_of_money, 0)) |
+-----------------------------------+
|                            1.6667 |
+-----------------------------------+

结论

关系数据库中关系主题中的空值。如果你认为你对这件事了解很多，小心点。那个兔子洞很深。我希望本文将帮助您更好地理解空值。谢谢你的阅读。

原文标题：Surprises From NULL in MySQL
原文作者：Aleksei Kankov
原文链接：https://dzone.com/articles/surprises-from-null-in-mysql

赞(0)
踩(0)

免责声明：

1、本站资源由自动抓取工具收集整理于网络。

2、本站不承担由于内容的合法性及真实性所引起的一切争议和法律责任。

3、电子书、小说等仅供网友预览使用，书籍版权归作者或出版社所有。

4、如作者、出版社认为资源涉及侵权，请联系本站，本站将在收到通知书后尽快删除您认为侵权的作品。

5、如果您喜欢本资源，请您支持作者，购买正版内容。

6、资源失效，请下方留言，欢迎分享资源链接

文章评论

共0条评论