MySQL社区

标题: SQL语句中一个符号引发的“惨案”！ [打印本页]
作者: squall    时间: 2012-12-19 10:54
标题: SQL语句中一个符号引发的“惨案”！
本帖最后由 squall 于 2012-12-19 10:56 编辑

表结构和数据如下：
  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，也可以用到索引。
作者: pz9042@163.com    时间: 2013-3-14 09:59
提示: 作者被禁止或删除 内容自动屏蔽



欢迎光临 MySQL社区 (http://www.mysqlpub.com/) Powered by Discuz! X3.2