How to generate a UUIDv4 in MySQL?

Solution 1:

I've spent quite some time looking for a solution and came up with the following mysql function that generates a random UUID (i.e. UUIDv4) using standard MySQL functions. I'm answering my own question to share that in the hope that it'll be useful.

-- Change delimiter so that the function body doesn't end the function declaration
DELIMITER //

CREATE FUNCTION uuid_v4()
    RETURNS CHAR(36) NO SQL
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 LOWER(CONCAT(
        @h1, @h2, '-', @h3, '-', @h4, '-', @h5, '-', @h6, @h7, @h8
    ));
END
//
-- Switch back the delimiter
DELIMITER ;

Note: The pseudo-random number generation used (MySQL's RAND) is not cryptographically secure and thus has some bias which can increase the collision risk.

Solution 2:

In the off chance you're working with a DB and don't have perms to create functions, here's the same version as above that works just as a SQL expression:

SELECT LOWER(CONCAT(
    LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0'), 
    LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0'), '-',
    LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0'), '-', 
    '4',
    LPAD(HEX(FLOOR(RAND() * 0x0fff)), 3, '0'), '-', 
    HEX(FLOOR(RAND() * 4 + 8)), 
    LPAD(HEX(FLOOR(RAND() * 0x0fff)), 3, '0'), '-', 
    LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0'),
    LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0'),
    LPAD(HEX(FLOOR(RAND() * 0xffff)), 4, '0')));

Solution 3:

Both existing answers relies on MySQL RAND() function:

RAND() is not meant to be a perfect random generator. It is a fast way to generate random numbers on demand that is portable between platforms for the same MySQL version.

In the practice, this mean that the generated UUID using this function might (and will) be biased, and collisions can occur more frequently then expected.

Solution

It's possible to generate safe UUID V4 on MySQL side using random_bytes() function:

This function returns a binary string of len random bytes generated using the random number generator of the SSL library.

So we can update the function to:

CREATE FUNCTION uuid_v4s()
    RETURNS CHAR(36)
BEGIN
    -- 1th and 2nd block are made of 6 random bytes
    SET @h1 = HEX(RANDOM_BYTES(4));
    SET @h2 = HEX(RANDOM_BYTES(2));

    -- 3th block will start with a 4 indicating the version, remaining is random
    SET @h3 = SUBSTR(HEX(RANDOM_BYTES(2)), 2, 3);

    -- 4th block first nibble can only be 8, 9 A or B, remaining is random
    SET @h4 = CONCAT(HEX(FLOOR(ASCII(RANDOM_BYTES(1)) / 64)+8),
                SUBSTR(HEX(RANDOM_BYTES(2)), 2, 3));

    -- 5th block is made of 6 random bytes
    SET @h5 = HEX(RANDOM_BYTES(6));

    -- Build the complete UUID
    RETURN LOWER(CONCAT(
        @h1, '-', @h2, '-4', @h3, '-', @h4, '-', @h5
    ));
END

This should generate UUID V4 random enough to don't care about collisions.

NOTE: Unfortunately MariaDB doesn't support RANDOM_BYTES() (See https://mariadb.com/kb/en/function-differences-between-mariadb-105-and-mysql-80/#miscellaneous)

Test

I've created following test scenario: Insert random UUID v4 as primary key for a table until 40.000.000 rows are created. When a collision is found, the row is updated incrementing collisions column:

INSERT INTO test (uuid) VALUES (uuid_v4()) ON DUPLICATE KEY UPDATE collisions=collisions+1;

The sum of collisions after 40 million rows with each function is:

+----------+----------------+
| RAND()   | RANDOM_BYTES() |
+----------+----------------+
|       55 |              0 |
+----------+----------------+

The number collisions in both scenarios tends to increase as number of rows grows.