MySQL generate UUID() for multiple rows

Question

I'm trying to add UUIDs to some tables in some of my MySQL databases (MySQL 5.7.9). First of all, I start by adding a column to receive the UUIDs:

ALTER TABLE `mytable` ADD COLUMN `Uuid` BINARY(16) DEFAULT NULL;

Then, for all entries that are already in the table, I generate UUIDs (since every entry in the DB will have received a NULL value in it's UUID field.

UPDATE `mytable` SET Uuid= unhex(replace(uuid(),'-','')) WHERE Uuid IS NULL;

Now, the thing is that I get some very weird behaviour by doing this; on some of my databases, every UUID generated is unique (as expected). However, on other databases, all generated UUIDs are identical (not similar, identical).

I suspect that this is due to the MySQL query optimiser, since behaviour is not consistent between different database instances (all on MySQL 5.7.9). However, I have no clue how to solve this issue.

So, MySQL gurus, am I doing something wrong?

@TimBiegeleisen . . . This is an old bug report and the code to fix it should have been committed a decade ago. Do you know if the fix was never incorporated into the code base? — Gordon Linoff, Aug 11 '17 at 11:20
Possible duplicate of: https://stackoverflow.com/questions/9750536/mysql-uuid-duplication-bug — Tim Biegeleisen, Aug 11 '17 at 11:20
@GordonLinoff His query is calling UUID repeatedly with the same timestamp, at least this is my guess. Do you know of a workaround? — Tim Biegeleisen, Aug 11 '17 at 11:21

J. David Beutel · Answer 1 · 2019-02-20T02:33:18.920

I had the same problem, but the solution was a later answer to the linked question. I had a default character set of utf8mb4, and the theory is that it implicitly converts uuid()'s utf8 result to a constant string, before the sql executes. Converting the other parameters to utf8 avoids that:

UPDATE `mytable` SET Uuid= unhex(replace(uuid(), _utf8'-', _utf8'')) WHERE Uuid IS NULL;

The later answer links to the relevant (not a) bug report. This SO issue looks related.

score 3 · Answer 2 · answered Apr 13 '21 at 16:32

3

Best solution is:

UPDATE mytable SET Uuid= MID(UUID(),1,36) WHERE Uuid IS NULL;

answered Apr 13 '21 at 16:32

Jairo Bernal

41
2

This is brilliant. The only thing that worked – Vahan Mar 10 '22 at 03:29
Agree this is simple and works. An explanation would be useful. Why does selecting the first 36 characters randomize the result? – globalSchmidt Nov 23 '22 at 14:56

score 2 · Answer 3 · answered Aug 22 '17 at 12:47

The solution in the link from @TimBiegelsen works.

I declared a function to generate UUID according to v4 standard.

CREATE FUNCTION uuid_v4()
    RETURNS BINARY(16)
BEGIN
    -- Generate 8 2-byte strings that we will combine into a UUIDv4
    SET @h1 = LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0');
    SET @h2 = LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0');
    SET @h3 = LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0');
    SET @h6 = LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0');
    SET @h7 = LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0');
    SET @h8 = LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0');

    -- 4th section will start with a 4 indicating the version
    SET @h4 = CONCAT('4', LPAD(HEX(FLOOR(RAND() * 0x0fff)), 3, '0'));

    -- 5th section first half-byte can only be 8, 9 A or B
    SET @h5 = CONCAT(HEX(FLOOR(RAND() * 4 + 8)),
                LPAD(HEX(FLOOR(RAND() * 0x0fff)), 3, '0'));

    -- Build the complete UUID
    RETURN UNHEX(LOWER(CONCAT(@h1, @h2, @h3, @h4, @h5, @h6, @h7, @h8)));
END;;
DELIMITER ;

MySQL generate UUID() for multiple rows

3 Answers3

Linked