如何在 Oracle 上生成版本 4（随机）UUID？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13951576/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to generate a version 4 (random) UUID on Oracle?
提问by ceving
This blog explains, that the output of sys_guid()is not random for every system:
这个博客解释说，sys_guid()每个系统的输出不是随机的：
http://feuerthoughts.blogspot.de/2006/02/watch-out-for-sequential-oracle-guids.html
http://feuerthoughts.blogspot.de/2006/02/watch-out-for-sequential-oracle-guids.html
Unfortunately I have to use such a system.
不幸的是，我必须使用这样的系统。
How to ensure to get a random UUID? Is it possible with sys_guid()? If not how to reliably get a random UUID on Oracle?
如何确保获得随机 UUID？有可能sys_guid()吗？如果不是，如何在 Oracle 上可靠地获取随机 UUID？
采纳答案by ceving
I use this now as a workaround:
我现在使用它作为解决方法：
创建或替换函数 random_uuid 返回 RAW 是 v_uuid RAW(16); 开始 v_uuid := sys.dbms_crypto.randombytes(16); 返回 (utl_raw.overlay(utl_raw.bit_or(utl_raw.bit_and(utl_raw.substr(v_uuid, 7, 1), '0F'), '40'), v_uuid, 7)); 结束 random_uuid;
The function requires dbms_cryptoand utl_raw. Both require an execute grant.
该函数需要dbms_crypto和utl_raw。两者都需要执行授权。
grant execute on sys.dbms_crypto to uuid_user;
回答by Jon Heller
Here's a complete example, based on @Pablo Santa Cruz's answer and the code you posted.
这是一个完整的示例，基于@Pablo Santa Cruz 的回答和您发布的代码。
I'm not sure why you got an error message. It's probably an issue with SQL Developer. Everything works fine when you run it in SQL*Plus, and add a function:
我不知道为什么你收到一条错误消息。这可能是 SQL Developer 的问题。在 SQL*Plus 中运行时一切正常，并添加一个函数：
create or replace and compile
java source named "RandomUUID"
as
public class RandomUUID
{
public static String create()
{
return java.util.UUID.randomUUID().toString();
}
}
/
Java created.
Java created.
CREATE OR REPLACE FUNCTION RandomUUID
RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'RandomUUID.create() return java.lang.String';
/
Function created.
Function created.
select randomUUID() from dual;
RANDOMUUID() -------------------------------------------------------------- 4d3c8bdd-5379-4aeb-bc56-fcb01eb7cc33
RANDOMUUID() -------------------------------------------------------------- 4d3c8bdd-5379-4aeb-bc56-fcb01eb7cc33
But I would stick with SYS_GUIDif possible. Look at ID 1371805.1 on My Oracle Support - this bug is supposedly fixed in 11.2.0.3.
但SYS_GUID如果可能的话，我会坚持下去。查看 My Oracle Support 上的 ID 1371805.1 - 这个错误应该在 11.2.0.3 中修复。
EDIT
编辑
Which one is faster depends on how the functions are used.
哪个更快取决于函数的使用方式。
It looks like the Java version is slightly faster when used in SQL. However, if you're going to use this function in a PL/SQL context, the PL/SQL function is about twice as fast. (Probably because it avoids overhead of switching between engines.)
在 SQL 中使用时，Java 版本似乎稍快一些。但是，如果您打算在 PL/SQL 上下文中使用此函数，则 PL/SQL 函数的速度大约是其两倍。（可能是因为它避免了引擎之间切换的开销。）
Here's a quick example:
这是一个快速示例：
--Create simple table
create table test1(a number);
insert into test1 select level from dual connect by level <= 100000;
commit;
--SQL Context: Java function is slightly faster
--
--PL/SQL: 2.979, 2.979, 2.964 seconds
--Java: 2.48, 2.465, 2.481 seconds
select count(*)
from test1
--where to_char(a) > random_uuid() --PL/SQL
where to_char(a) > RandomUUID() --Java
;
--PL/SQL Context: PL/SQL function is about twice as fast
--
--PL/SQL: 0.234, 0.218, 0.234
--Java: 0.52, 0.515, 0.53
declare
v_test1 raw(30);
v_test2 varchar2(36);
begin
for i in 1 .. 10000 loop
--v_test1 := random_uuid; --PL/SQL
v_test2 := RandomUUID; --Java
end loop;
end;
/
Version 4 GUIDs are not completelyrandom. Some of the bytes are supposed to be fixed. I'm not sure why this was done, or if it matters, but according to https://www.cryptosys.net/pki/uuid-rfc4122.html:
版本 4 GUID 不是完全随机的。一些字节应该是固定的。我不确定为什么这样做，或者这是否重要，但根据https://www.cryptosys.net/pki/uuid-rfc4122.html：
The procedure to generate a version 4 UUID is as follows:
Generate 16 random bytes (=128 bits) Adjust certain bits according to RFC 4122 section 4.4 as follows: set the four most significant bits of the 7th byte to 0100'B, so the high nibble is "4" set the two most significant bits of the 9th byte to 10'B, so the high nibble will be one of "8", "9", "A", or "B". Encode the adjusted bytes as 32 hexadecimal digits Add four hyphen "-" characters to obtain blocks of 8, 4, 4, 4 and 12 hex digits Output the resulting 36-character string "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX"
生成版本 4 UUID 的过程如下：
Generate 16 random bytes (=128 bits) Adjust certain bits according to RFC 4122 section 4.4 as follows: set the four most significant bits of the 7th byte to 0100'B, so the high nibble is "4" set the two most significant bits of the 9th byte to 10'B, so the high nibble will be one of "8", "9", "A", or "B". Encode the adjusted bytes as 32 hexadecimal digits Add four hyphen "-" characters to obtain blocks of 8, 4, 4, 4 and 12 hex digits Output the resulting 36-character string "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX"
The values from the Java version appear to conform to the standard.
Java 版本的值似乎符合标准。
回答by lonecat
https://stackoverflow.com/a/10899320/1194307
https://stackoverflow.com/a/10899320/1194307
The following function use sys_guid() and transform it into uuid format:
以下函数使用 sys_guid() 并将其转换为 uuid 格式：
create or replace function random_uuid return VARCHAR2 is
v_uuid VARCHAR2(40);
begin
select regexp_replace(rawtohex(sys_guid()), '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})', '----') into v_uuid from dual;
return v_uuid;
end random_uuid;
It do not need create dbms_crypto package and grant it.
它不需要创建 dbms_crypto 包并授予它。
回答by Kirill
The easiest and shortest way to get a Java-based function for me was:
为我获取基于 Java 的函数的最简单和最短的方法是：
create or replace function random_uuid return varchar2 as
language java
name 'java.util.UUID.randomUUID() return String';
I can't completely understand why it does not compile if I add .toString()though.
如果我添加，我无法完全理解为什么它不能编译.toString()。
回答by Pablo Santa Cruz
You can write a Java procedure and compile it and run it inside Oracle. In that procedure, you can use:
您可以编写一个 Java 过程并编译它并在 Oracle 中运行它。在该过程中，您可以使用：
UUID uuid = UUID.randomUUID();
return uuid.toString();
To generate desired value.
产生想要的价值。
Here'sa link on how to compile java procedures in Oracle.
这是有关如何在 Oracle 中编译 java 过程的链接。
回答by marciel.deg
It may not be unique, but generate a "GUID-like" random string:
它可能不是唯一的，但会生成一个“类似 GUID”的随机字符串：
FUNCTION RANDOM_GUID
RETURN VARCHAR2 IS
RNG NUMBER;
N BINARY_INTEGER;
CCS VARCHAR2 (128);
XSTR VARCHAR2 (4000) := NULL;
BEGIN
CCS := '0123456789' || 'ABCDEF';
RNG := 15;
FOR I IN 1 .. 32 LOOP
N := TRUNC (RNG * DBMS_RANDOM.VALUE) + 1;
XSTR := XSTR || SUBSTR (CCS, N, 1);
END LOOP;
RETURN XSTR;
END RANDOM_GUID;
Adapted from source of DBMS_RANDOM.STRING.
改编自 DBMS_RANDOM.STRING 的来源。
回答by U?ur Ye?ilyurt
According to UUID Version 4 format should be xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx. @lonecatanswer provide this format, also @cevinganswer partially provide version 4 requirements. Missing part is format y, y should be one of 8, 9, a, or b.
根据 UUID 版本 4 格式应该是 xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx。@lonecat答案提供了这种格式，@ceving答案也部分提供了版本 4 的要求。缺少的部分是格式 y，y 应该是 8、9、a 或 b 之一。
After mixing these answers and fix the y part, code looks like below:
混合这些答案并修复 y 部分后，代码如下所示：
create or replace function fn_uuid return varchar2 is
/* UUID Version 4 must be formatted as xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx where x is any hexadecimal character (lower case only) and y is one of 8, 9, a, or b.*/
v_uuid_raw raw(16);
v_uuid varchar2(36);
v_y varchar2(1);
begin
v_uuid_raw := sys.dbms_crypto.randombytes(16);
v_uuid_raw := utl_raw.overlay(utl_raw.bit_or(utl_raw.bit_and(utl_raw.substr(v_uuid_raw, 7, 1), '0F'), '40'), v_uuid_raw, 7);
v_y := case round(dbms_random.value(1, 4))
when 1 then
'8'
when 2 then
'9'
when 3 then
'a'
when 4 then
'b'
end;
v_uuid_raw := utl_raw.overlay(utl_raw.bit_or(utl_raw.bit_and(utl_raw.substr(v_uuid_raw, 9, 1), '0F'), v_y || '0'), v_uuid_raw, 9);
v_uuid := regexp_replace(lower(v_uuid_raw), '([a-f0-9]{8})([a-f0-9]{4})([a-f0-9]{4})([a-f0-9]{4})([a-f0-9]{12})', '----');
return v_uuid;
end fn_uuid;
回答by Leonid
Accepted answer from ceving is inconsistent with RFC4122: the two most significant bits (bits 6 and 7) of the clock_seq_hi_and_reserved should be set to zero and one, respectively. That makes y equal to 8,9,a or b in already mentioned by u?ur-ye?ilyurt format xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx
ceving 接受的答案与 RFC4122 不一致：clock_seq_hi_and_reserved的两个最高有效位（第 6 位和第 7 位）应分别设置为 0 和 1。这使得 y 等于 8,9,a 或 b 在已经提到的 u?ur-ye?ilyurt 格式 xxxxxxxx-xxxx-4xxx-yxxx-xxxxxxxxxxxx
My solution made point blank along RFC:
我的解决方案在 RFC 中做了点空白：
create or replace function random_uuid return raw is
/*
Set the four most significant bits (bits 12 through 15) of the
time_hi_and_version field to the 4-bit version number from
Section 4.1.3.
*/
v_time_hi_and_version raw(2) := utl_raw.bit_and(utl_raw.bit_or(dbms_crypto.randombytes(2), '4000'), '4FFF');
/*
Set the two most significant bits (bits 6 and 7) of the
clock_seq_hi_and_reserved to zero and one, respectively.
*/
v_clock_seq_hi_and_reserved raw(1) := utl_raw.bit_and(utl_raw.bit_or(dbms_crypto.randombytes(1), '80'), 'BF');
/*
Set all the other bits to randomly (or pseudo-randomly) chosen
values.
*/
v_time raw(6) := dbms_crypto.randombytes(6);
v_clock_seq_low_and_node raw(7) := dbms_crypto.randombytes(7);
begin
return v_time || v_time_hi_and_version || v_clock_seq_hi_and_reserved || v_clock_seq_low_and_node;
end random_uuid;
EDIT:
编辑：
Although first implementation easy to understand it's rather inefficient. Next solution is 3 to 4 times faster.
虽然第一个实现很容易理解，但效率很低。下一个解决方案要快 3 到 4 倍。
create or replace function random_uuid2 return raw is
v_uuid raw(16) := dbms_crypto.randombytes(16);
begin
v_uuid := utl_raw.bit_or(v_uuid, '00000000000040008000000000000000');
v_uuid := utl_raw.bit_and(v_uuid, 'FFFFFFFFFFFF4FFFBFFFFFFFFFFFFFFF');
return v_uuid;
end;
This test demostrates that random_uuid takes about one millisecond and random_uuid2 only 250 microseconds. Concatenation in the first version consumed too much time;
该测试演示了 random_uuid 大约需要 1 毫秒，而 random_uuid2 仅需要 250 微秒。第一个版本中的串联消耗了太多时间；
declare
dummy_uuid raw(16);
begin
for i in 1 .. 20000 loop
--dummy_uuid := random_uuid;
dummy_uuid := random_uuid2;
end loop;
end;
回答by Sina Salmani
there are some pure plsql functions written by me and one of my friend that generates uuid version 4 and formats any type of GUIDs. also formatters written in two way. one concating string and one use regex for formatting uuid
我和我的一位朋友编写了一些纯 plsql 函数，它们生成 uuid 版本 4 并格式化任何类型的 GUID。格式化程序也以两种方式编写。一个连接字符串和一个使用正则表达式来格式化 uuid
CREATE OR REPLACE FUNCTION RANDOM_UUD_RAW
RETURN RAW IS V_UUID RAW(16);
BEGIN V_UUID := SYS.DBMS_CRYPTO.Randombytes(16);
V_UUID := UTL_RAW.Overlay(UTL_RAW.Bit_or(UTL_RAW.Bit_and(UTL_RAW.Substr(V_UUID, 7, 1), '0F'), '40'), V_UUID, 7, 1);
V_UUID := UTL_RAW.Overlay(UTL_RAW.Bit_or(UTL_RAW.Bit_and(UTL_RAW.Substr(V_UUID, 9, 1), '3F'), '80'), V_UUID, 9, 1);
RETURN V_UUID;
END RANDOM_UUD_RAW; --
CREATE OR REPLACE FUNCTION UUID_FORMATTER_CONCAT(V_UUID RAW)
RETURN VARCHAR2 IS V_STR VARCHAR2(36);
BEGIN V_STR := lower(SUBSTR(V_UUID, 1, 8) || '-' || SUBSTR(V_UUID, 9, 4) || '-' || SUBSTR(V_UUID, 13, 4) || '-' || SUBSTR(V_UUID, 17, 4) || '-' || SUBSTR(V_UUID, 21));
RETURN V_STR;
END UUID_FORMATTER_CONCAT; --
CREATE OR REPLACE FUNCTION UUID_FORMATTER_REGEX(V_UUID RAW)
RETURN VARCHAR2 IS V_STR VARCHAR2(36);
BEGIN V_STR := lower(regexp_replace(V_UUID, '(.{8})(.{4})(.{4})(.{4})(.{12})', '----'));
RETURN V_STR;
END UUID_FORMATTER_REGEX; --
CREATE OR REPLACE FUNCTION RANDOM_UUID_STR
RETURN VARCHAR2 AS BEGIN RETURN UUID_FORMATTER_CONCAT(RANDOM_UUD_RAW());
END RANDOM_UUID_STR; --
CREATE OR REPLACE FUNCTION RANDOM_UUID_STR_REGEX
RETURN VARCHAR2 AS BEGIN RETURN UUID_FORMATTER_REGEX(RANDOM_UUD_RAW());
END RANDOM_UUID_STR_REGEX;
