The Passionate Craftsman

Ruby, PHP, MySql, Software engineering and more.

Friday, 12 March 2010

Case sensitive MySql search

If you want to make a table case sensitive or you just discovered that you are searching some text and the result comprises only a result matching only case sensitive pattern, here is an explanation that might help you.

The key point is collation. Collations names ending in _ci are case insensitive. Collections ending with _cs and _bin are case sensitive. You can change a collection of a table with the following query:

ALTER TABLE table_name COLLATE collation_name

It is possible to make a single column case sensitive:

`title` varchar(50) collate utf8_bin

That row has been taken from a CREATE TABLE statement. With that 'collate utf8_bin' text in you table definition, your serach will be case sensitive!

Userful links:

Farhan Mushraqi blog - Case sensitive SQL