blocks|key|1009220|text|您还可以在表的create语句中包含guid作为默认值，例如：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1009221|create+table+t_sysguid
(+id+++++raw(16)+default+sys_guid()+primary+key
,+filler+varchar2(1000)
)
/|code-block|syntax|javascript|1009222|查看此处：http://rwijk.blogspot.com/2009/12/sysguid.html|offset|length|1009223|entityMap|0|LINK|mutability|MUTABLE|url|http://rwijk.blogspot.com/2009/12/sysguid.html^0|0|0|5|1A|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

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

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

See here: <a href="http://rwijk.blogspot.com/2009/12/sysguid.html" rel="noreferrer">http://rwijk.blogspot.com/2009/12/sysguid.html</a>

blocks|key|1009382|text|示例位于：http://www.orafaq.com/usenet/comp.databases.oracle.server/2006/12/20/0646.htm|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1009383|SELECT+REGEXP_REPLACE(SYS_GUID(),+'(.{8})(.{4})(.{4})(.{4})(.{12})',+'\1-\2-\3-\4-\5')+MSSQL_GUID++FROM+DUAL+|code-block|syntax|javascript|1009384|结果：|1009385|6C7C9A50-3514-4E77-E053-B30210AC1082+|1009386|entityMap|0|LINK|mutability|MUTABLE|url|http://www.orafaq.com/usenet/comp.databases.oracle.server/2006/12/20/0646.htm^0|5|25|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@$A|V|B|W|1|X]]|C|$]]|$1|D|3|E|5|F|7|Y|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|Z|8|@]|9|@]|C|$]]|$1|K|3|L|5|F|7|10|8|@]|9|@]|C|$G|H]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|C|$]]]|N|$O|$5|P|Q|R|C|$S|T]]]]

Example found on: 
<a href="http://www.orafaq.com/usenet/comp.databases.oracle.server/2006/12/20/0646.htm" rel="noreferrer">http://www.orafaq.com/usenet/comp.databases.oracle.server/2006/12/20/0646.htm</a>

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

Result:

<pre><code>6C7C9A50-3514-4E77-E053-B30210AC1082 
</code></pre>

blocks|key|1223507|text|不清楚您所说的将guid自动生成到insert语句中是什么意思，但根据猜测，我认为您正在尝试执行类似以下的操作：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1223508|INSERT+INTO+MY_TAB+(ID,+NAME)+VALUES+(SYS_GUID(),+'Adams');
INSERT+INTO+MY_TAB+(ID,+NAME)+VALUES+(SYS_GUID(),+'Baker');|code-block|syntax|javascript|1223509|在这种情况下，我认为ID列应该声明为RAW(16)；|1223510|我做这件事完全是胡思乱想。我没有现成的Oracle实例可供测试，但我认为这正是您想要的。|1223511|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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:

<pre><code>INSERT INTO MY_TAB (ID, NAME) VALUES (SYS_GUID(), 'Adams');
INSERT INTO MY_TAB (ID, NAME) VALUES (SYS_GUID(), 'Baker');
</code></pre>

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.

blocks|key|1223773|text|正如其他答案所提到的，sys_guid()是一个糟糕的选择。生成UUID并避免序列值的一种方法是自己生成随机十六进制字符串：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1223774|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;|code-block|syntax|javascript|1223775|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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:

<pre><code>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;
</code></pre>

blocks|key|1009287|text|您可以运行以下查询|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1009288|+select+sys_guid()+from+dual
+union+all
+select+sys_guid()+from+dual
+union+all+
+select+sys_guid()+from+dual|code-block|syntax|javascript|1009289|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You can run the following query

<pre><code> select sys_guid() from dual
 union all
 select sys_guid() from dual
 union all 
 select sys_guid() from dual
</code></pre>

blocks|key|1223863|text|如果需要非连续的guids，可以通过散列函数(参见https://stackoverflow.com/a/22534843/1462295+)发送sys_guid()结果。这个想法是保留原始创作中使用的任何唯一性，并获得具有更多混洗比特的东西。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1223864|例如：|1223865|LOWER(SUBSTR(STANDARD_HASH(SYS_GUID(),+'SHA1'),+0,+32))++|code-block|syntax|javascript|1223866|显示默认顺序guid与通过散列发送guid的示例：|1223867|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++|1223868|输出|1223869|80c32a4fbe405707e0531e18980a1bbb
80c32a4fbe415707e0531e18980a1bbb
80c32a4fbe425707e0531e18980a1bbb
80c32a4fbe435707e0531e18980a1bbb
c0f2ff2d3ef7b422c302bd87a4588490
d1886a8f3b4c547c28b0805d70b384f3
a0c565f3008622dde3148cfce9353ba7
1c375f3311faab15dc6a7503ce08182c|1223870|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/a/22534843/1462295^0|21|A|P|18|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|12|8|@$9|13|A|14|B|C]]|D|@$9|15|A|16|1|17]]|E|$]]|$1|F|3|G|5|6|7|18|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|19|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|1A|8|@]|D|@]|E|$]]|$1|O|3|P|5|J|7|1B|8|@]|D|@]|E|$K|L]]|$1|Q|3|R|5|6|7|1C|8|@]|D|@]|E|$]]|$1|S|3|T|5|J|7|1D|8|@]|D|@]|E|$K|L]]|$1|U|3|-4|5|6|7|1E|8|@]|D|@]|E|$]]]|V|$W|$5|X|Y|Z|E|$10|11]]]]

If you need non-sequential guids you can send the <code>sys_guid()</code> results through a hashing function (see <a href="https://stackoverflow.com/a/22534843/1462295">https://stackoverflow.com/a/22534843/1462295</a> ). The idea is to keep whatever uniqueness is used from the original creation, and get something with more shuffled bits. 

For instance: 

<pre class="lang-sql prettyprint-override"><code>LOWER(SUBSTR(STANDARD_HASH(SYS_GUID(), 'SHA1'), 0, 32)) 
</code></pre>

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

<pre><code>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 
</code></pre>

output 

<pre class="lang-none prettyprint-override"><code>80c32a4fbe405707e0531e18980a1bbb
80c32a4fbe415707e0531e18980a1bbb
80c32a4fbe425707e0531e18980a1bbb
80c32a4fbe435707e0531e18980a1bbb
c0f2ff2d3ef7b422c302bd87a4588490
d1886a8f3b4c547c28b0805d70b384f3
a0c565f3008622dde3148cfce9353ba7
1c375f3311faab15dc6a7503ce08182c
</code></pre>

blocks|key|1009491|text|创建350个字符的GUID：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1009492|dbms_random.STRING+('a'，350)+-返回混合大小写字母字符的字符串|1009493|dbms_random.STRING+('x'，350)+-返回大写字母数字字符的字符串|1009494|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

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

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

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

How to generate a GUID in Oracle?

可以将GUID自动生成到Insert语句中吗？另外，我应该使用什么类型的字段来存储此GUID？

问如何在Oracle中生成GUID？
EN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在Oracle中生成GUID？EN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在Oracle中生成GUID？
EN