Vijay Boyapati Vijay Boyapati - 1 year ago 76
MySQL Question

Ensuring uniqueness on a varchar greater than 255 in MYSQL/InnoDB

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?

Answer Source

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:

ON foo
    NEW.hash = SHA1(NEW.url)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download