How to generate a UUIDv4 in MySQL?

Question

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

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