如:
select * from `table` where `column` like '一些文字'
select * from `table` where `column` = '一些文字'
的比较。like 中的文字不加通配符。两者执行上有何异同?
建议你用 EXPLAIN
语句来检查一下这两条语句的的执行结果。
下面是我在自己机器上用 phpMyAdmin
的性能分析工具做的测试。
第一条:
SELECT *
FROM `Affiliate_Impression_Log`
WHERE ProductID = '7360'
LIMIT 0 , 30
Starting 30 µs
Waiting For Query Cache Lock 6 µs
Checking Query Cache For Query 43 µs
Checking Permissions 8 µs
Opening Tables 24 µs
System Lock 7 µs
Table Lock 8 µs
Waiting For Query Cache Lock 19 µs
Init 41 µs
Optimizing 16 µs
Statistics 21 µs
Preparing 14 µs
Executing 5 µs
Sending Data 6.8 ms
End 54 µs
Query End 9 µs
Closing Tables 19 µs
Freeing Items 13 µs
Updating Status 11 µs
Waiting For Query Cache Lock 5 µs
Updating Status 722 µs
Waiting For Query Cache Lock 16 µs
Updating Status 4 µs
Storing Result In Query Cache 10 µs
Cleaning Up 9 µs
第二条:
SELECT *
FROM `Affiliate_Impression_Log`
WHERE ProductID LIKE '7360'
LIMIT 0 , 30
Starting 32 µs
Waiting For Query Cache Lock 7 µs
Checking Query Cache For Query 62 µs
Checking Permissions 8 µs
Opening Tables 25 µs
System Lock 8 µs
Table Lock 9 µs
Waiting For Query Cache Lock 25 µs
Init 37 µs
Optimizing 17 µs
Statistics 26 µs
Preparing 16 µs
Executing 6 µs
Sending Data 6.9 ms
End 25 µs
Query End 7 µs
Closing Tables 12 µs
Freeing Items 10 µs
Updating Status 8 µs
Waiting For Query Cache Lock 4 µs
Updating Status 27 µs
Waiting For Query Cache Lock 5 µs
Updating Status 12 µs
Storing Result In Query Cache 6 µs
Cleaning Up 9 µs
目前看来这两者几乎是没有区别的。
你最好自己做一下测试,因为在不同的机器上会有不同的表现,可能字段类型也会有影响。
在 SO 上找到了这个答案:http://stackoverflow.com/questions/543580/equals-vs-like
注意这几点文字:
LIKE is generally used only with strings and equals (I believe) is faster. The equals operator treats wild-card characters as literal characters. The difference in results returned are as follows:
SELECT * FROM Employees WHERE Name = 'Chris';
And
SELECT * FROM Employees WHERE Name LIKE 'Chris';
Would return the same result, though using LIKE would generally take longer as its a pattern match.
Depends on the database system.
Generally with no special characters, yes, = and LIKE are the same.
Some database systems, however, may treat collation settings differently with the different operators.
For instance, in MySQL comparisons with = on strings is always case-insensitive by default, so LIKE without special characters is the same. On some other RDBMS's LIKE is case-insensitive while = is not.
看mysql 手册:
http://dev.mysql.com/doc/refman/5.7/en/string-comparison-functions.html#operator_like
like 和 = 是有区别的
手册里举例:
mysql> SELECT 'ä' LIKE 'ae' COLLATE latin1_german2_ci;
+-----------------------------------------+
| 'ä' LIKE 'ae' COLLATE latin1_german2_ci |
+-----------------------------------------+
| 0 |
+-----------------------------------------+
mysql> SELECT 'ä' = 'ae' COLLATE latin1_german2_ci;
+--------------------------------------+
| 'ä' = 'ae' COLLATE latin1_german2_ci |
+--------------------------------------+
| 1 |
+--------------------------------------+
mysql> SELECT 'a' = 'a ', 'a' LIKE 'a ';
+------------+---------------+
| 'a' = 'a ' | 'a' LIKE 'a ' |
+------------+---------------+
| 1 | 0 |
+------------+---------------+
1 row in set (0.00 sec)