mysql expression for random uuid4?

Question

Mysql offers a UUID() function, which returns an rfc 4122 version 1 guid. This is an easily guessed timestamp + node_id bit string.

How may we insert randomized version 4 guids?

(Defining a new function requires permissions and is out-of-scope. Ben Johnson offers an expression that is very nice but a little verbose.)

Bluet

Answer 1

This inserts a version-4 random string, without dashes. In the interest of conciseness it uses a slightly reduced portion of the key space, just 120 bits. (So 8-bits are predictable, they're constant.)

-- Produces version 4 guids for mysql, as its UUID() only offers version 1.
--
-- See https://en.wikipedia.org/wiki/Universally_unique_identifier#Version_4_(random)
-- We consider variant 1 only, ignoring the Microsoft proprietary variant 2.
-- Version 1 is predictable timestamp.
-- Version 4 is 122 random bits + 6 constant bits.
--
-- The nil guid comes out like this:
-- UUID('00000000-0000-4000-8000-000000000000')  # 8-4-4-4-12
-- The nybble '4' is constant version.
-- The nybble '8' has hi bit set, next bit cleared, plus two wasted bits.
-- We deliberately choose to emit just 120 random bits, for simplicity.
-- The RAND() function returns about 53 bits of entropy in the mantissa,
-- so for 15 nybbles we call it twice to obtain 106 ( > 60 ) unguessable bits.
-- The standard spelling of a guid, with four '-' dashes, is 36 characters.
-- We emit 32 hex characters, sans dashes.

INSERT INTO guid_test (guid) VALUES (
  concat(substr(sha2(rand(), 256),                 1, 12),
    '4', substr(sha2(rand(), 256),                 1,  3),
    '8', substr(sha2(concat(rand(), rand()), 256), 1, 15)
  )
);

Bluet