LIKE query does not work after I change CHARACTER SET of a MySQL Table to UTF-8
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 o开发者_开发知识库f 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?
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
精彩评论