postgresql - 按 UUID 排序在 PostgreSQL 中如何工作？

Question

在 postgresql 上使用 uuid_generate_v1() 是否有任何顺序保证？

如果是，t保证是每台机器还是在哪台机器上生成的 UUID 无关紧要？由于 V1 UUID 是按时间戳 + MAC 地址，Postgres 是否在内部按时间戳部分然后按 MAC 排序？

我能否按 UUID 类型列“排序”并期望它始终有效(似乎有效)？

我想在多台机器上生成 UUID(使用 postgresql uuid_generate_v1())，将它们复制到一个 Postgres 实例，然后按 UUID 列排序。它必须保证按机器排序，而不是所有机器的UUID的顺序。

Answer 1

我针对 PostgreSQL 调整了上述查询

With UIDs As (--                     0 1 2 3  4 5  6 7  8 9  A B C D E F
            Select 'F' as id, cast('00000000-0000-0000-0000-000000000011' as uuid) as uid
    Union   Select 'E' as id, cast('00000000-0000-0000-0000-000000001100' as uuid) as uid
    Union   Select 'D' as id, cast('00000000-0000-0000-0000-000000110000' as uuid) as uid
    Union   Select 'C' as id, cast('00000000-0000-0000-0000-000011000000' as uuid) as uid
    Union   Select 'B' as id, cast('00000000-0000-0000-0000-001100000000' as uuid) as uid
    Union   Select 'A' as id, cast('00000000-0000-0000-0000-110000000000' as uuid) as uid
    Union   Select '9' as id, cast('00000000-0000-0000-0011-000000000000' as uuid) as uid
    Union   Select '8' as id, cast('00000000-0000-0000-1100-000000000000' as uuid) as uid
    Union   Select '7' as id, cast('00000000-0000-0011-0000-000000000000' as uuid) as uid
    Union   Select '6' as id, cast('00000000-0000-1100-0000-000000000000' as uuid) as uid
    Union   Select '5' as id, cast('00000000-0011-0000-0000-000000000000' as uuid) as uid
    Union   Select '4' as id, cast('00000000-1100-0000-0000-000000000000' as uuid) as uid
    Union   Select '3' as id, cast('00000011-0000-0000-0000-000000000000' as uuid) as uid
    Union   Select '2' as id, cast('00001100-0000-0000-0000-000000000000' as uuid) as uid
    Union   Select '1' as id, cast('00110000-0000-0000-0000-000000000000' as uuid) as uid
    Union   Select '0' as id, cast('11000000-0000-0000-0000-000000000000' as uuid) as uid
)
Select * From UIDs Order By uid desc

在 PostgreSQL 中，排序顺序不同:

Position by highest-to-lowest value

id                                  uuid                             
 0  11000000-0000-0000-0000-000000000000
 1  00110000-0000-0000-0000-000000000000
 2  00001100-0000-0000-0000-000000000000
 3  00000011-0000-0000-0000-000000000000
 4  00000000-1100-0000-0000-000000000000
 5  00000000-0011-0000-0000-000000000000
 6  00000000-0000-1100-0000-000000000000
 7  00000000-0000-0011-0000-000000000000
 8  00000000-0000-0000-1100-000000000000
 9  00000000-0000-0000-0011-000000000000
 A  00000000-0000-0000-0000-110000000000
 B  00000000-0000-0000-0000-001100000000
 C  00000000-0000-0000-0000-000011000000
 D  00000000-0000-0000-0000-000000110000
 E  00000000-0000-0000-0000-000000001100
 F  00000000-0000-0000-0000-000000000011

这意味着 UUID 不按其在 PostgreSQL 中的时间组件排序。在内部，PostgreSQL uses memcmp to sort UUIDs by their memory layout .