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

MySQL & MariaDB Other MySQL® Questions
1

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?

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