不同的运营商

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"时，在查询计划的创建中也可能存在可以忽略不计的差异。

equals(=)操作符是一个“比较操作符，比较两个值是否相等”。换句话说，在SQL语句中，除非等式两边相等，否则它不会返回true。例如:

SELECT * FROM Store WHERE Quantity = 200;

LIKE操作符“实现了模式匹配比较”，尝试将“字符串值与包含通配符的模式字符串”进行匹配。例如:

SELECT * FROM Employees WHERE Name LIKE 'Chris%';

LIKE通常只用于字符串，等号(我相信)更快。等号运算符将通配符视为文字字符。返回结果的差异如下:

SELECT * FROM Employees WHERE Name = 'Chris';

And

SELECT * FROM Employees WHERE Name LIKE 'Chris';

将返回相同的结果，尽管使用LIKE通常会花费更长的时间，因为它是一个模式匹配。然而,

SELECT * FROM Employees WHERE Name = 'Chris%';

And

SELECT * FROM Employees WHERE Name LIKE 'Chris%';

将返回不同的结果，其中使用“=”只会返回带有“Chris%”的结果，LIKE操作符将返回以“Chris”开头的任何结果。

一些好的信息可以在这里找到。

除了通配符，=和LIKE之间的区别还取决于SQL服务器的类型和列类型。

举个例子:

CREATE TABLE testtable (
  varchar_name VARCHAR(10),
  char_name CHAR(10),
  val INTEGER
);

INSERT INTO testtable(varchar_name, char_name, val)
    VALUES ('A', 'A', 10), ('B', 'B', 20);

SELECT 'VarChar Eq Without Space', val FROM testtable WHERE varchar_name='A'
UNION ALL
SELECT 'VarChar Eq With Space', val FROM testtable WHERE varchar_name='A '
UNION ALL
SELECT 'VarChar Like Without Space', val FROM testtable WHERE varchar_name LIKE 'A'
UNION ALL
SELECT 'VarChar Like Space', val FROM testtable WHERE varchar_name LIKE 'A '
UNION ALL
SELECT 'Char Eq Without Space', val FROM testtable WHERE char_name='A'
UNION ALL
SELECT 'Char Eq With Space', val FROM testtable WHERE char_name='A '
UNION ALL
SELECT 'Char Like Without Space', val FROM testtable WHERE char_name LIKE 'A'
UNION ALL
SELECT 'Char Like With Space', val FROM testtable WHERE char_name LIKE 'A '

Using MS SQL Server 2012, the trailing spaces will be ignored in the comparison, except with LIKE when the column type is VARCHAR. Using MySQL 5.5, the trailing spaces will be ignored for =, but not for LIKE, both with CHAR and VARCHAR. Using PostgreSQL 9.1, spaces are significant with both = and LIKE using VARCHAR, but not with CHAR (see documentation). The behaviour with LIKE also differs with CHAR. Using the same data as above, using an explicit CAST on the column name also makes a difference: SELECT 'CAST none', val FROM testtable WHERE char_name LIKE 'A' UNION ALL SELECT 'CAST both', val FROM testtable WHERE CAST(char_name AS CHAR) LIKE CAST('A' AS CHAR) UNION ALL SELECT 'CAST col', val FROM testtable WHERE CAST(char_name AS CHAR) LIKE 'A' UNION ALL SELECT 'CAST value', val FROM testtable WHERE char_name LIKE CAST('A' AS CHAR) This only returns rows for "CAST both" and "CAST col".

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

LIKE关键字无疑带有“性能价签”。也就是说，如果您的输入字段可能包含要在查询中使用的通配符，那么我建议仅在输入包含其中一个通配符时使用LIKE。否则，使用标准等于比较。

最好的祝福……