0

Are there solutions to maintain a VARCHAR(65535) field unique per ID in MySQL?

Nick
  • 331
  • 3
  • 14

1 Answers1

1

I would use a hashing function on field's value and store it in a separate unique field on the same table.

So your table would be something like this:

create table my_table (
   id integer not null primary key,
   text_content varchar(65535) not null,
   text_hash varchar(128) not null unique
);

When inserting on that table, you will compute sha256 hash for text_content field and store it in text_hash field. That way you can be pretty sure text_content values are unique on your table.

If you like DB SIDE programming, you can put this logic on a trigger provided MySQL supports them.

Pablo Santa Cruz
  • 176,835
  • 32
  • 241
  • 292
  • how do I deal with collisions? – Nick Aug 20 '16 at 15:45
  • Collisions would be **extremely** rare. Don't think you will find them. That's what those kind of algorithms were designed for. Check this question out: http://stackoverflow.com/questions/4014090/is-it-safe-to-ignore-the-possibility-of-sha-collisions-in-practice – Pablo Santa Cruz Aug 20 '16 at 15:46
  • This could have been solved 100% bulletproof with a binary comparison in case of a hash collision + an autoincremented secondary field for key uniqueness in case of collisions. Isn't there any database solution that would perform such a task? – Nick Aug 20 '16 at 15:55
  • 1
    That could also be a generated column using the sha2() function. – Paul Spiegel Aug 20 '16 at 22:38
  • @PaulSpiegel, what do you mean? 2 hash columns with different algorithms? from what I see, `sha256` belongs to `sha2` family. What would be a secondary choice to mate with `sha256`? – Nick Aug 21 '16 at 13:23
  • 1
    @Nick - my comment didn't address your collision problem. I'm just suggesting an improvement to the answer, using [MySQL 5.7 generated columns](https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html). Something like: `text_hash varchar(128) as sha2(text_content, 256) unique`. This way you keep everything within the table definition. No triggers or additional code in your application required. – Paul Spiegel Aug 21 '16 at 13:41