MySQL 中 !col 和 col=false 有什么区别？

发布于 2024-08-26 18:02:28 字数 1414 浏览 4 评论 0原文

两种语句的性能完全不同：

mysql> explain select * from jobs  where createIndexed=false;
+----+-------------+-------+------+----------------------+----------------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys        | key                  | key_len | ref   | rows | Extra |
+----+-------------+-------+------+----------------------+----------------------+---------+-------+------+-------+
|  1 | SIMPLE      | jobs  | ref  | i_jobs_createIndexed | i_jobs_createIndexed | 1       | const |    1 |       | 
+----+-------------+-------+------+----------------------+----------------------+---------+-------+------+-------+
1 row in set (0.01 sec)

mysql> explain select * from jobs  where !createIndexed;
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | jobs  | ALL  | NULL          | NULL | NULL    | NULL | 17996 | Using where | 
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

辅助分析的列定义及相关索引：

createIndexed tinyint(1) NOT NULL DEFAULT 0,
create index i_jobs_createIndexed on jobs(createIndexed);

原文

The two statements have totally different performance:

mysql> explain select * from jobs  where createIndexed=false;
+----+-------------+-------+------+----------------------+----------------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys        | key                  | key_len | ref   | rows | Extra |
+----+-------------+-------+------+----------------------+----------------------+---------+-------+------+-------+
|  1 | SIMPLE      | jobs  | ref  | i_jobs_createIndexed | i_jobs_createIndexed | 1       | const |    1 |       | 
+----+-------------+-------+------+----------------------+----------------------+---------+-------+------+-------+
1 row in set (0.01 sec)

mysql> explain select * from jobs  where !createIndexed;
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | jobs  | ALL  | NULL          | NULL | NULL    | NULL | 17996 | Using where | 
+----+-------------+-------+------+---------------+------+---------+------+-------+-------------+

Column definition and related index for aiding analysis:

createIndexed tinyint(1) NOT NULL DEFAULT 0,
create index i_jobs_createIndexed on jobs(createIndexed);

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

清晰传感 2024-09-02 18:02:28

从逻辑上讲，这些操作是相同的，但是MySQL的优化器并没有那么聪明，无法在NOT createIndexed中看到createIndexed = 0。

MySQL 中的 FALSE 只是 0 的同义词，TRUE 是 1 的同义词>。

此条件为 false:

SELECT  2 = TRUE

--
0

，因此第一个查询只是与 MySQL 识别的 0 的纯索引 ref 比较，而第二个查询包含 MySQL 无法表示为可控制表达式的更复杂的逻辑。

Logically, these operations are the same, but MySQL's optimizer is just not so smart to see createIndexed = 0 in NOT createIndexed.

FALSE in MySQL is just a synonym for 0 and TRUE is a synonym for 1.

This condition is false:

SELECT  2 = TRUE

--
0

, so the first query is just a pure index ref comparison to 0 which MySQL is aware of, while the second one contains more complex logic that MySQL cannot represent as a sargable expression.

回复收藏 0 原文

帅的被狗咬 2024-09-02 18:02:28

MySQL 无法将索引用于 WHERE !createIndexed，因为它需要通过表扫描来评估每一行的 NOT createIndexed。

回复收藏 0 原文

一指流沙 2024-09-02 18:02:28

我认为区别在于对空值的处理 - （无论您的情况中的 NOT NULL 语句如何）。也许这些手册部分可以提供帮助？

http://dev.mysql.com/doc/refman/ 5.1/en/comparison-operators.html
http://dev.mysql.com/doc/ refman/5.1/en/tical-operators.html#operator_not

回复收藏 0 原文

心意如水 2024-09-02 18:02:28

在 MySQL 中，FALSE 关键字不是布尔数据：它是等于零的整数常量。相反，！ (aka NOT) 是一个逻辑运算符：

如果操作数为 0，则计算结果为 1，
如果操作数非零则为 0，并且 NOT
NULL 返回 NULL。

我认为没有太大的实际差异：

mysql> select 1=0, 0=0, 33=0, null=0, not 1, not 0, not 33, not null;
+-----+-----+------+--------+-------+-------+--------+----------+
| 1=0 | 0=0 | 33=0 | null=0 | not 1 | not 0 | not 33 | not null |
+-----+-----+------+--------+-------+-------+--------+----------+
|   0 |   1 |    0 |   NULL |     0 |     1 |      0 |     NULL |
+-----+-----+------+--------+-------+-------+--------+----------+
1 row in set (0.00 sec)

但它们不是相同的操作。

In MySQL, the FALSE keyword is not a boolean piece of data: it's an integer constant that equals zero. On the contrary, ! (aka NOT) is a logical operator that:

Evaluates to 1 if the operand is 0, to
0 if the operand is nonzero, and NOT
NULL returns NULL.

I suppose that there's not much practical difference:

mysql> select 1=0, 0=0, 33=0, null=0, not 1, not 0, not 33, not null;
+-----+-----+------+--------+-------+-------+--------+----------+
| 1=0 | 0=0 | 33=0 | null=0 | not 1 | not 0 | not 33 | not null |
+-----+-----+------+--------+-------+-------+--------+----------+
|   0 |   1 |    0 |   NULL |     0 |     1 |      0 |     NULL |
+-----+-----+------+--------+-------+-------+--------+----------+
1 row in set (0.00 sec)

Yet they're not identical operations.

回复收藏 0 原文

~没有更多了~