如何在 Oracle 中生成 GUID？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3037462/
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 GUID in Oracle?
提问by Acibi
Is it possible to auto-generate a GUID into an Insert statement?
是否可以将 GUID 自动生成到 Insert 语句中？
Also, what type of field should I use to store this GUID?
另外，我应该使用什么类型的字段来存储这个 GUID？
回答by Tony Andrews
You can use the SYS_GUID() function to generate a GUID in your insert statement:
您可以使用 SYS_GUID() 函数在插入语句中生成 GUID：
insert into mytable (guid_col, data) values (sys_guid(), 'xxx');
The preferred datatype for storing GUIDs is RAW(16).
存储 GUID 的首选数据类型是 RAW(16)。
As Gopinath answer:
正如戈皮纳特回答的那样：
select sys_guid() from dual
union all
select sys_guid() from dual
union all
select sys_guid() from dual
You get
你得到
88FDC68C75DDF955E040449808B55601
88FDC68C75DEF955E040449808B55601
88FDC68C75DFF955E040449808B55601
88FDC68C75DDF955E040449808B55601
88FDC68C75DEF955E040449808B55601
88FDC68C75DFF955E040449808B55601
As Tony Andrews says, differs only at one character
正如托尼安德鲁斯所说，只有一个角色不同
88FDC68C75DDF955E040449808B55601
88FDC68C75DEF955E040449808B55601
88FDC68C75DFF955E040449808B55601
88FDC68C75D DF955E040449808B55601
88FDC68C75D EF955E040449808B55601
88FDC68C75D FF955E040449808B55601
Maybe useful: http://feuerthoughts.blogspot.com/2006/02/watch-out-for-sequential-oracle-guids.html
也许有用：http: //feuerthoughts.blogspot.com/2006/02/watch-out-for-sequential-oracle-guids.html
回答by TTT
You can also include the guid in the create statement of the table as default, for example:
您还可以在表的 create 语句中默认包含 guid，例如：
create table t_sysguid
( id raw(16) default sys_guid() primary key
, filler varchar2(1000)
)
/
回答by Kenneth Baltrinic
It is not clear what you mean by auto-generate a guid into an insert statement but at a guess, I think you are trying to do something like the following:
目前尚不清楚将 guid 自动生成到插入语句中是什么意思，但据猜测，我认为您正在尝试执行以下操作：
INSERT INTO MY_TAB (ID, NAME) VALUES (SYS_GUID(), 'Adams');
INSERT INTO MY_TAB (ID, NAME) VALUES (SYS_GUID(), 'Baker');
In that case I believe the ID column should be declared as RAW(16);
在这种情况下，我认为 ID 列应声明为 RAW(16)；
I am doing this off the top of my head. I don't have an Oracle instance handy to test against, but I think that is what you want.
我正在做这件事。我没有可以方便地测试的 Oracle 实例，但我认为这就是您想要的。
回答by Zartch
Example found on: http://www.orafaq.com/usenet/comp.databases.oracle.server/2006/12/20/0646.htm
示例位于：http: //www.orafaq.com/usenet/comp.databases.oracle.server/2006/12/20/0646.htm
SELECT REGEXP_REPLACE(SYS_GUID(), '(.{8})(.{4})(.{4})(.{4})(.{12})', '----') MSSQL_GUID FROM DUAL
Result:
结果：
6C7C9A50-3514-4E77-E053-B30210AC1082
回答by MikeWyatt
sys_guid() is a poor option, as other answers have mentioned. One way to generate UUIDs and avoid sequential values is to generate random hex strings yourself:
sys_guid() 是一个糟糕的选择，正如其他答案所提到的。生成 UUID 并避免序列值的一种方法是自己生成随机的十六进制字符串：
select regexp_replace(
to_char(
DBMS_RANDOM.value(0, power(2, 128)-1),
'FM0xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'),
'([a-f0-9]{8})([a-f0-9]{4})([a-f0-9]{4})([a-f0-9]{4})([a-f0-9]{12})',
'----') from DUAL;
回答by BERGUIGA Mohamed Amine
you can use function bellow in order to generate your UUID
您可以使用下面的函数来生成您的 UUID
create or replace 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 SUBSTR(XSTR, 1, 4) || '-' ||
SUBSTR(XSTR, 5, 4) || '-' ||
SUBSTR(XSTR, 9, 4) || '-' ||
SUBSTR(XSTR, 13,4) || '-' ||
SUBSTR(XSTR, 17,4) || '-' ||
SUBSTR(XSTR, 21,4) || '-' ||
SUBSTR(XSTR, 24,4) || '-' ||
SUBSTR(XSTR, 28,4);
END RANDOM_GUID;
Example of GUID genedrated by the function above:
8EA4-196D-BC48-9793-8AE8-5500-03DC-9D04
上述函数生成的 GUID 示例：
8EA4-196D-BC48-9793-8AE8-5500-03DC-9D04
回答by Gopinath Kotla
You can run the following query
您可以运行以下查询
select sys_guid() from dual
union all
select sys_guid() from dual
union all
select sys_guid() from dual
回答by BurnsBA
If you need non-sequential guids you can send the sys_guid()results through a hashing function (see https://stackoverflow.com/a/22534843/1462295). The idea is to keep whatever uniqueness is used from the original creation, and get something with more shuffled bits.
如果您需要非顺序 guid，您可以sys_guid()通过散列函数发送结果（请参阅https://stackoverflow.com/a/22534843/1462295）。这个想法是保留原始创作中使用的任何独特性，并获得更多混洗位的东西。
For instance:
例如：
LOWER(SUBSTR(STANDARD_HASH(SYS_GUID(), 'SHA1'), 0, 32))
Example showing default sequential guid vs sending it through a hash:
显示默认顺序 guid 与通过哈希发送它的示例：
SELECT LOWER(SYS_GUID()) AS OGUID FROM DUAL
UNION ALL
SELECT LOWER(SYS_GUID()) AS OGUID FROM DUAL
UNION ALL
SELECT LOWER(SYS_GUID()) AS OGUID FROM DUAL
UNION ALL
SELECT LOWER(SYS_GUID()) AS OGUID FROM DUAL
UNION ALL
SELECT LOWER(SUBSTR(STANDARD_HASH(SYS_GUID(), 'SHA1'), 0, 32)) AS OGUID FROM DUAL
UNION ALL
SELECT LOWER(SUBSTR(STANDARD_HASH(SYS_GUID(), 'SHA1'), 0, 32)) AS OGUID FROM DUAL
UNION ALL
SELECT LOWER(SUBSTR(STANDARD_HASH(SYS_GUID(), 'SHA1'), 0, 32)) AS OGUID FROM DUAL
UNION ALL
SELECT LOWER(SUBSTR(STANDARD_HASH(SYS_GUID(), 'SHA1'), 0, 32)) AS OGUID FROM DUAL
output
输出
80c32a4fbe405707e0531e18980a1bbb
80c32a4fbe415707e0531e18980a1bbb
80c32a4fbe425707e0531e18980a1bbb
80c32a4fbe435707e0531e18980a1bbb
c0f2ff2d3ef7b422c302bd87a4588490
d1886a8f3b4c547c28b0805d70b384f3
a0c565f3008622dde3148cfce9353ba7
1c375f3311faab15dc6a7503ce08182c
