不同的运营商

LIKE和=是不同的运算符。这里的大多数答案都集中在通配符支持上，这并不是这些操作符之间的唯一区别!

=是一个比较运算符，操作数字和字符串。比较字符串时，比较操作符比较整个字符串。

LIKE是一个字符串操作符，用于逐个字符进行比较。

更复杂的是，这两个操作符都使用了对比较结果有重要影响的排序规则。

激励的例子

让我们首先确定一个例子，其中这些运算符产生明显不同的结果。请允许我引用MySQL手册中的一句话:

根据SQL标准，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手册的这一页被称为字符串比较函数，=没有被讨论，这意味着=不是严格意义上的字符串比较函数。

=如何工作?

SQL标准§8.2描述了=如何比较字符串:

The comparison of two character strings is determined as follows: a) If the length in characters of X is not equal to the length in characters of Y, then the shorter string is effectively replaced, for the purposes of comparison, with a copy of itself that has been extended to the length of the longer string by concatenation on the right of one or more pad characters, where the pad character is chosen based on CS. If CS has the NO PAD attribute, then the pad character is an implementation-dependent character different from any character in the character set of X and Y that collates less than any string under CS. Otherwise, the pad character is a <space>. b) The result of the comparison of X and Y is given by the collating sequence CS. c) Depending on the collating sequence, two strings may compare as equal even if they are of different lengths or contain different sequences of characters. When the operations MAX, MIN, DISTINCT, references to a grouping column, and the UNION, EXCEPT, and INTERSECT operators refer to character strings, the specific value selected by these operations from a set of such equal values is implementation-dependent.

(强调)。

这是什么意思?这意味着在比较字符串时，=操作符只是当前排序规则的一个薄包装。排序规则是一个库，它具有各种比较字符串的规则。下面是一个来自MySQL的二进制排序的例子:

static int my_strnncoll_binary(const CHARSET_INFO *cs __attribute__((unused)),
                               const uchar *s, size_t slen,
                               const uchar *t, size_t tlen,
                               my_bool t_is_prefix)
{
  size_t len= MY_MIN(slen,tlen);
  int cmp= memcmp(s,t,len);
  return cmp ? cmp : (int)((t_is_prefix ? len : slen) - tlen);
}

这种特殊的排序恰好是逐字节比较的(这就是为什么它被称为“二进制”——它没有给字符串任何特殊的含义)。其他排序方法可以提供更高级的比较。

例如，这里有一个支持大小写不敏感比较的UTF-8排序规则。代码太长，不能粘贴在这里，但是请转到那个链接并读取my_strnncollsp_utf8mb4()的主体。这种排序可以一次处理多个字节，并且可以应用各种转换(例如不区分大小写的比较)。=运算符完全从反复无常的排序中抽象出来。

LIKE是如何工作的?

SQL标准§8.5描述了LIKE如何比较字符串:

The <predicate> M LIKE P is true if there exists a partitioning of M into substrings such that: i) A substring of M is a sequence of 0 or more contiguous <character representation>s of M and each <character representation> of M is part of exactly one substring. ii) If the i-th substring specifier of P is an arbitrary character specifier, the i-th substring of M is any single <character representation>. iii) If the i-th substring specifier of P is an arbitrary string specifier, then the i-th substring of M is any sequence of 0 or more <character representation>s. iv) If the i-th substring specifier of P is neither an arbitrary character specifier nor an arbitrary string specifier, then the i-th substring of M is equal to that substring specifier according to the collating sequence of the <like predicate>, without the appending of <space> characters to M, and has the same length as that substring specifier. v) The number of substrings of M is equal to the number of substring specifiers of P.

(强调)。

这太啰嗦了，让我们分解一下。第ii项和iii项分别表示通配符_和%。如果P不包含任何通配符，则只有第iv项适用。这是OP提出的感兴趣的情况。

在本例中，它使用当前排序规则将M中的每个“子字符串”(单个字符)与P中的每个子字符串进行比较。

结论

底线是，当比较字符串时，=比较整个字符串，而LIKE一次比较一个字符。两种比较都使用当前排序规则。这种差异在某些情况下会导致不同的结果，这在本文的第一个例子中得到了证明。

你应该使用哪一个?没有人能告诉你——你需要使用适合你用例的那个。不要过早地通过切换比较运算符进行优化。

要解决最初关于性能的问题，可以归结为索引利用率。当进行简单的表扫描时，“LIKE”和“=”是相同的。当涉及到索引时，这取决于LIKE子句是如何形成的。更具体地说，通配符的位置是什么?

考虑以下几点:

CREATE TABLE test(
    txt_col  varchar(10) NOT NULL
)
go

insert test (txt_col)
select CONVERT(varchar(10), row_number() over (order by (select 1))) r
  from master..spt_values a, master..spt_values b
go

CREATE INDEX IX_test_data 
    ON test (txt_col);
go 

--Turn on Show Execution Plan
set statistics io on

--A LIKE Clause with a wildcard at the beginning
DBCC DROPCLEANBUFFERS
SELECT txt_Col from test where txt_col like '%10000'
--Results in
--Table 'test'. Scan count 3, logical reads 15404, physical reads 2, read-ahead reads 15416, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Index SCAN is 85% of Query Cost

--A LIKE Clause with a wildcard in the middle
DBCC DROPCLEANBUFFERS
SELECT txt_Col from test where txt_col like '1%99'
--Results in
--Table 'test'. Scan count 1, logical reads 3023, physical reads 3, read-ahead reads 3018, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Index Seek is 100% of Query Cost for test data, but it may result in a Table Scan depending on table size/structure

--A LIKE Clause with no wildcards
DBCC DROPCLEANBUFFERS
SELECT txt_Col from test where txt_col like '10000'
--Results in
--Table 'test'. Scan count 1, logical reads 3, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Index Seek is 100% of Query Cost
GO

--an "=" clause = does Index Seek same as above
DBCC DROPCLEANBUFFERS
SELECT txt_Col from test where txt_col = '10000'
--Results in
--Table 'test'. Scan count 1, logical reads 3, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Index Seek is 100% of Query Cost
GO


DROP TABLE test

当使用"="和"LIKE"时，在查询计划的创建中也可能存在可以忽略不计的差异。

使用=可以避免在运行时构建查询时字符串中的通配符和特殊字符冲突。

这使程序员的工作更轻松，因为不必转义所有可能滑入LIKE子句并不能产生预期结果的特殊通配符。毕竟，=是99%的用例场景，每次都必须逃避它们将是一种痛苦。

在90年代翻白眼

我也怀疑它有点慢，但如果模式中没有通配符，我怀疑它的意义。

=和LIKE是不一样的;

=匹配精确的字符串 LIKE匹配可能包含通配符的字符串(%)

LIKE和=是不同的。LIKE是在搜索查询中使用的。它还允许像_(简单字符通配符)和%(多字符通配符)这样的通配符。

如果你想要精确匹配，应该使用=，它会更快。

这个网站解释了LIKE

除了LIKE可以使用通配符外，还有一个区别在于尾随空格:=运算符忽略尾随空格，而LIKE则不会。