字符串的比较查询

edwin_chen

更正一下，第2条语句是可以使用索引的。下面是我的例子

1. 
   
2. mysql> explain select * from DEVICEINFO where DEVICEMAC LIKE 'aa%';
   
3. +----+---------------+----------------+--------+--------------------------------------+---------------+-----------+-------+------+---------------+
   
4. | id   | select_type  | table             | type   | possible_keys                           | key              | key_len  | ref     | rows| Extra           |
   
5. +----+---------------+----------------+--------+--------------------------------------+---------------+-----------+-------+------+---------------+
   
6. |  1   | SIMPLE        | DEVICEINFO | range | DEVICEMAC,deviceMAC_Index | DEVICEMAC | 768        | NULL |    1  | Using where |
   
7. +----+---------------+----------------+--------+--------------------------------------+---------------+-----------+-------+------+---------------+
   
8. 1 row in set (0.00 sec)

复制代码

1. explain select * from DEVICEINFO where DEVICEMAC LIKE '%aa';
   
2. +----+-------------+------------+------+---------------+------+---------+------+-------+-------------+
   
3. | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
   
4. +----+-------------+------------+------+---------------+------+---------+------+-------+-------------+
   
5. | 1 | SIMPLE | DEVICEINFO | ALL | NULL | NULL | NULL | NULL | 10126 | Using where |
   
6. +----+-------------+------------+------+---------------+------+---------+------+-------+-------------+
   
7. 1 row in set (0.00 sec)

复制代码

也就是说like子句中如果％出现在右边，则可以使用索引，出现在左边则不可以。

[ 本帖最后由 edwin_chen 于 2008-3-5 15:24 编辑 ]