2

I have a table with a text column that I need to be unique. Because I can't put a unique constraint on the column in MySQL, I want to know if the following approach would guarantee uniqueness in the column:

  1. Create a varchar column for holding the hash of the unique text column. Put a plain INDEX on it.
  2. Start transaction
  3. Use SELECT FOR UPDATE to see if hash exists
  4. a) Use a subsequent INSERT if hash doesn't exist

    b) If hash exists, SELECT by unique text to see if it exists. If unique text does not exist, do a subsequent INSERT, otherwise do 'UPDATE'

  5. End transaction

If I use the above approach for all inserts in that table, can that guarantee uniqueness?

0

Yes, that should work. However, consider either of these one-liners:

INSERT IGNORE ...

INSERT ... ON DUPLICATE KEY UPDATE ...

If you are using AUTO_INCREMENT, be cautious about 'burning' ids.

Links:

| improve this answer | |
  • Forgot to mention my table actually doesn't have any unique constraints or primary keys so those INSERT solutions may not work for me. DB-design considerations aside, I just want to ensure my proposed solution works. – user2345093 Nov 1 '16 at 22:09
  • Then how does your Step 2 SELECT work??? – Rick James Nov 1 '16 at 22:15
  • Sorry, I misspoke about my approach - I've updated the question. Basically I have a varchar column that holds a hash of the unique text which has a INDEX that's used for the SELECT FOR UPDATE. Would that work? – user2345093 Nov 1 '16 at 22:53
  • IGNORE and IODKU need a UNIQUE key to hang their hats on - to know when to "ignore" or update instead of insert. Yes, a "hash" of a long text column is viable. (I like MD5().) – Rick James Nov 1 '16 at 23:01
  • Just to confirm, my updated overall approach in the question should work, right? – user2345093 Nov 1 '16 at 23:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.