mysql - 查询条件:列中是否有 NULL 值

Question

我不明白两个查询之间的区别，请参阅图像。注意最后一个条件。Mysql版本是5.7有什么魔力？

select distinct(pc) as aggregate 
from `installers` 
where 
    `success` =1 
    and 
    date(created_at) >= '2018-08-15' 
    and 
    date(created_at) <= '2018-08-21' 
    and 
    (free=0 or free is null)

(free 为 null 或 free=0)

select distinct(pc) as aggregate 
from `installers` 
where 
    `success` =1 
    and 
    date(created_at) >= '2018-08-15' 
    and 
    date(created_at) <= '2018-08-21' 
    and 
    free!=1

免费!=1

表结构

    CREATE TABLE `installers` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `driver_id` BIGINT(20) UNSIGNED NOT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    `success` TINYINT(4) NULL DEFAULT NULL,
    `version` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
    `pc` VARCHAR(255) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `status` VARCHAR(180) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    `error` VARCHAR(180) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    `free` INT(11) NULL DEFAULT NULL,
    `time` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
    PRIMARY KEY (`id`),
    INDEX `installers_created_at_index` (`created_at`),
    INDEX `installers_updated_at_index` (`updated_at`),
    INDEX `installers_driver_id_foreign` (`driver_id`),
    INDEX `installers_success_index` (`success`),
    INDEX `installers_version_index` (`version`),
    INDEX `installers_pc_index` (`pc`(191)),
    INDEX `installers_status_index` (`status`),
    INDEX `installers_error_index` (`error`),
    INDEX `installers_free_index` (`free`),
    INDEX `installers_time_index` (`time`),
    CONSTRAINT `installers_driver_id_foreign` FOREIGN KEY (`driver_id`) REFERENCES `drivers` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=4709971
;

“free”的不同值为 NULL,0,1

Answer 1

NULL不是值，NULL(根据WIKI - NULL):

Null (or NULL) is a special marker used in Structured Query Language to indicate that a data value does not exist in the database.

This should not be confused with a value of 0. A null value indicates a lack of a value — a lack of a value is not the same thing as a value of zero in the same way that a lack of an answer is not the same thing as an answer of "no". For example, consider the question "How many books does Adam own?" The answer may be "zero" (we know that he owns none) or "null" (we do not know how many he owns). In a database table, the column reporting this answer would start out with no value (marked by Null), and it would not be updated with the value "zero" until we have ascertained that Adam owns no books.

SQL null is a state, not a value. This usage is quite different from most programming languages, where null value of a reference means it is not pointing to any object.

Since Null is not a member of any data domain, it is not considered a "value", but rather a marker (or placeholder) indicating the absence of value. Because of this, comparisons with Null can never result in either True or False, but always in a third logical result, Unknown.

即:

• 1 = 0 为 FALSE，但 1 = NULL 未知
• 1 != 0 为 TRUE，但 1 != NULL 也未知

WHERE 子句中的 UNKNOWN 状态相当于 FALSE。

这是直观的 - 因为 x 是 NULL(未知)，所以我们无法判断 x = 1 和 x != 1 是否为真 - 在这两种情况下，比较结果都是未知的。

由于上述原因，SQL 中有特殊的运算符来检查列是否为空 -x IS NULL 和 x IS NOT NULL。
< br/>您可以在这个简单的演示中看到此行为:http://www.sqlfiddle.com/#!9/9f78b0/5

SELECT * FROM t;
| id |      x |
|----|--------|
|  1 |      1 |
|  2 |      0 |
|  3 | (null) |

---

SELECT * FROM t WHERE x =1;
| id | x |
|----|---|
|  1 | 1 |

---

SELECT * FROM t WHERE x != 1;
| id | x |
|----|---|
|  2 | 0 |

请注意，上述查询仅返回 x = 2 的记录，但跳过 x = NULL 的记录，因为比较 x != NULL 评估为 UNKNOWN，相当于 FALSE。

---

SELECT * FROM t WHERE x IS NULL;

| id |      x |
|----|--------|
|  3 | (null) |

---

SELECT * FROM t WHERE x IS NOT NULL;
| id | x |
|----|---|
|  1 | 1 |
|  2 | 0 |

---

SELECT * FROM t WHERE x = 1 OR x IS NULL;
| id |      x |
|----|--------|
|  1 |      1 |
|  3 | (null) |

---

SELECT * FROM t WHERE x != 1 OR x IS NULL;
| id |      x |
|----|--------|
|  2 |      0 |
|  3 | (null) |