MySQL case insensitivity on varchar fields

I created a MySQL table like the following:

CREATE TABLE `links` (
`id` int(11) NOT NULL auto_increment,
`short_id` varchar(255) NOT NULL,
`url` text NOT NULL,
`user_id` int(11) default NULL,
`created_at` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
CONSTRAINT unique_key_1 UNIQUE (`short_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This structure caused an error for me when it came to inserting an upper-case ‘A’ in addition to the lower-case ‘a’ into the short_id field. I removed the unique constraint for the short_id field and this allowed me to create a row with ‘A’ as the short_id.

Unfortunately

SELECT * FROM links WHERE short_id='A';

gave me both rows (‘a’ and ‘A’), with the ‘a’ row being first. To fix this I had to set the short_id field to be BINARY. When I’d done that I realised I could make the short_id field unique again.

To create the table properly I now use the following query:

CREATE TABLE IF NOT EXISTS `links` (
`id` int(11) NOT NULL auto_increment,
`short_id` varchar(255) character set utf8 collate utf8_bin NOT NULL,
`url` text NOT NULL,
`user_id` int(11) default NULL,
`created_at` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`)
CONSTRAINT unique_key_1 UNIQUE (`short_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

You can just add the BINARY keyword after the field data type if you like, e.g.:

`short_id` varchar(255) BINARY NOT NULL

Hope this helps someone else out there. 🙂