当前位置： 文江博客话题详情

Oracle中如何生成GUID？

发布于 2024-09-05 09:07:01 字数 67 浏览 2 评论 0原文

是否可以在 Insert 语句中自动生成 GUID？

另外，我应该使用什么类型的字段来存储此 GUID？

原文

Is it possible to auto-generate a GUID into an Insert statement?

Also, what type of field should I use to store this GUID?

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（10）

妳是的陽光 2024-09-12 09:07:01

您可以使用 SYS_GUID() 函数在插入语句中生成 GUID：

insert into mytable (guid_col, data) values (sys_guid(), 'xxx');

存储 GUID 的首选数据类型是 RAW(16)。

正如戈皮纳特回答的那样：

 select sys_guid() from dual
 union all
 select sys_guid() from dual
 union all 
 select sys_guid() from dual

你得到

88FDC68C75DDF955E040449808B55601
88FDC68C75DEF955E040449808B55601
88FDC68C75DFF955E040449808B55601

正如托尼安德鲁斯所说，仅在一个字符上有所不同

88FDC68C75DDF955E040449808B55601
88FDC68C75DEF955E040449808B55601
88FDC68C75DFF955E040449808B55601

也许有用：http://feuerthoughts.blogspot.com/2006/02/watch-out-for-sequential-oracle-guids.html

You can use the SYS_GUID() function to generate a GUID in your insert statement:

insert into mytable (guid_col, data) values (sys_guid(), 'xxx');

The preferred datatype for storing GUIDs is 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

As Tony Andrews says, differs only at one character

88FDC68C75DDF955E040449808B55601
88FDC68C75DEF955E040449808B55601
88FDC68C75DFF955E040449808B55601

Maybe useful: http://feuerthoughts.blogspot.com/2006/02/watch-out-for-sequential-oracle-guids.html

回复 原文
痴意少年 2024-09-12 09:07:01

您还可以默认在表的创建语句中包含 guid，例如：

create table t_sysguid
( id     raw(16) default sys_guid() primary key
, filler varchar2(1000)
)
/

请参见此处：http://rwijk.blogspot.com/2009/12/sysguid.html

You can also include the guid in the create statement of the table as default, for example:

create table t_sysguid
( id     raw(16) default sys_guid() primary key
, filler varchar2(1000)
)
/

See here: http://rwijk.blogspot.com/2009/12/sysguid.html

回复 原文
疾风者 2024-09-12 09:07:01

示例发现于：
http://www.orafaq.com /usenet/comp.databases.oracle.server/2006/12/20/0646.htm

SELECT REGEXP_REPLACE(SYS_GUID(), '(.{8})(.{4})(.{4})(.{4})(.{12})', '\1-\2-\3-\4-\5') MSSQL_GUID  FROM DUAL

结果：

6C7C9A50-3514-4E77-E053-B30210AC1082

Example found on:
http://www.orafaq.com/usenet/comp.databases.oracle.server/2006/12/20/0646.htm

SELECT REGEXP_REPLACE(SYS_GUID(), '(.{8})(.{4})(.{4})(.{4})(.{12})', '\1-\2-\3-\4-\5') MSSQL_GUID  FROM DUAL

Result:

6C7C9A50-3514-4E77-E053-B30210AC1082
回复 原文
挽你眉间 2024-09-12 09:07:01

目前尚不清楚在插入语句中自动生成 guid 是什么意思，但猜测，我认为您正在尝试执行如下操作：

INSERT INTO MY_TAB (ID, NAME) VALUES (SYS_GUID(), 'Adams');
INSERT INTO MY_TAB (ID, NAME) VALUES (SYS_GUID(), 'Baker');

在这种情况下，我认为 ID 列应声明为 RAW(16) ;

我是凭空做这件事的。我没有方便测试的 Oracle 实例，但我认为这就是您想要的。

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:

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

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.

回复 原文
公布 2024-09-12 09:07:01

如果您需要非顺序 guid，您可以通过哈希函数发送 sys_guid() 结果（请参阅 https: //stackoverflow.com/a/22534843/1462295）。这个想法是保留原始创作中使用的任何独特性，并获得具有更多改组位的东西。

例如：

LOWER(SUBSTR(STANDARD_HASH(SYS_GUID(), 'SHA1'), 0, 32))

显示默认顺序 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

输出

