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

问题描述

为了简单和(假设的)速度，我一直更喜欢使用长整数作为数据库中的主键.但是当对对象实例使用 REST 或类似 Rails 的 URL 方案时，我最终会使用如下网址:

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 会是更好的解决方案吗?然后我可以像这样设置 URL:

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?

更新:对于那些询问只使用 URL 中的用户名的人(例如，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, ...

推荐答案

我不能说你问题的网络方面.但是 uuid 非常适合 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屋！