MariaDB社区

标题: 字符串的比较查询 [打印本页]
作者: mysqlkumao    时间: 2008-2-28 13:39
标题: 字符串的比较查询
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    时间: 2008-3-4 13:14
作者: edwin_chen    时间: 2008-3-5 15:20
更正一下，第2条语句是可以使用索引的。下面是我的例子

  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    时间: 2008-4-10 09:12
呵呵，谢谢楼上兄弟的指正，粗心了，没仔细看％号在左在右就上来乱发贴，真该打。



欢迎光临 MariaDB社区 (http://123.56.88.72/) Powered by Discuz! X3.2