80c32a4fbe405707e0531e18980a1bbb
80c32a4fbe415707e0531e18980a1bbb
80c32a4fbe425707e0531e18980a1bbb
80c32a4fbe435707e0531e18980a1bbb
c0f2ff2d3ef7b422c302bd87a4588490
d1886a8f3b4c547c28b0805d70b384f3
a0c565f3008622dde3148cfce9353ba7
1c375f3311faab15dc6a7503ce08182c

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.

For instance:

LOWER(SUBSTR(STANDARD_HASH(SYS_GUID(), 'SHA1'), 0, 32))

Example showing default sequential guid vs sending it through a hash:

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
回复 原文
别在捏我脸啦 2024-09-12 09:07:01

正如其他答案所提到的，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})',
    '\1-\2-\3-\4-\5') from DUAL;

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:

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})',
    '\1-\2-\3-\4-\5') from DUAL;
回复 原文
挽容 2024-09-12 09:07:01

您可以运行以下查询

 select sys_guid() from dual
 union all
 select sys_guid() from dual
 union all 
 select sys_guid() from dual

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
回复 原文
极度宠爱 2024-09-12 09:07:01

我建议使用 Oracle 的“dbms_crypto.randombytes”函数。

为什么？
此函数返回包含加密安全伪随机字节序列的 RAW 值，它可用于生成加密密钥的随机材料。

select REGEXP_REPLACE(dbms_crypto.randombytes(16), '(.{8})(.{4})(.{4})(.{4})(.{12})', '\1-\2-\3-\4-\5') from dual;

您不应该不使用函数“sys_guid”，因为只有一个字符会发生变化。

ALTER TABLE locations ADD (uid_col RAW(16));

UPDATE locations SET uid_col = SYS_GUID();

SELECT location_id, uid_col FROM locations
   ORDER BY location_id, uid_col;

LOCATION_ID UID_COL
----------- ----------------------------------------------------------------
       1000 09F686761827CF8AE040578CB20B7491
       1100 09F686761828CF8AE040578CB20B7491
       1200 09F686761829CF8AE040578CB20B7491
       1300 09F68676182ACF8AE040578CB20B7491
       1400 09F68676182BCF8AE040578CB20B7491
       1500 09F68676182CCF8AE040578CB20B7491

https://docs.oracle.com/database/121/SQLRF/函数202.htm#SQLRF06120

I would recommend using Oracle's "dbms_crypto.randombytes" function.

Why?
This function returns a RAW value containing a cryptographically secure pseudo-random sequence of bytes, which can be used to generate random material for encryption keys.

select REGEXP_REPLACE(dbms_crypto.randombytes(16), '(.{8})(.{4})(.{4})(.{4})(.{12})', '\1-\2-\3-\4-\5') from dual;

You should not use the function "sys_guid" because only one character changes.

ALTER TABLE locations ADD (uid_col RAW(16));

UPDATE locations SET uid_col = SYS_GUID();

SELECT location_id, uid_col FROM locations
   ORDER BY location_id, uid_col;

LOCATION_ID UID_COL
----------- ----------------------------------------------------------------
       1000 09F686761827CF8AE040578CB20B7491
       1100 09F686761828CF8AE040578CB20B7491
       1200 09F686761829CF8AE040578CB20B7491
       1300 09F68676182ACF8AE040578CB20B7491
       1400 09F68676182BCF8AE040578CB20B7491
       1500 09F68676182CCF8AE040578CB20B7491

https://docs.oracle.com/database/121/SQLRF/functions202.htm#SQLRF06120

回复 原文
雄赳赳气昂昂 2024-09-12 09:07:01

您可以使用下面的函数来生成您的 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;

由上面的函数生成的 GUID 示例：
8EA4-196D-BC48-9793-8AE8-5500-03DC-9D04

you can use function bellow in order to generate your 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

回复 原文
芯好空 2024-09-12 09:07:01

创建 350 个字符的 GUID：

dbms_random.STRING ('a', 350) - 返回混合大小写字母字符的字符串

dbms_random.STRING ('x', 350) - 返回大写字母数字字符的字符串

Creating a 350 characters GUID:

dbms_random.STRING ('a', 350) - returning string in mixed case alpha characters

dbms_random.STRING ('x', 350) - returning string in uppercase alpha-numeric characters

回复 原文
~没有更多了~

关于作者

背叛残局

暂无简介

0 文章
0 评论
661 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

忆伤

文章 0 评论 0

眼泪也成诗

文章 0 评论 0

zangqw

文章 0 评论 0

旧伤慢歌

文章 0 评论 0

qq_GlP2oV

文章 0 评论 0

旧时模样

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文