MySQL

How to Make a Long Text Column Unique

You may have known that unique index on a string-typed column has limitation on prefix length, which can’t guarantee the uniqueness if the length of string was larger than the limit.

MySQL :: MySQL 5.7 Reference Manual :: 14.8.1.7 Limits on InnoDB Tables
The index key prefix length limit is 767 bytes for InnoDB tables that use the REDUNDANT or COMPACT row format. For example, you might hit this limit with a column prefix index of more than 255 characters on a TEXT or VARCHAR column, assuming a utf8mb3 character set and the maximum of 3 bytes for each character.

Attempting to use an index key prefix length that exceeds the limit returns an error. To avoid such errors in replication configurations, avoid enabling innodb_large_prefix on the master if it cannot also be enabled on slaves.

The limits that apply to index key prefixes also apply to full-column index keys.
Therefore, we should find another way to workaround it.

My solution is to create another varchar column that is derived from the hash value (e.g. MD5 or SHA) of the long string. The hash values are usually 32 or 40 chars in length. After that, you can create an unique index on this new column and keep the long string column unique indirectly and implicitly.

The possibility of hash value collisions on this column is very small and can be almost ignored in our case.

Leave a Reply

Your email address will not be published. Required fields are marked *