mysqluuid

How to generate a UUIDv4 in MySQL?


MySQL's UUID function returns a UUIDv1 GUID. I'm looking for an easy way to generate random GUIDs (i.e. UUIDv4) in SQL.


Solution

  • 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.