How to generate a UUIDv4 in MySQL?

by

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.

More Related Contents:

  1. How should I store GUID in MySQL tables?
  2. How to store uuid as number?
  3. UUID performance in MySQL?
  4. Using LIMIT within GROUP BY to get N results per group?
  5. How can I output MySQL query results in CSV format?
  6. Change limit for “Mysql Row size too large”
  7. Converting a date in MySQL from string field
  8. Quick easy way to migrate SQLite3 to MySQL? [closed]
  9. Ordering by specific field value first
  10. CHECK constraint in MySQL is not working
  11. How to change the CHARACTER SET (and COLLATION) throughout a database?
  12. Equivalent of Oracle’s RowID in MySQL
  13. How can I get around MySQL Errcode 13 with SELECT INTO OUTFILE?
  14. Error Code 1292 – Truncated incorrect DOUBLE value – Mysql
  15. Getting all parent rows in one SQL query
  16. How can I manipulate MySQL fulltext search relevance to make one field more ‘valuable’ than another?
  17. Creating functions in phpMyAdmin – Error: access denied you need the super privilege for this operation
  18. How to schedule a stored procedure in MySQL
  19. How can I enable the federated engine in mysql after installation?
  20. mysql date comparison with date_format
  21. Foreign key constraints: When to use ON UPDATE and ON DELETE
  22. MySQL column type “TIMESTAMP” implicitly includes “NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP”
  23. When to use a left outer join?
  24. MySQL: Compare BETWEEN time
  25. MySQL: how to do row-level security (like Oracle’s Virtual Private Database)?
  26. MySQL – Cannot insert NULL value in column, but I have a default value specified?
  27. uninitialized constant MysqlCompat::MysqlRes (using mms2r gem)
  28. Migrating data into Firebase from MySQL
  29. Inventory management with stock options
  30. Working with INTERVAL and CURDATE in MySQL

Leave a Comment