MySql and case sensitive in where clause

By default the content of a VARCHAR field in MySql is not case sensitive in string searches.
This official article gives some solutions if you need distinguish between "hello" and "Hello".

In my case I  have a table with a string field computed starting from the auto-incremental numeric id.
This alias (unique) is stored for performance reasons and uses a set of 62 symbols (A-Za-z0-9).

It is a base-62 representation of the real id so of course, the alias "xa" is different from "xA".

My first table definition was:


CREATE TABLE IF NOT EXISTS `boxes` (
  `idboxes` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `creation` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `alias` varchar(45) NOT NULL,
  `language` varchar(45) DEFAULT NULL,
  `lastvisit` timestamp NULL DEFAULT NULL,
  `readonly` int(11) DEFAULT NULL,
  `password` varchar(60) DEFAULT '',
  PRIMARY KEY (`idboxes`),
  KEY `aliasIndex` (`alias`)
)


That with this query returned the following records:


select * from boxes where alias='xi'



'320', '2011-11-25 14:22:26', 'xI', 'text/x-java', NULL, '0', '123'

'317', '2011-11-16 17:19:30', 'xi', 'text/plain', NULL, '0', 'pwd'

Since alias must be an unique id this is a BIG problem!
The solution is to set the field collation to one case sensitive charset (or to a binary). You can identify them from the suffix "_cs" (or _bi).

In my case cp1251_general_cs is enough good because it contains all my 62 symbols set.
So the table can be changed in this way:



CREATE TABLE IF NOT EXISTS `boxes` (
  `idboxes` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `creation` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `alias` varchar(45) CHARACTER SET cp1251 COLLATE cp1251_general_cs NOT NULL,
  `language` varchar(45) DEFAULT NULL,
  `lastvisit` timestamp NULL DEFAULT NULL,
  `readonly` int(11) DEFAULT NULL,
  `password` varchar(60) DEFAULT '',
  PRIMARY KEY (`idboxes`),
  KEY `aliasIndex` (`alias`)
)


The same change have to be applied to the password field!