使用 '=' 或 LIKE 比较 SQL 中的字符串？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/515039/
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
Use '=' or LIKE to compare strings in SQL?
提问by guerda
There's the (almost religious) discussion, if you should use LIKE or '=' to compare strings in SQL statements.
如果您应该使用 LIKE 或 '=' 来比较 SQL 语句中的字符串，则有（几乎是宗教性的）讨论。
- Are there reasons to use LIKE?
- Are there reasons to use '='?
- Performance? Readability?
- 有理由使用 LIKE 吗？
- 是否有理由使用“=”？
- 表现？可读性？
采纳答案by Techmaddy
To see the performance difference, try this:
要查看性能差异，请尝试以下操作：
SELECT count(*)
FROM master..sysobjects as A
JOIN tempdb..sysobjects as B
on A.name = B.name
SELECT count(*)
FROM master..sysobjects as A
JOIN tempdb..sysobjects as B
on A.name LIKE B.name
Comparing strings with '=' is much faster.
用 '=' 比较字符串要快得多。
回答by soulmerge
LIKEand the equality operator have different purposes, they don't do the same thing:=is much faster, whereas LIKEcan interpret wildcards. Use =wherever you can and LIKEwherever you must.
LIKE并且相等运算符有不同的目的，它们不做同样的事情：=速度快得多，而LIKE可以解释通配符。=随时随地使用LIKE。
SELECT * FROM user WHERE login LIKE 'Test%';
Sample matches:
示例匹配：
TestUser1
TestUser2
TestU
Test
TestUser1
TestUser2
TestU
测试
回答by Stu Andrews
In my small experience:
以我的小经验：
"=" for Exact Matches.
“=”表示精确匹配。
"LIKE" for Partial Matches.
“LIKE”表示部分匹配。
回答by Ceilingfish
There's a couple of other tricks that Postgres offers for string matching (if that happens to be your DB):
Postgres 还为字符串匹配提供了一些其他技巧（如果这恰好是您的数据库）：
ILIKE, which is a case insensitive LIKE match:
ILIKE，这是一个不区分大小写的 LIKE 匹配：
select * from people where name ilike 'JOHN'
Matches:
火柴：
- John
- john
- JOHN
- 约翰
- 约翰
- 约翰
And if you want to get really mad you can use regular expressions:
如果你真的想生气，你可以使用正则表达式：
select * from people where name ~ 'John.*'
Matches:
火柴：
- John
- Johnathon
- Johnny
- 约翰
- 乔纳森
- 约翰尼
回答by Philip H
Just as a heads up, the '=' operator will pad strings with spaces in Transact-SQL. So 'abc' = 'abc 'will return true; 'abc' LIKE 'abc 'will return false. In most cases '=' will be correct, but in a recent case of mine it was not.
提醒一下，'=' 运算符将在 Transact-SQL 中用空格填充字符串。所以'abc' = 'abc '会返回真；'abc' LIKE 'abc '将返回假。在大多数情况下 '=' 是正确的，但在我最近的一个案例中却不是。
So while '=' is faster, LIKE might more explicitly state your intentions.
因此，虽然 '=' 更快，但 LIKE 可能更明确地说明您的意图。
回答by Techmaddy
For pattern matching use LIKE. For exact match =.
对于模式匹配，请使用 LIKE。对于完全匹配 =。
回答by Mehrdad Afshari
LIKEis used for pattern matching and =is used for equality test (as defined by the COLLATIONin use).
LIKE用于模式匹配并=用于相等性测试（由COLLATIONin use定义）。
=can use indexes while LIKEqueries usually require testing every single record in the result set to filter it out (unless you are using full text search) so =has better performance.
=可以使用索引，而LIKE查询通常需要测试结果集中的每条记录以将其过滤掉（除非您使用全文搜索），因此=具有更好的性能。
回答by Gishu
LIKE does matching like wildcards char [*, ?] at the shell
LIKE '%suffix' - give me everything that ends with suffix. You couldn't do that with =
Depends on the case actually.
LIKE 在外壳上像通配符 char [*, ?] 一样匹配
LIKE '%suffix' - 给我以后缀结尾的所有内容。你不能用 = 做到这一点
取决于实际情况。
回答by Uniotter
There is another reason for using "like" even if the performance is slower: Character values are implicitly converted to integer when compared, so:
即使性能较慢，使用“like”还有另一个原因：字符值在比较时隐式转换为整数，因此：
declare @transid varchar(15)
声明@transid varchar(15)
if @transid != 0
如果@transid != 0
will give you a "The conversion of the varchar value '123456789012345' overflowed an int column" error.
会给你一个“varchar值'123456789012345'的转换溢出了一个int列”错误。
