MYSQL Creating several UUIDs at one single time

Posted on by

I recently ran into the problem that I had too add two rows to a database that uses a UUID (in this case converted to a binary) as a key.
The problem I had was that running two insert statements after each other (just separated by “;”) then the same id was generated.
A workaround I have found is to sleep for a second between the two statements.

Examples (using SELECT instead of INSERT):
Getting the same id twice

SELECT UNHEX(REPLACE(UUID(),'-','')) AS uuid1,  UNHEX(REPLACE(UUID(),'-','')) AS uuid2;
-- Shows: "849392548f1311df91b70019dbd264f1"	"849392908f1311df91b70019dbd264f1"

Adding a sleep to get different ids

SELECT UNHEX(REPLACE(UUID(),'-','')) AS uuid1, SLEEP(1), UNHEX(REPLACE(UUID(),'-','')) AS uuid2
-- Shows: "3901c1928f1511df91b70019dbd264f1"	"0"	"399a5b148f1511df91b70019dbd264f1"

And another one using INSERTS

INSERT INTO testTable (id) VALUES (UNHEX(REPLACE(UUID(),'-','')));
SLEEP(1);
INSERT INTO testTable (id) VALUES (UNHEX(REPLACE(UUID(),'-','')));