使用LIKE vs. =进行精确的字符串匹配

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?

或者：差异如此微不足道以至于无关紧要？

2 个解决方案

#1

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.

我会说=比较器会更快。词汇不会将比较发送到另一个词汇系统来进行一般匹配。相反，引擎只能匹配或继续前进。我们的数据库工作有数百万行，而且=总是更快。

#2

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中，数据库引擎会识别字符串中没有通配符，并隐式将其转换为纯等式（不一定与=相同）。因此，您只能在一开始就获得较小的性能，通常可以忽略不计任何体面的查询。

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 CHAR and VARCHAR data, 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 '和'pax '的行。

If you want to do a proper equality check, you use the binary keyword:

如果要进行适当的相等检查，请使用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

#1

#2