What's the difference between

SELECT foo FROM bar WHERE foobar='$foo'

AND

SELECT foo FROM bar WHERE foobar LIKE'$foo'

• mysql

LIKE can do wildcard matching:

SELECT foo FROM bar WHERE foobar LIKE "Foo%"

If you don't need pattern matching, then use = instead of LIKE. It's faster and more secure. (You are using parameterized queries, right?)

= in SQL does exact matching.

LIKE does wildcard matching, using '%' as the multi-character match symbol and '_' as the single-character match symbol. '\' is the default escape character.

foobar = '$foo' and foobar LIKE '$foo' will behave the same, because neither string contains a wildcard.

foobar LIKE '%foo' will match anything ending in 'foo'.

LIKE also has an ESCAPE clause so you can set an escape character. This will let you match literal '%' or '_' within the string. You can also do NOT LIKE.

The MySQL site has documentation on the LIKE operator. The syntax is

expression [NOT] LIKE pattern [ESCAPE 'escape']

Please bear in mind as well that MySQL will do castings dependent upon the situation: LIKE will perform string cast, whereas = will perform int cast. Considering the situation of:

    (int)   (vchar2)
id  field1  field2
1   1       1
2   1       1,2

SELECT * FROM test AS a LEFT JOIN test AS b ON a.field1 LIKE b.field2

will produce

id  field1  field2  id  field1  field2
1   1       1       1   1       1
2   1       1,2     1   1       1

whereas

SELECT * FROM test AS a LEFT JOIN test AS b ON a.field1 = b.field2

will produce

id  field1  field2  id  field1  field2
1   1       1       1   1       1
1   1       1       2   1       1,2
2   1       1,2     1   1       1
2   1       1,2     2   1       1,2

According to the MYSQL Reference page, trailing spaces are significant in LIKE but not =, and you can use wildcards, % for any characters, and _ for exactly one character.

I think in term of speed = is faster than LIKE. As stated, = does an exact match and LIKE can use a wildcard if needed.

I always use = sign whenever I know the values of something. For example

select * from state where state='PA'

Then for likes I use things like:

select * from person where first_name like 'blah%' and last_name like 'blah%'

If you use Oracle Developers Tool, you can test it with Explain to determine the impact on the database.

The end result will be the same, but the query engine uses different logic to get to the answer. Generally, LIKE queries burn more cycles than "=" queries. But when no wildcard character is supplied, I'm not certain how the optimizer may treat that.

With the example in your question there is no difference.

But, like Jesse said you can do wildcard matching

SELECT foo FROM bar WHERE foobar LIKE "Foo%"

SELECT foo FROM bar WHERE foobar NOT LIKE "%Foo%"

More info:

http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html

A little bit og google doesn't hurt...

A WHERE clause with equal sign (=) works fine if we want to do an exact match. But there may be a requirement where we want to filter out all the results where 'foobar' should contain "foo". This can be handled using SQL LIKE clause alongwith WHERE clause.

If SQL LIKE clause is used along with % characters then it will work like a wildcard.

SELECT foo FROM bar WHERE foobar LIKE'$foo%'

Without a % character LIKE clause is very similar to equal sign alongwith WHERE clause.

In your example, they are semantically equal and should return the same output.

However, LIKE will give you the ability of pattern matching with wildcards.

You should also note that = might give you a performance boost on some systems, so if you are for instance, searching for an exakt number, = would be the prefered method.

Looks very much like taken out from a PHP script. The intention was to pattern-match the contents of variable $foo against the foo database field, but I bet it was supposed to be written in double quotes, so the contents of $foo would be fed into the query.

As you put it, there is NO difference.

It could potentially be slower but I bet MySQL realises there are no wildcard characters in the search string, so it will not do LIKE patter-matching after all, so really, no difference.

In my case I find Like being faster than =

• Like fetched a number of rows in 0.203 secs the first time then 0.140 secs

• = returns fetched the same rows in 0.156 secs constantly

Take your choice

I found an important difference between LIKE and equal sign = !

Example: I have a table with a field "ID" (type: int(20) ) and a record that contains the value "123456789"

If I do:

SELECT ID FROM example WHERE ID = '123456789-100'

Record with ID = '123456789' is found (is an incorrect result)

If I do:

SELECT ID FROM example WHERE ID LIKE '123456789-100'

No record is found (this is correct)

So, at least for INTEGER-fields it seems an important difference...