Oracle Universal Unique Identifier (UUID)

本文介绍Oracle生成全局唯一ID的函数SYS_GUID，后续会对SYS_GUID和Sequence两种方法进行比较。

SYS_GUID 函数生成并返回一个由 16 个字节组成的全局唯一标识符（RAW 值）。在大多数平台上，生成的标识符由主机标识符、调用该函数的进程或线程的进程或线程标识符以及该进程或线程的非重复值（字节序列）组成。

create table loc as select * from locations;
ALTER TABLE loc ADD (uid_col RAW(16));
UPDATE loc SET uid_col = SYS_GUID();

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

LOCATION_ID UID_COL
----------- --------------------------------
       1000 27C94AC98E2B13C5E0634600000A203B
       1100 27C94AC98E2C13C5E0634600000A203B
       1200 27C94AC98E2D13C5E0634600000A203B
       1300 27C94AC98E2E13C5E0634600000A203B
       1400 27C94AC98E2F13C5E0634600000A203B
       1500 27C94AC98E3013C5E0634600000A203B
       1600 27C94AC98E3113C5E0634600000A203B
       1700 27C94AC98E3213C5E0634600000A203B
       1800 27C94AC98E3313C5E0634600000A203B
       1900 27C94AC98E3413C5E0634600000A203B
       2000 27C94AC98E3513C5E0634600000A203B
       2100 27C94AC98E3613C5E0634600000A203B
       2200 27C94AC98E3713C5E0634600000A203B
       2300 27C94AC98E3813C5E0634600000A203B
       2400 27C94AC98E3913C5E0634600000A203B
       2500 27C94AC98E3A13C5E0634600000A203B
       2600 27C94AC98E3B13C5E0634600000A203B
       2700 27C94AC98E3C13C5E0634600000A203B
       2800 27C94AC98E3D13C5E0634600000A203B
       2900 27C94AC98E3E13C5E0634600000A203B
       3000 27C94AC98E3F13C5E0634600000A203B
       3100 27C94AC98E4013C5E0634600000A203B
       3200 27C94AC98E4113C5E0634600000A203B

23 rows selected.

注意以上输出中的第12-13字节是有序的，从B1，C1，到40，41。这模拟了sequence的特点，但这不是必然的，例如在不同的节点上，如RAC。

查询时，直接使用uid_col和rawtohex(uid_col)显式转换的结果都是一样的，说明前者数据库进行了隐式转换。

SQL> desc loc
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 LOCATION_ID                                        NUMBER(4)
 STREET_ADDRESS                                     VARCHAR2(40)
 POSTAL_CODE                                        VARCHAR2(12)
 CITY                                      NOT NULL VARCHAR2(30)
 STATE_PROVINCE                                     VARCHAR2(25)
 COUNTRY_ID                                         CHAR(2)
 UID_COL                                            RAW(16)

SQL> select rawtohex(uid_col) from loc;

RAWTOHEX(UID_COL)
--------------------------------
27C94AC98E2B13C5E0634600000A203B
27C94AC98E2C13C5E0634600000A203B
27C94AC98E2D13C5E0634600000A203B
27C94AC98E2E13C5E0634600000A203B
27C94AC98E2F13C5E0634600000A203B
27C94AC98E3013C5E0634600000A203B
27C94AC98E3113C5E0634600000A203B
27C94AC98E3213C5E0634600000A203B
27C94AC98E3313C5E0634600000A203B
27C94AC98E3413C5E0634600000A203B
27C94AC98E3513C5E0634600000A203B
27C94AC98E3613C5E0634600000A203B
27C94AC98E3713C5E0634600000A203B
27C94AC98E3813C5E0634600000A203B
27C94AC98E3913C5E0634600000A203B
27C94AC98E3A13C5E0634600000A203B
27C94AC98E3B13C5E0634600000A203B
27C94AC98E3C13C5E0634600000A203B
27C94AC98E3D13C5E0634600000A203B
27C94AC98E3E13C5E0634600000A203B
27C94AC98E3F13C5E0634600000A203B
27C94AC98E4013C5E0634600000A203B
27C94AC98E4113C5E0634600000A203B

23 rows selected.

SQL> select city from loc where uid_col='27C94AC98E2B13C5E0634600000A203B';

CITY
------------------------------
Roma

SQL> select city from loc where uid_col=hextoraw('27C94AC98E2B13C5E0634600000A203B');

CITY
------------------------------
Roma