MySQL text index only checks 256 chars?

Question

MySQL's UNI key on a text column seems to only look at the first 255 characters to test for uniqueness. If I have two strings that begin with the same 255 characters they cannot both be added to this table.

Is there a way to overcome this?

The reason why I keyed this field is to ensure uniqueness regardless of query (those aren't under my control), rather than for performance.

Answer 1

You can not index text fields in MySQL with key length more than 255 symbols before MySQL 4.1.2 / 1000 bytes (767 for InnoDB) for higher versions - and you can not overcome that natively.

The easiest solution is to evaluate md5 hash and store it in another column, then create unique index by it and create a trigger before insert & before update which will insert md5 automatically for you. If you're running MySQL server 4.x - then you'll need to handle that by yourself (since no trigger will be available)