MySQL LIKE与LOCATE [英] MySQL LIKE vs LOCATE

问题描述

有人知道哪个更快吗?

SELECT * FROM table WHERE column LIKE '%text%';

或

SELECT * FROM table WHERE LOCATE('text',column)>0;

推荐答案

添加于2015年4月20日:请另阅读 Hallie的答案下方

Added April 20th, 2015: Please read also Hallie's answer below

第一个，但很少.主要是因为它不必进行额外的> 0比较.

First one but marginally. Mostly because it doesn't have to do an extra > 0 comparison.

mysql> SELECT BENCHMARK(100000000,LOCATE('foo','foobar'));
+---------------------------------------------+
| BENCHMARK(100000000,LOCATE('foo','foobar')) |
+---------------------------------------------+
|                                           0 |
+---------------------------------------------+
1 row in set (3.24 sec)

mysql> SELECT BENCHMARK(100000000,LOCATE('foo','foobar') > 0);
+-------------------------------------------------+
| BENCHMARK(100000000,LOCATE('foo','foobar') > 0) |
+-------------------------------------------------+
|                                               0 |
+-------------------------------------------------+
1 row in set (4.63 sec)


mysql> SELECT BENCHMARK(100000000,'foobar' LIKE '%foo%');
+--------------------------------------------+
| BENCHMARK(100000000,'foobar' LIKE '%foo%') |
+--------------------------------------------+
|                                          0 |
+--------------------------------------------+
1 row in set (4.28 sec)


mysql> SELECT @@version;
+----------------------+
| @@version            |
+----------------------+
| 5.1.36-community-log |
+----------------------+
1 row in set (0.01 sec)

这篇关于MySQL LIKE与LOCATE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！