您对使用UUID作为数据库行标识符（特别是在Web应用程序中）有什么看法？ [英] What's your opinion on using UUIDs as database row identifiers, particularly in web apps?

问题描述

为了简单和（假定）速度，我总是倾向于使用长整数作为数据库中的主键。但是，当对对象实例使用 REST 或类似Rails的网址方案时，我最终会使用以下网址：

I've always preferred to use long integers as primary keys in databases, for simplicity and (assumed) speed. But when using a REST or Rails-like URL scheme for object instances, I'd then end up with URLs like this:

http://example.com/user/783

然后假设还有ID为782,781，...，2和1的用户。假设Web应用程序在安全性足以防止人们输入其他号码以查看其他用户没有授权，一个简单的顺序分配的代理键也泄漏实例（超过这一个）的总数，在这种情况下，用户，可能是特权信息。 （例如，我是stackoverflow中的用户＃726。）

And then the assumption is that there are also users with IDs of 782, 781, ..., 2, and 1. Assuming that the web app in question is secure enough to prevent people entering other numbers to view other users without authorization, a simple sequentially-assigned surrogate key also "leaks" the total number of instances (older than this one), in this case users, which might be privileged information. (For instance, I am user #726 in stackoverflow.)

UUID / GUID是一个更好的解决方案？然后，我可以设置如下的网址：

Would a UUID/GUID be a better solution? Then I could set up URLs like this:

http://example.com/user/035a46e0-6550-11dd-ad8b-0800200c9a66

不完全简洁，但显示的用户隐含信息较少。当然，它的安全通过隐晦，这是不能取代适当的安全，但它似乎至少有点更安全。

Not exactly succinct, but there's less implied information about users on display. Sure, it smacks of "security through obscurity" which is no substitute for proper security, but it seems at least a little more secure.

这个好处是值得的成本和实现Web可寻址对象实例的UUID的复杂性？我想我仍然想使用整数列作为数据库PK只是为了加快连接。

Is that benefit worth the cost and complexity of implementing UUIDs for web-addressable object instances? I think that I'd still want to use integer columns as database PKs just to speed up joins.

还有一个问题是数据库中的UUID表示。我知道MySQL将它们存储为36个字符的字符串。 Postgres似乎有一个更有效的内部表示（128位？），但我没有自己尝试。

There's also the question of in-database representation of UUIDs. I know MySQL stores them as 36-character strings. Postgres seems to have a more efficient internal representation (128 bits?) but I haven't tried it myself. Anyone have any experience with this?

更新：对于那些询问只在网址中使用用户名的用户， http://example.com/user/yukondude ），对于名称为是唯一的，但是真正只能通过数字识别的万维网应用程序对象呢？订单，交易，发票，重复图片名称，stackoverflow问题，...

Update: for those who asked about just using the user name in the URL (e.g., http://example.com/user/yukondude), that works fine for object instances with names that are unique, but what about the zillions of web app objects that can really only be identified by number? Orders, transactions, invoices, duplicate image names, stackoverflow questions, ...

推荐答案

的问题。但是uuids非常适合n层应用程序。 PK生成可以分散：每个客户端生成它自己的pk没有碰撞的风险。
并且速度差异通常很小。

I can't say about the web side of your question. But uuids are great for n-tier applications. PK generation can be decentralized: each client generates it's own pk without risk of collision. And the speed difference is generally small.

确保您的数据库支持高效的存储数据类型（16字节，128位）。
至少你可以编码base64中的uuid字符串并使用char（22）。

Make sure your database supports an efficient storage datatype (16 bytes, 128 bits). At the very least you can encode the uuid string in base64 and use char(22).

我已经使用它们与Firebird广泛使用，并推荐。

I've used them extensively with Firebird and do recommend.

这篇关于您对使用UUID作为数据库行标识符（特别是在Web应用程序中）有什么看法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！