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?
- 有理由使用LIKE吗?
- Are there reasons to use '='?
- 有理由使用'='吗?
- Performance? Readability?
- 性能?可读性呢?
9 个解决方案
#1
100
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.
将字符串与'='进行比较要快得多。
#2
168
LIKE and the equality operator have different purposes, they don't do the same thing: = is much faster, whereas LIKE can interpret wildcards. Use = wherever you can and LIKE wherever you must.
等式运算符有不同的目的，它们不做相同的事情:=要快得多，而LIKE可以解释通配符。用法=无论你在哪里，喜欢在哪里。
SELECT * FROM user WHERE login LIKE 'Test%';
-- Matches
-- TestUser1
-- TestUser2
-- TestU
-- Test
-- etc.
#3
29
In my small experience:
在我的小经验:
"=" for Exact Matches.
“=”精确匹配。
"LIKE" for Partial Matches.
“喜欢”部分匹配。
#4
11
There's a couple of other tricks that Postgres offers for string matching (if that happens to be your DB):
对于字符串匹配，Postgres还提供了一些其他的技巧(如果碰巧是您的DB):
ILIKE, which is a case insensitive LIKE match:
就像match一样不敏感的案例:
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
- Johnathon
- Johnny
- 约翰尼
#5
9
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会返回true;像abc这样的abc会返回false。在大多数情况下'='都是正确的，但在我最近的一个例子中却不是。
So while '=' is faster, LIKE might more explicitly state your intentions.
所以，虽然'='更快，比如可能更明确地陈述你的意图。
http://support.microsoft.com/kb/316626
http://support.microsoft.com/kb/316626
#6
6
For pattern matching use LIKE. For exact match =.
用于模式匹配使用LIKE。精确匹配=。
#7
6
LIKE is used for pattern matching and = is used for equality test (as defined by the COLLATION in use).
LIKE用于模式匹配，=用于相等性测试(由使用的排序规则定义)。
= can use indexes while LIKE queries 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.
=可以使用索引，而与查询类似，查询通常需要测试结果集中的每个记录，以过滤它(除非您正在使用全文搜索)，因此=具有更好的性能。
#8
3
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.
就像在shell中匹配通配符char[*， ?]，比如'%后缀' -给我所有以后缀结尾的东西。你不能用=来做，这取决于实际情况。
#9
3
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列”错误。
#1
100
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.
将字符串与'='进行比较要快得多。
#2
168
LIKE and the equality operator have different purposes, they don't do the same thing: = is much faster, whereas LIKE can interpret wildcards. Use = wherever you can and LIKE wherever you must.
等式运算符有不同的目的，它们不做相同的事情:=要快得多，而LIKE可以解释通配符。用法=无论你在哪里，喜欢在哪里。
SELECT * FROM user WHERE login LIKE 'Test%';
-- Matches
-- TestUser1
-- TestUser2
-- TestU
-- Test
-- etc.
#3
29
In my small experience:
在我的小经验:
"=" for Exact Matches.
“=”精确匹配。
"LIKE" for Partial Matches.
“喜欢”部分匹配。
#4
11
There's a couple of other tricks that Postgres offers for string matching (if that happens to be your DB):
对于字符串匹配，Postgres还提供了一些其他的技巧(如果碰巧是您的DB):
ILIKE, which is a case insensitive LIKE match:
就像match一样不敏感的案例:
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
- Johnathon
- Johnny
- 约翰尼
#5
9
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会返回true;像abc这样的abc会返回false。在大多数情况下'='都是正确的，但在我最近的一个例子中却不是。
So while '=' is faster, LIKE might more explicitly state your intentions.
所以，虽然'='更快，比如可能更明确地陈述你的意图。
http://support.microsoft.com/kb/316626
http://support.microsoft.com/kb/316626
#6
6
For pattern matching use LIKE. For exact match =.
用于模式匹配使用LIKE。精确匹配=。
#7
6
LIKE is used for pattern matching and = is used for equality test (as defined by the COLLATION in use).
LIKE用于模式匹配，=用于相等性测试(由使用的排序规则定义)。
= can use indexes while LIKE queries 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.
=可以使用索引，而与查询类似，查询通常需要测试结果集中的每个记录，以过滤它(除非您正在使用全文搜索)，因此=具有更好的性能。
#8
3
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.
就像在shell中匹配通配符char[*， ?]，比如'%后缀' -给我所有以后缀结尾的东西。你不能用=来做，这取决于实际情况。
#9
3
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列”错误。