mysqlkumao 发表于 2008-2-28 13:39:28

字符串的比较查询

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上的索引，为全表扫描，第二种方法可以，所以当数据量大时，第二种方法要比第一种方法快。

:lol

kider 发表于 2008-3-4 13:14:04

:victory:

edwin_chen 发表于 2008-3-5 15:20:26

更正一下，第2条语句是可以使用索引的。下面是我的例子
mysql> explain select * from DEVICEINFO where DEVICEMAC LIKE 'aa%';
+----+---------------+----------------+--------+--------------------------------------+---------------+-----------+-------+------+---------------+
| id   | select_type| table             | type   | possible_keys                           | key            | key_len| ref   | rows| Extra         |
+----+---------------+----------------+--------+--------------------------------------+---------------+-----------+-------+------+---------------+
|1   | SIMPLE      | DEVICEINFO | range | DEVICEMAC,deviceMAC_Index | DEVICEMAC | 768      | NULL |    1| Using where |
+----+---------------+----------------+--------+--------------------------------------+---------------+-----------+-------+------+---------------+
1 row in set (0.00 sec)explain select * from DEVICEINFO where DEVICEMAC LIKE '%aa';
+----+-------------+------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+------+---------+------+-------+-------------+
| 1 | SIMPLE | DEVICEINFO | ALL | NULL | NULL | NULL | NULL | 10126 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)也就是说like子句中如果％出现在右边，则可以使用索引，出现在左边则不可以。

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

mysqlkumao 发表于 2008-4-10 09:12:20

呵呵，谢谢楼上兄弟的指正，粗心了，没仔细看％号在左在右就上来乱发贴，真该打。
页: [1]
查看完整版本: 字符串的比较查询