SQL语句中一个符号引发的“惨案”！

squall

表结构和数据如下：

1. DROP TABLE IF EXISTS `student`;
   
2. CREATE TABLE `student` (
   
3. `id` int(11) NOT NULL DEFAULT '0',
   
4. `name` varchar(6) DEFAULT NULL,
   
5. `class` int(11) DEFAULT NULL,
   
6. `score` varchar(10) DEFAULT NULL,
   
7. PRIMARY KEY (`id`),
   
8. KEY `name` (`name`),
   
9. KEY `IX_score` (`score`),
   
10. KEY `IX_class` (`class`)
    
11. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

复制代码

1. mysql> select * from student;
   
2. +----+------+-------+-------+
   
3. | id | name | class | score |
   
4. +----+------+-------+-------+
   
5. | 1 | a | 1 | 56 |
   
6. | 2 | b | 1 | 61 |
   
7. | 3 | c | 2 | 78 |
   
8. | 4 | d | 2 | 45 |
   
9. | 5 | e | 3 | 76 |
   
10. | 6 | f | 3 | 89 |
    
11. | 7 | g | 4 | 43 |
    
12. | 8 | h | 4 | 90 |
    
13. +----+------+-------+-------+
    
14. 8 rows in set (0.00 sec)
    

复制代码

执行语句 select * from student where score = 60;

看看执行计划：

1. mysql> explain select * from student where score = 60;
   
2. +----+-------------+---------+------+---------------+------+---------+------+------+-------------+
   
3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
   
4. +----+-------------+---------+------+---------------+------+---------+------+------+-------------+
   
5. | 1 | SIMPLE | student | ALL | IX_score | NULL | NULL | NULL | 8 | Using where |
   
6. +----+-------------+---------+------+---------------+------+---------+------+------+-------------+
   
7. 1 row in set (0.00 sec)

复制代码

没有用到索引？

其中的原因，虽然保存的是数字，但是列设置为了varchar，那么在MySQL中就用不到索引了，加个引号会怎么样呢：

1. mysql> explain select * from student where score = '60' ;
   
2. +----+-------------+---------+------+---------------+----------+---------+-------+------+-----------------------+
   
3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
   
4. +----+-------------+---------+------+---------------+----------+---------+-------+------+-----------------------+
   
5. | 1 | SIMPLE | student | ref | IX_score | IX_score | 33 | const | 1 | Using index condition |
   
6. +----+-------------+---------+------+---------------+----------+---------+-------+------+-----------------------+
   
7. 1 row in set (0.00 sec)

复制代码

另外，要不就把列类型改成integer，也可以用到索引。