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
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
There be dragons. As one title came through as “[Q] help me” it was duly inserted and given a
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“.