blocks|key|351048|text|对于模式匹配，请使用LIKE。精确匹配=。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|351049|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

For pattern matching use LIKE. For exact match =.

blocks|key|351068|text|就像匹配像通配符一样使用*，？在外壳上|type|unstyled|depth|inlineStyleRanges|entityRanges|data|351069|LIKE+'%25+suffix‘-给我所有以后缀结尾的内容。使用=不能做到这一点|351070|实际上要视情况而定。|351071|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

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.

blocks|key|3130204|text|在我的小经验中：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3130205|"=“表示完全匹配。|3130206|"LIKE“表示部分匹配。|3130207|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

In my small experience:

"=" for Exact Matches.

"LIKE" for Partial Matches.

blocks|key|351117|text|LIKE和相等运算符有不同的目的，它们做的不是同一件事：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|351118|=要快得多，而LIKE可以解释通配符。只要能用就用=，必须用就用LIKE。|351119|SELECT+*+FROM+user+WHERE+login+LIKE+'Test%25';|code-block|syntax|javascript|351120|匹配示例：|351121|351122|+TestUser1|blockquote|351123|351124|TestUser2|351125|TestU|351126|测试|351127|entityMap^0|0|4|0|0|1|7|4|P|1|W|4|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|11|8|@$9|12|A|13|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|14|8|@$9|15|A|16|B|C]|$9|17|A|18|B|C]|$9|19|A|1A|B|C]|$9|1B|A|1C|B|C]]|D|@]|E|$]]|$1|H|3|I|5|J|7|1D|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|1E|8|@]|D|@]|E|$]]|$1|O|3|-4|5|6|7|1F|8|@]|D|@]|E|$]]|$1|P|3|Q|5|R|7|1G|8|@]|D|@]|E|$]]|$1|S|3|-4|5|6|7|1H|8|@]|D|@]|E|$]]|$1|T|3|U|5|6|7|1I|8|@]|D|@]|E|$]]|$1|V|3|W|5|6|7|1J|8|@]|D|@]|E|$]]|$1|X|3|Y|5|6|7|1K|8|@]|D|@]|E|$]]|$1|Z|3|-4|5|6|7|1L|8|@]|D|@]|E|$]]]|10|$]]

<code>LIKE</code> and the equality operator have different purposes, they don't do the same thing: 
<code>=</code> is much faster, whereas <code>LIKE</code> can interpret wildcards. Use <code>=</code> wherever you can and <code>LIKE</code> wherever you must.

<pre><code>SELECT * FROM user WHERE login LIKE 'Test%';
</code></pre>

Sample matches: 

<blockquote>
 TestUser1 
 TestUser2 
 TestU 
 Test
</blockquote>

blocks|key|3130271|text|LIKE用于模式匹配，=用于相等性测试(由使用中的COLLATION定义)。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3130272|=可以使用索引，而LIKE查询通常需要测试结果集中的每一条记录以将其过滤掉(除非您正在使用全文搜索)，因此=具有更好的性能。|3130273|entityMap^0|0|4|B|1|P|9|0|0|1|9|4|1H|1|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]|$9|M|A|N|B|C]|$9|O|A|P|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|Q|8|@$9|R|A|S|B|C]|$9|T|A|U|B|C]|$9|V|A|W|B|C]]|D|@]|E|$]]|$1|H|3|-4|5|6|7|X|8|@]|D|@]|E|$]]]|I|$]]

<code>LIKE</code> is used for pattern matching and <code>=</code> is used for equality test (as defined by the <code>COLLATION</code> in use). 

<code>=</code> can use indexes while <code>LIKE</code> queries usually require testing every single record in the result set to filter it out (unless you are using full text search) so <code>=</code> has better performance.

blocks|key|351140|text|要查看性能差异，请尝试执行以下操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|351141|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|code-block|syntax|javascript|351142|用'=‘比较字符串要快得多。|351143|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

To see the performance difference, try this:

<pre><code>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
</code></pre>

Comparing strings with '=' is much faster.

