Search code examples
mysqlinnodb

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?

Solution

  • For MySQL 5.7 or newer, see Andre Dalcher's answer for a nicer way using generated columns.

    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)