I managed to overlook an issue with creating a varchar column in an app I have been working on. I have basically got a normalised table, with a forien key to a table of values. In this case, its a set of HTML Document Titles, keyed off an autoincrement column called Title_ID. What I want to do is look up a title, and get a Title_ID back.
Great; I can do this with a stored function, which I did, and it worked. But it was slow. So I decided that I’d normalise these en-mass with one big INSERT statement to the normalised table (protected by a unique index constraint), and then store the resulting Title_ID.
There be dragons. As one title came through as “[Q] help me” it was duly inserted and given a Title_ID.
However, when a lower case “[q] help me” came through, it matched as a duplicate of the original and therefore was not inserted again. I then pulled the strings into a Perl hash, and of course, couldn’t find a key with “[q] help me”, only “[Q] help me”.
Turns out that the issue was my column definition. varchar(x) is not case sensative. varchar(x) binary is. The Unique index I had on here was doing its job and comparing values based upon the case in-sensative column – not its fault.
ALTER TABLE Titles CHANGE COLUMN `Title` `Title` VARCHAR(600) BINARY NULL DEFAULT NULL ;
And now I see my column as “`Title` varchar(600) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL“.