MySQL，`column` like '一些文字' 是否就等于 `column` = '一些文字'，有什么区别是呢？

Question

如：

select * from `table` where `column` like '一些文字'
select * from `table` where `column` = '一些文字'

的比较。like 中的文字不加通配符。两者执行上有何异同？

Answer 1

建议你用 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.

Answer 2

看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)