LIKE query does not work after I change CHARACTER SET of a MySQL Table to UTF-8

Question

I have MySQL database with 30 rows in customer_customer table. Out of which 5 record has adm_name as Mike.

mysql> select id from customer_customer where adm_name like '%mike%';
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
5 rows in set (0.00 sec)

Now I have changed character set of my table to utf8

mysql> ALTER TABLE customer_customer CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 30 rows affected (0.03 sec)
Records: 30  Duplicates: 0  Warnings: 0

Again if I run same like query, then MySQL is not returning me any records.

mysql> select id from customer_customer where adm_name like '%mike%';
Empty set (0.00 sec)

I am not able to understand this behavior. Is there anyone who has came across this situation? Am I doing anything wrong?

By looking at the similar post http://stackoverflow.com/questions/2150256/how-to-change-all-the-tables-in-my-database-to-utf8-character-set, if I change COLLATE to utf8_unicode_ci then the query works fine. — Vijayendra Bapte, Sep 28 '11 at 07:28

score 0 · Accepted Answer · answered Sep 28 '11 at 07:43

You changed collation to binary, in this case comparison is done byte by byte rather than character by character. Here it is a good example and explanation for the BINARY operator.

mysql> SELECT 'a' = 'A';
        -> 1
mysql> SELECT BINARY 'a' = 'A';
        -> 0

LIKE query does not work after I change CHARACTER SET of a MySQL Table to UTF-8

1 Answers1