mysql bit类型与隐式转换导致SQL结果不正确

现象描述，同一个SQL，不建索引时，结果是正确的，建了索引后，结果不正确。

测试过程

1.建表并定入数据

mysql [localhost:5727] {root} (test) > CREATE TABLE `test1` (
    ->   `id` int NOT NULL AUTO_INCREMENT,
    ->   cname varchar(3),
    ->   status bit(1) not null default b'1',
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB ;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:5727] {root} (test) > insert into test1(cname) values('hjp') ;
Query OK, 1 row affected (0.01 sec)

mysql [localhost:5727] {root} (test) > insert into test1(cname) values('hjp') ;
Query OK, 1 row affected (0.00 sec)

mysql [localhost:5727] {root} (test) > insert into test1(cname) values('hjp') ;
Query OK, 1 row affected (0.00 sec)

mysql [localhost:5727] {root} (test) > select * from test;
+----+-------+--------+
| id | cname | status |
+----+-------+--------+
|  1 | hjp   |
                     |
|  2 | hjp   |       |
|  3 | hjp   |       |
+----+-------+--------+
3 rows in set (0.00 sec)

2.执行同一个SQL，建索引与不建索引，结果不一样。

mysql [localhost:5727] {root} (test) > select count(*) from test1 where status='1';
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)
mysql [localhost:5727] {root} (test) > create index idx_sta on test1(status);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql [localhost:5727] {root} (test) > select count(*) from test1 where status='1';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

从这可以看出，同一SQL，一个结果是3，一个结果是0，区别只在于建了一个索引。

3.关键先要了解bit这个数据类型，这个bit类型实际上存储的是位值。我们可以从hexdump结果看看。

那针对SQL select count(*) from test1 where status=‘1’ 建了索引找不到数据呢，没建索引可以找到数据。没建索引时，隐式转换会把字符1转换成位值1。那建了索引为什么不做隐式转换呢？其实也是做了转换，只是转换的不一样。
字符1的ASCII值是31

mysql [localhost:5727] {root} (test) > select hex('1');
+----------+
| hex('1') |
+----------+
| 31       |
+----------+
1 row in set (0.00 sec)

而位值1的ASCII值是01

mysql [localhost:5727] {root} (test) > select hex(b'1');
+-----------+
| hex(b'1') |
+-----------+
| 01        |
+-----------+
1 row in set (0.00 sec)

4.怎么解决SQL要用到索引？

改SQL的传入值：

  mysql [localhost:5727] {root} (test) > explain select count(*) from test1 where status=b'1';
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test1 | NULL       | ref  | idx_sta       | idx_sta | 1       | const |    3 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql [localhost:5727] {root} (test) > select count(*) from test1 where status=b'1';
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

或者改传值为整数1,其实这里整数1也是隐式转换了：

mysql [localhost:5727] {root} (test) >  explain select count(*) from test1 where status=1;
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test1 | NULL       | ref  | idx_sta       | idx_sta | 1       | const |    3 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql [localhost:5727] {root} (test) > select count(*) from test1 where status=1;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

5.小结：

1.隐式转换对SQL的性能影响很大，用不到索引，当搜索的数据很大时，CPU使用率会大大提高。
2.监控慢SQL，定期优化。