字符串的比较查询

mysqlkumao

mysql> select * from abc;
+----+-----------+---------------------+
| c1 | c2        | c3                  |
+----+-----------+---------------------+
|  1 | abc       | 2008-02-28 11:32:29 |
|  2 | abc123    | 2008-02-28 11:32:32 |
|  3 | abc123456 | 2008-02-28 11:32:36 |
|  4 | abc xyz   | 2008-02-28 11:32:41 |
|  5 | abcabc    | 2008-02-28 11:32:53 |
+----+-----------+---------------------+
5 rows in set (0.00 sec)

mysql> select * from abc where c2 like 'abc%';
+----+-----------+---------------------+
| c1 | c2        | c3                  |
+----+-----------+---------------------+
|  1 | abc       | 2008-02-28 11:32:29 |
|  2 | abc123    | 2008-02-28 11:32:32 |
|  3 | abc123456 | 2008-02-28 11:32:36 |
|  4 | abc xyz   | 2008-02-28 11:32:41 |
|  5 | abcabc    | 2008-02-28 11:32:53 |
+----+-----------+---------------------+
5 rows in set (0.00 sec)

mysql> select * from abc where c2>='abc' and c2<'abd';
+----+-----------+---------------------+
| c1 | c2        | c3                  |
+----+-----------+---------------------+
|  1 | abc       | 2008-02-28 11:32:29 |
|  2 | abc123    | 2008-02-28 11:32:32 |
|  3 | abc123456 | 2008-02-28 11:32:36 |
|  4 | abc xyz   | 2008-02-28 11:32:41 |
|  5 | abcabc    | 2008-02-28 11:32:53 |
+----+-----------+---------------------+
5 rows in set (0.00 sec)

第一种like方法不能使用列c2上的索引，为全表扫描，第二种方法可以，所以当数据量大时，第二种方法要比第一种方法快。

kider

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 编辑 ]

mysqlkumao

呵呵，谢谢楼上兄弟的指正，粗心了，没仔细看％号在左在右就上来乱发贴，真该打。