How can I guarantee string uniqueness in MySQL without using a UNIQUE constraint?

Question

I have fairly simple requirements, but I'm not sure how to implement them:

• I have multiple parallel threads executing the same request
• The query provides the value "string" - if it exists in the table, the query should return the identifier of the corresponding row, if the query should not insert the value "string" and return the last inserted id
• The row column is (and should be) a text column (it is larger than varchar 255), so I cannot set it as unique - uniqueness must be done through an access mechanism
• The request must be in a stored procedure format (which does not support table locking in MySQL)

How can I guarantee that the "string" is unique? How can I prevent other threads from writing to the table after another thread has read it and has not found the corresponding string element?

Thanks for any advice ..

Answer 1

If you are sure that you cannot use the DB constraint, then use the UNIQUE index in another field, where you store a good cryptic hash of the entire string. I guess MD5 or SHA1 should be enough. Several source control systems (e.g. Git, Mercurial, Monotone, etc.) rely on an extremely low chance of hash collisions.

Answer 2

/, , .

, "" , :

select distinct string from table where ...

, Thread , HashMap , Thread , . , HashMap, . HashMap, , .

Answer 3

, , , . .

Answer 4

Instead of using lock tables, since it is not supported in stored procedures, you can use

START TRANSACTION;

    SELECT * FROM ....

COMMIT;

You just need to make sure that you are using a transaction-safe storage engine, for example InnoDB.