sql语句中对布尔字段的处理

Question

前提：
布尔就是那个tinyint，我试过里面可以是0或者非0值问题：
我用sql语句筛选这一带有索引的字段
select * from t where b=true  这样是不行的
select * from t where b<>0    只能这样用解释：
1、可是貌似，b<>0这个使用后，索引实际上就无效了（我是看到有个帖子这么说的，估计诸位应该也看过这个帖子）
2、因为b字段的值很多时候竟然是 -1 而不是 1，而有时候也可能是1，所以我试过用cbool(b)=true来解决，但失败解决：
高手出招吧。

Answer 1

mysql> create table t_dingyanwei(id int primary key,b tinyint,key (b));
Query OK, 0 rows affected (0.10 sec)mysql> insert into t_dingyanwei values
    -> (1,1),
    -> (2,1),
    -> (3,0),
    -> (4,0),
    -> (5,1),
    -> (6,0),
    -> (7,0);
Query OK, 7 rows affected (0.07 sec)
Records: 7  Duplicates: 0  Warnings: 0mysql> select * from t_dingyanwei;
+----+------+
| id | b    |
+----+------+
|  1 |    1 |
|  2 |    1 |
|  3 |    0 |
|  4 |    0 |
|  5 |    1 |
|  6 |    0 |
|  7 |    0 |
+----+------+
7 rows in set (0.17 sec)mysql> show index from t_dingyanwei;
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table        | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| t_dingyanwei |          0 | PRIMARY  |            1 | id          |          A|           7 |     NULL | NULL   |      | BTREE      |         |
| t_dingyanwei |          1 | b        |            1 | b           |          A|        NULL |     NULL | NULL   | YES  | BTREE      |         |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.02 sec)mysql>
mysql> select * from t_dingyanwei where b=true;
+----+------+
| id | b    |
+----+------+
|  1 |    1 |
|  2 |    1 |
|  5 |    1 |
+----+------+
3 rows in set (0.04 sec)mysql> select * from t_dingyanwei where b<>0;
+----+------+
| id | b    |
+----+------+
|  1 |    1 |
|  2 |    1 |
|  5 |    1 |
+----+------+
3 rows in set (0.00 sec)mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)mysql> explain select * from t_dingyanwei where b=true;
+----+-------------+--------------+------+---------------+------+---------+-----
--+------+-------------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref
  | rows | Extra       |
+----+-------------+--------------+------+---------------+------+---------+-----
--+------+-------------+
|  1 | SIMPLE      | t_dingyanwei | ref  | b             | b    | 2       | cons
t |    3 | Using where |
+----+-------------+--------------+------+---------------+------+---------+-----
--+------+-------------+
1 row in set (0.00 sec)mysql> explain select * from t_dingyanwei where b=1;
+----+-------------+--------------+------+---------------+------+---------+-----
--+------+-------------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref
  | rows | Extra       |
+----+-------------+--------------+------+---------------+------+---------+-----
--+------+-------------+
|  1 | SIMPLE      | t_dingyanwei | ref  | b             | b    | 2       | cons
t |    3 | Using where |
+----+-------------+--------------+------+---------------+------+---------+-----
--+------+-------------+
1 row in set (0.00 sec)mysql> explain select * from t_dingyanwei where b>0;
+----+-------------+--------------+------+---------------+------+---------+-----
-+------+-------------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref
 | rows | Extra       |
+----+-------------+--------------+------+---------------+------+---------+-----
-+------+-------------+
|  1 | SIMPLE      | t_dingyanwei | ALL  | b             | NULL | NULL    | NULL
 |    7 | Using where |
+----+-------------+--------------+------+---------------+------+---------+-----
-+------+-------------+
1 row in set (0.00 sec)mysql>

Answer 2

测试结果如一楼所贴出来的。select * from t where b=true  这样是不行的
你可以看到，你的这个说法并不成立，是可以的。1、可是貌似，b<>0这个使用后，索引实际上就无效了（我是看到有个帖子这么说的，估计诸位应该也看过这个帖子）
是的，因为你用的是！=，这样很难使用索引。2、因为b字段的值很多时候竟然是 -1 而不是 1，而有时候也可能是1，所以我试过用cbool(b)=true来解决，但失败
MySQL中没有CBOOL这个函数。
如果是在你的程序语言中，由于TINYINT是一个smallint 所以不能转换。 不过要看你具体的编程语言。

Answer 3

针对这种情况，建议作一次全局更新，将<>0的所有值全更新为1
这样，你就可以放心的使用索引了。

Answer 4

如果我使用select * from t where b=1 or b=-1 这样是不是一定比 b<>0 要好呢？

Answer 5

MYSQL中TRUE实质上是1，FALSE是0。 
如果你的B字段中有其它值，则需要按 #3楼建议处理一下，全部设置为1

Answer 6

应该差不多，都不会走索引。 建议的做法是用两个查询然后UNION ALL在一起。
select * from t_dingyanwei where b=1 union all select * from t_dingyanwei where b=-1;

Answer 7

如果or的字段特别多，那么union all一定会比or效率高吗？
当然，前提是记录数足够多的话。

Answer 8

假设表T中有 100,000 条记录。 如果 1 有 1000 条，-1有1000条，2 有1000条，3有1000条，则UNION ALL理论上会快一些如果T中有100,000 条记录。如果说有50,000条，-1有40,000条，则使用索引还不如直接全表扫描。

Answer 9

设不设置索引是我说了算，用不用索引，应该是mysql说了算吧所以我认为，设置索引后，如果会降低效率，那么mysql貌似应该会自动调整吧，是不是这样呢？

Answer 10

对！对！建议参考手册中下面内容。
MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html
7.2. 优化SELECT语句和其它查询
7.2.1. EXPLAIN语法（获取SELECT相关信息）
7.2.2. 估计查询性能
7.2.3. SELECT查询的速度
7.2.4. MySQL怎样优化WHERE子句