# Is there a difference between 'column LIKE \`string\`' and 'column = \`string\`'

**URL:** <https://forums.percona.com/t/is-there-a-difference-between-column-like-string-and-column-string/1468>\
**Category:** Other MySQL® Questions\
**Created:** [August 5, 2010, 2:19pm UTC](https://forums.percona.com/t/is-there-a-difference-between-column-like-string-and-column-string/1468 "2010-08-05T14:19:34Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![lucek](https://avatars.discourse-cdn.com/v4/letter/l/50afbb/32.png) [@lucek](https://forums.percona.com/u/lucek)\
**Post date:** [August 5, 2010, 2:19pm UTC](https://forums.percona.com/t/is-there-a-difference-between-column-like-string-and-column-string/1468/1 "2010-08-05T14:19:34Z")

</div>

I’ve seen some odd (for me) behavior of MySQL server some time ago (unfortunately I don’t remember server version and exact query). The query was quite simple and looked like:

SELECT col1, col2FROM table1WHERE col1 LIKE ? --no wildcards!

The query executed for a long time despite the fact that there was index on col1 (EXPLAIN showed that the index is ignored). I’ve changed accidentally the query to

SELECT col1, col2FROM table1WHERE col1 = ?

and everything started working smoothly (EXPLAIN showed that the query used index on col1). Collation on col1 was case insensitive.

This all leads me to question: what’s the difference between above queries?

---

<div class="post-metadata">

**Author:** ![carpii](https://avatars.discourse-cdn.com/v4/letter/c/b5ac83/32.png) [@carpii](https://forums.percona.com/u/carpii)\
**Post date:** [August 8, 2010, 10:49am UTC](https://forums.percona.com/t/is-there-a-difference-between-column-like-string-and-column-string/1468/2 "2010-08-08T10:49:29Z")

</div>

| [B]Quote:[/B] |
| 

Per the SQL standard, LIKE performs matching on a per-character basis, thus it can produce results different from the = comparison operator:

mysql\> SELECT ‘ä’ LIKE ‘ae’ COLLATE latin1\_german2\_ci;  
±----------------------------------------+  
| ‘ä’ LIKE ‘ae’ COLLATE latin1\_german2\_ci |  
±----------------------------------------+  
| 0 |  
±----------------------------------------+  
mysql\> SELECT ‘ä’ = ‘ae’ COLLATE latin1\_german2\_ci;  
±-------------------------------------+  
| ‘ä’ = ‘ae’ COLLATE latin1\_german2\_ci |  
±-------------------------------------+  
| 1 |  
±-------------------------------------+

In particular, trailing spaces are significant, which is not true for CHAR or VARCHAR comparisons performed with the = operator

 |

[http://dev.mysql.com/doc/refman/5.0/en/string-comparison-fun](http://dev.mysql.com/doc/refman/5.0/en/string-comparison-fun) ctions.html