boolean在mysql,在where子句上使用mysql boolean

架构

create table t123

(

id int auto_increment primary key,

x boolean not null,

key(x)

);

truncate table t123;

insert t123(x) values (false),(true),(false),(true),(false),(true),(false),(true),(false),(true),(false),(true);

insert t123(x) select (x) from t123;

insert t123(x) select (x) from t123;

insert t123(x) select (x) from t123;

insert t123(x) select (x) from t123;

insert t123(x) select (x) from t123;

insert t123(x) select (x) from t123;

insert t123(x) select (x) from t123;

insert t123(x) select (x) from t123;

insert t123(x) select (x) from t123;

insert t123(x) select (x) from t123;

insert t123(x) select (x) from t123;

insert t123(x) select (x) from t123;

insert t123(x) select (x) from t123;

insert t123(x) select (x) from t123;

insert t123(x) select (x) from t123;

insert t123(x) select (x) from t123;

insert t123(x) select (x) from t123;

insert t123(x) select (x) from t123;

select count(*) as rowCount from t123;

+----------+

| rowCount |

+----------+

| 3145728 |

+----------+

我们现在有3.1M行.

一个

explain SELECT * FROM t123 WHERE x=false;

+----+-------------+-------+------+---------------+------+---------+-------+---------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+------+---------------+------+---------+-------+---------+-------------+

| 1 | SIMPLE | t123 | ref | x | x | 1 | const | 1570707 | Using index |

+----+-------------+-------+------+---------------+------+---------+-------+---------+-------------+

乙

explain SELECT * FROM t123 WHERE NOT `x`;

+----+-------------+-------+-------+---------------+------+---------+------+---------+--------------------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+-------+-------+---------------+------+---------+------+---------+--------------------------+

| 1 | SIMPLE | t123 | index | NULL | x | 1 | NULL | 3141414 | Using where; Using index |

+----+-------------+-------+-------+---------------+------+---------+------+---------+--------------------------+

因此A更快,因为它能够使用本机数据类型(如在具有它的索引中所见),并且由于B处理数据转换的方式(并且确实导致表扫描)而不强制进行表扫描

它的证明在解释输出中,具有确定答案所需的行数,并且即使在两个查询的列上也没有使用索引(ref列).