Difference between LIKE and = in MYSQL?

Solution 1:

= 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']

Solution 2:

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?)

Solution 3:

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