MySQL Postgresql：主键的 UUID 或 SEQUENCE？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33274291/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgresql: UUID or SEQUENCE for primary key?
提问by Justin Leo
I am coming from MySQL, and in MySQL you can use AUTOINCREMENT for a row's unique id as the primary key.
我来自 MySQL，在 MySQL 中，您可以使用 AUTOINCREMENT 作为行的唯一 id 作为主键。
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 中没有 AUTOINCREMENT，只有 SEQUENCE 或 UUID。我在某处读到我们可以使用 UUID 作为表的主键。这具有屏蔽其他用户 ID 的额外优势（因为我想构建将 ID 作为参数的 API）。我应该为 Postgresql 使用哪个？
回答by Patrick
A sequencein PostgreSQL does exactly the same as AUTOINCREMENTin MySQL. A sequenceis more efficient than a uuidbecause it is 8 bytes instead of 16 for the uuid. You can use a uuidas a primary key, just like most any other data type.
sequencePostgreSQL 中的A与 MySQL 中的完全相同AUTOINCREMENT。Asequence比 a 更有效，uuid因为它是 8 个字节而不是 16 个字节uuid。您可以使用 auuid作为主键，就像大多数其他数据类型一样。
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 hash the ID using - for instance - md5().
但是，我看不出这与屏蔽用户 ID 有何关系。如果您想对其他用户屏蔽某个用户的 ID，您应该仔细管理表权限和/或使用 - 例如 - 散列 ID md5()。
If you want to protect a table with user data from snooping hackers that are trying to guess other IDs, the the uuidtype 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类型是一个很好的选择。版本 4 是最佳选择，因为它有 122 个随机位（其他 6 个用于识别版本）。您可以像这样创建主键：
id uuid PRIMARY KEY DEFAULT uuid_generate_v4()
and then you will never have to worry about it anymore.
然后你就再也不用担心了。
回答by Rahul Tripathi
You can use UUID as primary key in your table as it will be unique. However do keep in mind that UUID will occupy a bit more space as compared to SEQUENCE. And also they are not very fast. But yes they are for sure unique and hence you are guaranteed to get a consistent data.
您可以使用 UUID 作为表中的主键，因为它是唯一的。但是请记住，与 SEQUENCE 相比，UUID 会占用更多空间。而且它们也不是很快。但是是的，它们肯定是独一无二的，因此您可以保证获得一致的数据。
You can also refer:
你也可以参考：
回答by Clayton Bonelli
For many years I developed applications for databases using PKs and FKs as numerical sequential values. This has worked perfectly, but in recent years when creating cloud applications where information will be exchanged between applications and we will have integrations between various applications developed by us, we realized that the use of sequential IDs in our APIs ended up creating an effort.
多年来，我使用 PK 和 FK 作为数字序列值开发了数据库应用程序。这非常有效，但近年来在创建云应用程序时，在应用程序之间交换信息，我们将在我们开发的各种应用程序之间进行集成，我们意识到在我们的 API 中使用顺序 ID 最终创造了一项努力。
In some applications we have to find the ID (of the target application) to be sent via the API call, on the other hand our database tables, in all our applications have, in addition to the sequential PK / FK column, a UUID column, which was not used in API calls. In this scenario we decided to rewrite the APIs so that the UUID column was used.
在某些应用程序中，我们必须找到要通过 API 调用发送的（目标应用程序的）ID，另一方面，我们的数据库表，在我们所有的应用程序中，除了顺序 PK / FK 列之外，还有一个 UUID 列，未在 API 调用中使用。在这种情况下，我们决定重写 API，以便使用 UUID 列。
This solved some of the problems because one of our desktop applications would have their data migrated to another cloud application, this cloud application also used PK / FK columns. When migrating this data we had to change the values ??of the PKs / FKs for new sequences as the sequences could clash between the values ??of the desktop application and the values ??of the cloud application. With this in mind we chose to switch cloud application PKs / FKs to UUID, since data coming from the desktop application had a UUID column.
这解决了一些问题，因为我们的一个桌面应用程序会将它们的数据迁移到另一个云应用程序，这个云应用程序也使用了 PK / FK 列。在迁移此数据时，我们必须更改新序列的 PK/FK 值，因为这些序列可能会在桌面应用程序的值和云应用程序的值之间发生冲突。考虑到这一点，我们选择将云应用程序 PK/FK 切换到 UUID，因为来自桌面应用程序的数据有一个 UUID 列。
The problem then was to convert the cloud application tables by turning the INT columns (PKs and FKs) into UUID columns without losing the table information. That was a big task, but it was made easier because I ended up building an application that makes this change easer. The application changes every PK / FK integer column to UUID, keeping the data and relationships. Anyone interested follows the link:
然后的问题是通过将 INT 列（PK 和 FK）转换为 UUID 列来转换云应用程序表，而不会丢失表信息。这是一项艰巨的任务，但它变得更容易了，因为我最终构建了一个应用程序，使这种更改更容易。应用程序将每个 PK / FK 整数列更改为 UUID，保留数据和关系。有兴趣的可以点击链接：
