Today at work I helped an intern with an interesting problem I would like to share.
he was doing this kind of query on a MySQL server :

SELECT description FROM service WHERE description LIKE '%cloud%';

It returned these lines :

cloud customer 1
cloud customer 2

but does not return these two lines he was expecting :

new Cloud infra
Cloud customer 2

LIKE should be case insensitive … What was wrong ?

Just check the create table :

CREATE TABLE `service` (
 `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
 `description` varchar(255) CHARACTER SET utf8 utf8_bin NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB;

So the field ‘description’ is considered a binary string (noticed the ‘_bin’ in utf8_bin ? That’s it).
Take a look at the documentation :

For binary strings (BINARY, VARBINARY, BLOB), comparisons use the numeric values of the bytes in the operands; this means that for alphabetic characters, comparisons will be case sensitive.

http://dev.mysql.com/doc/refman/5.5/en/case-sensitivity.html

So that’s why the query did not return what was expected .. Change the collation to utf8 DEFAULT (or utf8_general_ci) :

ALTER TABLE `service` CHANGE `description` `description` VARCHAR(255) CHARSET utf8 COLLATE utf8_general_ci NULL;

And now it works.