mysql表达式为随机的uuid4?

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

评论
  • taut
    taut 回复

    这将插入版本号为4的随机字符串,不带破折号。 为了简洁起见,它仅使用25%的密钥空间, 或120位。

    -- 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 we call it five times to obtain 265 ( > 256 ) unguessable bits.
    -- We wind up needing to do this thrice (15 calls) to gen a guid.
    -- 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(concat(rand(), rand(), rand(), rand(), rand()), 256), 1, 12),
        '4', substr(sha2(concat(rand(), rand(), rand(), rand(), rand()), 256), 1,  3),
        '8', substr(sha2(concat(rand(), rand(), rand(), rand(), rand()), 256), 1, 15)
      )
    );