Search code examples
mysqlinnodbunique-constraintunique-index

Mysql 5.7.13 innodb unique column varchar(500)

I need to maintain a unique index on a varchar(500) column of a table of my Mysql 5.7.13 database.

The charset is set to UTF8 so, as far as I know, the limit in characters is 255 since I am using innodb engine. I know also that exists the option innodb_large_prefix that should extend the dimension up to 3072 bytes. I tried to configure my db to support this feature, but it does not work. I tried to insert two identical 500 characters long rows with the last character different, but I get a unique constraint error since, as I assume, the uniqueness check is performed on the first 255 characters.

Am I missing something? Have I misunderstood the purpose of this option?

P.S I enabled the innodb_file_format = Barracuda as pointed in mysql documentation.

Thanks for the help. Simone

Solution

  • I solved my problem.

    It turned out that the innodb engine were configured fine. The problem is that I created the table before enabling the innodb_large_prefix option, setting the unique index on the varchar(500).

    After deleting and re-creating the index on the varchar(500) field, the unique constraint start to work as expected.