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

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?


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 ctions.html