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