数据库中的表用于生成主键？
您是否曾经使用单独的表来“生成”数据库的人工主键（以及为什么）？我的意思是拥有一个包含两列的表，表名和当前 ID - 通过简单地锁定具有该表名的行，获取键的当前值，递增它，您可以获取某个表的新“ID”加一，然后解锁该行。为什么您更喜欢这个而不是标准整数标识列？
PS“想法”来自企业应用程序架构的 Fowlers 模式，顺便说一句......
Do you ever use a separate table for "generating" artificial primary keys for DB (and why)? What I mean is to have a table with two columns, table name and current ID - with which you could get new "ID" for some table by simply locking the row with that table name, getting the current value of the key, increment it by one, and unlock the row. Why would you prefer this over standard integer identity column?
P.S. The "idea" is from Fowlers Patterns of Enterprise Application Architecture, btw...
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
我唯一一次使用过它是当我在 BTrieve 中有一个应用程序时，它没有标识列。我还应该说，当他们尝试使用此表时，由于所有额外的读取和写入，导致他们尝试导入数据时速度大幅减慢。我的朋友查看了它并重写了他们的做法以加快速度，但这个故事的寓意是，如果你错误地做这样的事情，可能会产生残酷的后果。
就我个人而言，我认为我永远不想这样做。出错的可能性太大了。两个人尝试使用同一把钥匙，因为他们在获取 id 之前忘记锁定桌子。如果可能的话，这似乎应该留给 RDBMS 处理。正如威尔所说，将这种情况最小化很容易，但如果你不知道自己在做什么，这种情况就有可能发生。
The only time I have ever used this is when I had an application in BTrieve, and it didn't have an identity column. And I should also say when they tried to use this table, it caused a massive slow down when they tried to import data, because of all the extra reads and writes. My friend looked at it and rewrote how they did it to speed it up, but the moral of the story is that if you do something like this incorrectly, there can be brutal consequences.
Personally, I don't think I would ever want to do this. There is too much possibility for error. Two people try and use the same key, because they forgot to lock the table before grabbing the id. This just seems like something that should be left up to the RDBMS if at all possible. As Will brought up, it's easy to minimize this situation, but if you don't know what you are doing it can happen.
你根本不会喜欢它。
无论您通过使用该模式或变得与数据库无关获得什么好处，您都会在头痛、支持和性能方面损失。
You wouldn't prefer it at all.
Whatever you gain by using the pattern or becoming DB agnostic, you'll lose in headaches, support and performance.
听起来很简单，不是吗？
事实上，这是一场灾难。应用程序中没有任何活动作为独立操作发生：所有操作都是事务的一部分。人们不能简单地“解锁”该行，因为“解锁”实际上仅在提交时发生。这意味着表上需要 ID 的所有事务都会被序列化，并且任何时候只有一个事务可以继续进行。这还意味着访问多个表的事务可能会在更新 Id 表时陷入死锁，因为在实践中强制执行“获取下一个 Id”更新顺序很困难。
为了避免完全序列化，需要获取可以立即提交的单独、独立事务的 Id（通常是 UPDATE 本身的隐式自动提交事务）。但这使应用程序逻辑变得非常复杂。每个操作都需要维护两个单独的数据库连接，一个用于执行正常的事务逻辑，另一个用于获取所需的 Id。即使如此，Id 的更新也可能成为一个热点，以至于仍然会导致可见的争用和阻塞（类似于 Web 应用程序中普遍存在的可怕的“更新页面点击计数 +1”）。
简而言之：使用 IDENTITY。身份生成针对高并发进行了优化。
This sounds simple, doesn't it?
In reality, its a disaster. No activity occurs in the application as a standalone operation: all operations are part of transactions. One cannot simply 'unlock' the row because the 'unlock' will actually occur only at commit time. Which means that all transactions that need an Id on a table are serialized and only one can proceed at any time. It also means that transaction that access more than one table will likely deadlock on updating the table of Ids because enforcing the 'get the next Id' update order is hard in practice.
To avoid complete serialization one needs to obtain the Ids on separate, standalone, transactions that can commit immediately (usually implicit auto-commit transaction on the UPDATE itself). But this complicates the application logic tremendously. Every operation needs to maintain two separate connections to the database, one to do the normal transaction logic and another one to obtain the needed Ids. Even then, the update of Ids can become such a hot spot that it can still cause visible contention and blocking (similar to the dreaded 'update page hit count +1' prevalent on web apps).
In short: use IDENTITY. The identity generation is optimized for high concurrency.
我见过当一个数据库中创建的数据需要迁移、备份、集群或暂存到另一个数据库时使用的这种模式。在这种情况下，首先您要确保主键不需要更改。其次是外键。第三，外部暴露的按键或耐用的参考。
I have seen this pattern used when data created in one database needs to be migrated, backed-up, clustered or staged to another database. In this situation, first of all your want to ensure the primary keys will not need to change. Secondly the foreign keys. Thirdly, externally exposed keys or durable references.
这称为高/低分配。
您可以在表上的 INSERT 上使用触发器来从该表中获取 ID，并根据您的选择在获取 ID 之前或之后递增它。
当您必须处理多个数据库引擎时，通常会使用这种方法。 Oracle 中的自动增量标识符是通过 SEQUENCE 实现的，您可以从数据表上的 BEFORE INSERT TRIGGER 中使用 SEQUENCE.NEXTVALUE 来递增该标识符。
相反，SQL Server 具有 IDENTITY 列，本机自动增量，并且由 DBE 本身管理。
为了使您的软件能够在两个 DBE 上运行，您必须制定某种标准，那么最常见的“标准”就是主键的 Hi/Lo 分配。
这是其他方法中的一种。如今，使用 NHibernate 等 ORM 映射工具，它是通过配置提供的，因此您无需关心应用程序和数据库端。
编辑 #1
因为这种操作不能用于全局范围，所以每个数据库或数据库模式都必须有这样的表。这样，每个模式都是相互独立的。但是，一个模式中的数据不能隐式移动到具有相同键的另一个模式，因为它可能会与已存在的行发生冲突。
对于安全模式，它与另一个模式或用户访问相同的数据库，因此对于特定的安全模式不应该存在额外的表。
This is called Hi/Lo assignment.
You would do this having either a trigger on INSERT on your tables getting the ID from this table and incrementing it before or after you get your ID, depending of your choice.
This is commonly used when you have to deal with multiple database engines. The autoincremental identifier in Oracle is through a SEQUENCE, which you increment with SEQUENCE.NEXTVALUE from within a BEFORE INSERT TRIGGER on your data table.
Oppositly, SQL Server has IDENTITY columns, autoincrementing natively and this is managed by the DBE itself.
In order for your software to work on both DBE, you have to come to some sort of a standard, then the most common "standard" used for this is the Hi/Lo assignment to the primary key.
This is one approach amongst others. These days, with ORM Mapping tools such as NHibernate, it is offered through configuration so that you need less to care on both the application and the database sides.
EDIT #1
Because this kind of maneuvre can't be used for a global scope, you'd have to have such a table per database, or database schema. This way, each schema is indenpendant from the other. However, data in one schema can't implicitly be moved toward another with the same key, as it would perhaps be conflicted with an already existing row.
As for a security schema, it accesses the same database as another schema or user, so no additional table should exist for specific security schema.
只要您可以使用 sql server 的身份或 GUID 功能，您就应该使用。然而，在某些情况下这可能是不可能的。
一个例子是 SQL Server 只允许每个表有一个标识列。很少有表会包含同时需要私有 ID 和公共 ID 的记录，并且一个标识列的限制意味着将两者生成为整数可能会很痛苦。您始终可以使用 guid，但您希望私有 id 上的整数能够提高速度，并且您可能还希望公共 id 比 guid 更易于理解。
在这种情况下，用于生成 id 的额外表是有意义的。不过，我会采取一些不同的做法。表中仍然有两列，但为每个真实表创建一个“影子”或“Id 映射”表。其中一列将是您的私有 ID（唯一约束），一列将是您的公共 ID（增量值可能为“7”或“13”或其他比“1”不那么明显的数字的身份）。
这里的关键区别是您不想自己进行锁定。让sql server 处理它。
Whenever you can use sql server's identity or guid features, you should. However, there are a few situations where this may not be possible.
One example is that sql server only allows one identity column per table. Rarely, a table will have records that need both a private id and a public id, and a limit of one identity column means generating both as integers can be a pain. You could always use a guid for one, but you want the integer on the private id for speed and you may also want the public id to be more human readable than a guid.
In this situation, an extra table for generating the ids can make sense. However, I'd do it a bit differently. Still have two columns in the table, but make one "shadow" or "Id mapping" table for every real table. One of the columns will be your private id (unique constraint) and one will be your public id (identity with maybe an increment value of '7' or '13' or other number that's less obvious than '1').
The key difference here is that you don't want to do the locking yourself. Let sql server handle it.