MySQL 使用 LIKE 与 = 进行精确的字符串匹配
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/559506/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using LIKE vs. = for exact string match
提问by mluebke
First off, I recognize the differences between the two:
- Like makes available the wildcards % and _
- significant trailing whitespace
- colation issues
首先，我认识到两者之间的区别：
- 像使通配符 % 和 _ 可用
- 重要的尾随空格
- 排序问题
All other things being equal, for an exact string match which is more efficient:
在所有其他条件相同的情况下，对于更有效的精确字符串匹配：
SELECT field WHERE 'a' = 'a';
Or:
或者：
SELECT field WHERE 'a' LIKE 'a';
Or: Is the difference so insignificant that it doesn't matter?
或者：差异是如此微不足道以至于无关紧要？
回答by Suroot
I would say that the = comparator would be faster. The lexical doesn't send the comparison to another lexical system to do general matches. Instead the engine is able to just match or move on. Our db at work has millions of rows and an = is always faster.
我会说 = 比较器会更快。词法不会将比较发送到另一个词法系统来进行一般匹配。相反，引擎能够匹配或继续前进。我们工作中的数据库有数百万行，并且 = 总是更快。
回答by paxdiablo
In a decent DBMS, the DB engine would recognise that there were no wildcard characters in the string and implicitly turn it into a pure equality (not necessarily the same as =). So, you'd only get a small performance hit at the start, usually negligible for any decent-sized query.
在一个体面的 DBMS 中，DB 引擎会识别出字符串中没有通配符并将其隐式转换为纯相等（不一定与 相同=）。因此，您在开始时只会受到很小的性能影响，对于任何大小合适的查询通常可以忽略不计。
However, the MySQL =operator doesn't necessarily act the way you'd expect (as a pure equality check). Specifically, it doesn't by default take into account trailing spaces for CHARand VARCHARdata, meaning that:
但是，MySQL=运算符不一定按您期望的方式运行（作为纯粹的相等性检查）。具体来说，它默认不考虑CHAR和VARCHAR数据的尾随空格，这意味着：
SELECT age WHERE name = 'pax'
will give you rows for 'pax', 'pax<one space>'and'pax<a hundred spaces>'.
将为您提供'pax','pax<one space>'和 的行'pax<a hundred spaces>'。
If you want to do a properequality check, you use the binarykeyword:
如果要进行适当的相等性检查，请使用binary关键字：
SELECT field WHERE name = binary 'pax'
You can test this with something like:
您可以使用以下内容进行测试：
mysql> create table people (name varchar(10));
mysql> insert into people value ('pax');
mysql> insert into people value ('pax ');
mysql> insert into people value ('pax ');
mysql> insert into people value ('pax ');
mysql> insert into people value ('notpax');
mysql> select count(*) from people where name like 'pax';
1
mysql> select count(*) from people where name = 'pax';
4
mysql> select count(*) from people where name = binary 'pax';
1