blocks|key|351153|text|Postgres还为字符串匹配提供了一些其他技巧(如果这恰好是您的数据库)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|351154|ILIKE，它是不区分大小写的，比如match：|351155|select+*+from+people+where+name+ilike+'JOHN'|code-block|syntax|javascript|351156|匹配项：|351157|351158|John|unordered-list-item|351159|john|351160|JOHN|351161|351162|如果你想变得疯狂，你可以使用正则表达式：|351163|select+*+from+people+where+name+~+'John.*'|351164|351165|351166|351167|Johnathon|351168|Johnny|351169|351170|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|17|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|18|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|19|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|1A|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|1B|8|@]|9|@]|A|$]]|$1|L|3|M|5|N|7|1C|8|@]|9|@]|A|$]]|$1|O|3|P|5|N|7|1D|8|@]|9|@]|A|$]]|$1|Q|3|R|5|N|7|1E|8|@]|9|@]|A|$]]|$1|S|3|-4|5|6|7|1F|8|@]|9|@]|A|$]]|$1|T|3|U|5|6|7|1G|8|@]|9|@]|A|$]]|$1|V|3|W|5|F|7|1H|8|@]|9|@]|A|$G|H]]|$1|X|3|J|5|6|7|1I|8|@]|9|@]|A|$]]|$1|Y|3|-4|5|6|7|1J|8|@]|9|@]|A|$]]|$1|Z|3|M|5|N|7|1K|8|@]|9|@]|A|$]]|$1|10|3|11|5|N|7|1L|8|@]|9|@]|A|$]]|$1|12|3|13|5|N|7|1M|8|@]|9|@]|A|$]]|$1|14|3|-4|5|6|7|1N|8|@]|9|@]|A|$]]|$1|15|3|-4|5|6|7|1O|8|@]|9|@]|A|$]]]|16|$]]

There's a couple of other tricks that Postgres offers for string matching (if that happens to be your DB):

ILIKE, which is a case insensitive LIKE match:

<pre><code>select * from people where name ilike 'JOHN'
</code></pre>

Matches:

<ul>
<li>John</li>
<li>john</li>
<li>JOHN</li>
</ul>

And if you want to get really mad you can use regular expressions:

<pre><code>select * from people where name ~ 'John.*'
</code></pre>

Matches:

<ul>
<li>John</li>
<li>Johnathon</li>
<li>Johnny</li>
</ul>

blocks|key|3343191|text|使用"like“还有另一个原因，即使性能较慢:字符值在比较时会隐式转换为整数，因此：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3343192|declare+@transid+varchar(15)|3343193|如果@transid+!=+0|3343194|将给你一个“转换的varchar值'123456789012345‘溢出一个整型列”错误。|3343195|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

There is another reason for using "like" even if the performance is slower: Character values are implicitly converted to integer when compared, so:

declare @transid varchar(15)

if @transid != 0

will give you a "The conversion of the varchar value '123456789012345' overflowed an int column" error.

blocks|key|350176|text|作为提示，“=”运算符将在Transact-SQL中用空格填充字符串。所以'abc'+=+'abc+'将返回true；'abc'+LIKE+'abc+'将返回false。在大多数情况下，'=‘将是正确的，但在我最近的一个例子中，它是不正确的。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|350177|因此，虽然'=‘更快，但LIKE可能会更明确地说明您的意图。|350178|http://support.microsoft.com/kb/316626|350179|entityMap|0|LINK|mutability|MUTABLE|url^0|11|E|1N|H|0|0|0|12|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|V|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|W|8|@]|D|@$9|X|A|Y|1|Z]]|E|$]]|$1|J|3|-4|5|6|7|10|8|@]|D|@]|E|$]]]|K|$L|$5|M|N|O|E|$P|I]]]]

Just as a heads up, the '=' operator will pad strings with spaces in Transact-SQL. So <code>'abc' = 'abc '</code> will return true; <code>'abc' LIKE 'abc '</code> will return false. In most cases '=' will be correct, but in a recent case of mine it was not.

So while '=' is faster, LIKE might more explicitly state your intentions.

<a href="http://support.microsoft.com/kb/316626">http://support.microsoft.com/kb/316626</a>

There's the (almost religious) discussion, if you should use LIKE or '=' to compare strings in SQL statements.

<ul>
<li>Are there reasons to use LIKE?</li>
<li>Are there reasons to use '='?</li>
<li>Performance? Readability? </li>
</ul>

Use '=' or LIKE to compare strings in SQL?

数据库

如果您应该使用LIKE或'=‘来比较SQL语句中的字符串，这里有一个(几乎是宗教式的)讨论。有使用'='?Performance?的理由吗？有使用LIKE的理由吗可读性-

问使用'=‘或LIKE来比较SQL中的字符串？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用'=‘或LIKE来比较SQL中的字符串？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用'=‘或LIKE来比较SQL中的字符串？
EN