Generating UUID v4 in MySQL

mysql_uuid_v4.md

Generating UUID v4 in MySQL

SELECT
  LOWER(
    CONCAT(
      # 1th and 2nd block are made of 6 random bytes
      HEX(RANDOM_BYTES(4)),
      '-',
      HEX(RANDOM_BYTES(2)),
      # 3th block will start with a 4 indicating the version, remaining is random
      '-4',
      SUBSTR(HEX(RANDOM_BYTES(2)), 2, 3),
      '-',
      # 4th block first nibble can only be 8, 9 A or B, remaining is random
      CONCAT(
        HEX(FLOOR(ASCII(RANDOM_BYTES(1)) / 64) + 8),
        SUBSTR(HEX(RANDOM_BYTES(2)), 2, 3)
      ),
      '-',
      # 5th block is made of 6 random bytes
      HEX(RANDOM_BYTES(6))
    )
  ) AS UUID;

• https://dev.mysql.com/doc/refman/5.7/en/encryption-functions.html#function_random-bytes
• https://stackoverflow.com/a/61062917/1461181
• https://www.freecodeformat.com/validate-uuid-guid.php

mysql_uuid_v4_stored_function.md

As quick reference, here's the function implementation of the above:

CREATE FUNCTION `UUIDV4` () RETURNS VARCHAR(255) NOT DETERMINISTIC NO SQL SQL SECURITY INVOKER
RETURN LOWER(
    CONCAT(
        # 1th and 2nd block are made of 6 random bytes
        HEX(RANDOM_BYTES(4)),
        '-',
        HEX(RANDOM_BYTES(2)),
        # 3th block will start with a 4 indicating the version, remaining is random
        '-4',
        SUBSTR(HEX(RANDOM_BYTES(2)), 2, 3),
        '-',
        # 4th block first nibble can only be 8, 9 A or B, remaining is random
        CONCAT(
            HEX(FLOOR(ASCII(RANDOM_BYTES(1)) / 64) + 8),
            SUBSTR(HEX(RANDOM_BYTES(2)), 2, 3)
        ),
        '-',
        # 5th block is made of 6 random bytes
        HEX(RANDOM_BYTES(6))
    )
);