这两个语句具有完全不同的性能:
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);
最佳答案
从逻辑上讲，这些操作是相同的，但是MySQL的优化器只是不太聪明，无法在createIndexed = 0中看到NOT createIndexed。FALSE中的MySQL只是0的同义词，而TRUE是1的同义词。
此条件为假:
SELECT 2 = TRUE
--
0
，因此第一个查询只是
ref知道的与0的纯索引MySQL比较，而第二个查询包含MySQL无法表示为可表达的更复杂的逻辑。关于sql - !col和col = false在MySQL中有什么区别？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/2517206/