如何通过UUID在PostgreSQL中进行排序?
在
postgresql上使用uuid_generate_v1()有任何顺序保证吗?
如果是,那么保证是每台机器还是在哪台机器上生成UUID无关紧要?由于V1 UUID是时间戳MAC地址,因此Postgres内部按时间戳部分排序,然后按MAC排序吗? 我可以“订购”UUID类型列并期望它始终有效(似乎工作)? 我想在多台机器上生成UUID(使用postgresql uuid_generate_v1()),将它们复制到一个Postgres实例,然后按UUID列排序.它必须保证按机器排序,而不是所有机器的UUID顺序. 解决方法
虽然绝不是明确的答案 – 即. “是所有PostgreSQL安装中定义的行为吗?”,这个SQL(用于SQL Server)检查GUID中每个字节的顺序.可能需要对PostgreSQL进行一些调整.
生成这样的映射应该允许人们在PostgreSQL中查看特定的UUID结构(明确定义的类型之一或其他)“以特定方式命令”. With UIDs As (-- 0 1 2 3 4 5 6 7 8 9 A B C D E F Select ID = 'F',UID = cast ('00000000-0000-0000-0000-000000000011' as uniqueidentifier) Union Select ID = 'E',UID = cast ('00000000-0000-0000-0000-000000001100' as uniqueidentifier) Union Select ID = 'D',UID = cast ('00000000-0000-0000-0000-000000110000' as uniqueidentifier) Union Select ID = 'C',UID = cast ('00000000-0000-0000-0000-000011000000' as uniqueidentifier) Union Select ID = 'B',UID = cast ('00000000-0000-0000-0000-001100000000' as uniqueidentifier) Union Select ID = 'A',UID = cast ('00000000-0000-0000-0000-110000000000' as uniqueidentifier) Union Select ID = '9',UID = cast ('00000000-0000-0000-0011-000000000000' as uniqueidentifier) Union Select ID = '8',UID = cast ('00000000-0000-0000-1100-000000000000' as uniqueidentifier) Union Select ID = '7',UID = cast ('00000000-0000-0011-0000-000000000000' as uniqueidentifier) Union Select ID = '6',UID = cast ('00000000-0000-1100-0000-000000000000' as uniqueidentifier) Union Select ID = '5',UID = cast ('00000000-0011-0000-0000-000000000000' as uniqueidentifier) Union Select ID = '4',UID = cast ('00000000-1100-0000-0000-000000000000' as uniqueidentifier) Union Select ID = '3',UID = cast ('00000011-0000-0000-0000-000000000000' as uniqueidentifier) Union Select ID = '2',UID = cast ('00001100-0000-0000-0000-000000000000' as uniqueidentifier) Union Select ID = '1',UID = cast ('00110000-0000-0000-0000-000000000000' as uniqueidentifier) Union Select ID = '0',UID = cast ('11000000-0000-0000-0000-000000000000' as uniqueidentifier) ) Select * From UIDs Order By UID desc 在SQL Server(2014,and matches that in SQL Server 2005)中,降序排序为: Position by highest-to-lowest value: A B C D E F | 8 9 | 7 6 | 5 4 | 3 2 1 0 由于SQL Server的newsequentialid利用这种顺序进行索引友好的GUID生成,因此行为可能永远不会改变. SQL Server还必须在所有系统中维护此行为以支持复制.因此,如果问题是关于SQL Server,我肯定会说“SQL Server中存在一致的GUID排序”,这绝对可以依赖于SQL Server. 但是,这种排序与.NET的GUID排序不同,如果PostgreSQL中的排序不同,我也不会感到惊讶. SQL Server中的“翻转”差异是因为它遵循COM GUIDs的“变体2”(又名小端)排序;甚至对于“变体1”UUID也是如此. (然而,为什么这些组织本身从右到左排序似乎更加随意:更多的微软历史?) 有趣的问题仍然存在:在PostgreSQL中指定哪个/如何指定?如果它没有明确指定,那么实现是否仍然可以被视为行为公理? 也是see this question for more details about SQL Server’s UUIDs;为什么这些差异存在的美味细节. (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |