How to generate UUIDv4 in MySQL?

Question

The MySQL UUID function returns the UUIDv1 GUID. I am looking for an easy way to generate random GUIDs (i.e. UUIDv4 ) in SQL.

Answer 1

I spent quite a bit of time finding a solution and came up with the following mysql, which generates random UUIDs (i.e. UUIDv4) using standard MySQL functions. I answer my question to share this in the hope that it will be useful.

 -- Change delimiter so that the function body doesn't end the function declaration DELIMITER // CREATE FUNCTION uuid_v4() RETURNS CHAR(36) 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 pseudorandom number generation used (MySQL RAND ) is not cryptographically protected and therefore has some bias that can increase collision risk.

Answer 2

In case you work with a database and you do not have rights to create functions, here is the same version as above, which works as an 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'), '-', CONCAT('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')));