PostgreSQL:主键是UUID还是SEQUENCE? [英] Postgresql: UUID or SEQUENCE for primary key?

问题描述

我来自MySQL，在MySQL中，您可以将AUTOINCREMENT用作行的唯一ID作为主键.

I am coming from MySQL, and in MySQL you can use AUTOINCREMENT for a row's unique id as the primary key.

我发现Postgresql中没有AUTOINCREMENT，只有SEQUENCE或UUID.我读过某个地方，我们可以将UUID用作表的主键.这具有屏蔽其他用户ID的附加优点(因为我想构建将ID作为参数的API).我应该为Postgresql使用哪个?

I find that there is no AUTOINCREMENT in Postgresql, only SEQUENCE or UUID.I have read somewhere that we can use UUID as the primary key of a table. This has the added advantage of masking other user's id (as I want to build APIs that take the ID in as a parameter). Which should I use for Postgresql?

推荐答案

PostgreSQL中的sequence与MySQL中的AUTOINCREMENT完全相同. sequence比uuid更有效，因为它是8个字节，而不是uuid的16个字节.就像大多数其他数据类型一样，您可以将uuid用作主键.

A sequence in PostgreSQL does exactly the same as AUTOINCREMENT in MySQL. A sequence is more efficient than a uuid because it is 8 bytes instead of 16 for the uuid. You can use a uuid as a primary key, just like most any other data type.

但是，我看不到这与掩盖用户ID有什么关系.如果要将某个用户的ID与其他用户屏蔽，则应谨慎管理表特权和/或使用-例如md5()对该ID进行加密.

However, I don't see how this relates to masking of an user ID. If you want to mask the ID of a certain user from other users, you should carefully manage the table privileges and/or encrypt the ID using - for instance - md5().

如果您想保护带有用户数据的表，以防止窥探试图猜测其他ID的黑客，则uuid类型是一个很好的选择.那么版本4是最好的选择，因为它具有122个随机位(其他6个用于标识版本).您可以这样创建一个主键:

If you want to protect a table with user data from snooping hackers that are trying to guess other IDs, the the uuid type is an excellent choice. The version 4 is then the best choice as it has 122 random bits (the other 6 are used for identification of the version). You can create a primary key like this:

id uuid PRIMARY KEY DEFAULT uuid_generate_v4()

然后您将不必再为它担心.

and then you will never have to worry about it anymore.

这篇关于PostgreSQL:主键是UUID还是SEQUENCE?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！