MySQL generates UUID () for multiple rows

Question

I am 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 get the UUID:

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

Then, for all records that are already in the table, I generate a UUID (since each record in the database will receive a NULL value in this UUID field.

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

Now the thing is, I get very strange behavior by doing this; in some of my databases, each generated UUID is unique (as expected). However, in other databases, all generated UUIDs are identical (not similar, identical).

I suspect this is due to the MySQL query optimizer, as the behavior is incompatible between different database instances (all in MySQL 5.7.9). However, I do not know how to solve this problem.

So, MySQL guru, am I doing something wrong?

Answer 1

The solution in the link from @TimBiegelsen works.

I declared a function to generate UUIDs in accordance with the 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 ;