mysql表达式为随机的uuid4？

由钱海秋发布于 2020-04-09 05:47:33

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

2020-04-09 05:47:33

这将插入版本号为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)
  )
);

钱海秋

这家伙很懒，什么都没留下

积分
0
话题
0
评论
3224
注册排名
1956