I have a table which contains URLs, some of which are longer than 255 characters. I want to impose a uniqueness constraint on the URL column, but MySQL isn't letting me create a key on the URL. I'm using an InnoDB/UTF8 table. From what I understand it's using multiple bytes per character with a limit of 766 bytes for the key (in InnoDB).
What is an elegant way of keeping the rows unique based on URL?
You could use a SHA1 hash of the url as the unique key. There is a chance that two urls have the same hash, but the probability of that is so ridiculously small, that for practical purposes this method should work fine.
You could also set up a trigger so the
hash column is computed automatically whenever you INSERT:
CREATE TRIGGER mytrigger BEFORE INSERT ON foo FOR EACH ROW SET NEW.hash = SHA1(NEW.url)