为 SQL 插入生成 ID 的最佳方法是什么？
生成将在 INSERT 语句中立即使用的 ID 号并保持 ID 大致按顺序排列的最佳、独立于 DBMS 的方法是什么？
What is the best, DBMS-independent way of generating an ID number that will be used immediately in an INSERT statement, keeping the IDs roughly in sequence?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（11）
独立于数据库管理系统？ 这是一个问题。 两种最常见的方法是自动递增列和序列，大多数 DBMS 都会执行其中之一，但不会同时执行这两种方法。 因此，独立于数据库的方法是拥有另一个表，其中一列具有一个值，您可以锁定、选择、更新和解锁。
通常我会说“让 DBMS 独立性见鬼去吧”，并使用 PostgreSQL 中的序列或 MySQL 中的自动增量列来实现。 就我的目的而言，支持两者比试图找出一种在任何地方都适用的方法更好。
DBMS independent? That's a problem. The two most common methods are auto incrementing columns, and sequences, and most DBMSes do one or the other but not both. So the database independent way is to have another table with one column with one value that you lock, select, update, and unlock.
Usually I say "to hell with DBMS independence" and do it with sequences in PostgreSQL, or autoincrement columns in MySQL. For my purposes, supporting both is better than trying to find out one way that works everywhere.
如果您可以使用您选择的编程语言创建全局唯一标识符 (GUID) - 请将其视为您的ID。
在进行故障排除时，它们更难使用（输入 INT 的
where条件要容易得多），但也有一些优点。 通过在本地将 GUID 指定为密钥，您可以轻松构建父子记录关系，而无需首先将父项保存到数据库并检索 ID。 由于 GUID 根据定义是唯一的，因此您不必担心在服务器上增加密钥。If you can create a Globally Unique Identifier (GUID) in your chosen programming language - consider that as your id.
They are harder to work with when troubleshooting (it is much easier to type in a
wherecondition that is an INT) but there are also some advantages. By assigning the GUID as your key locally, you can easily build parent-child record relationships without first having to save the parent to the database and retrieve the id. And since the GUID, by definition, is unique, you don't have to worry about incrementing your key on the server.有自动增量或序列，
这有什么意义，这是您最不用担心的？
您将如何处理 SQL 本身？
MySQL 有 Limit，
SQL Server 有 Top，
Oracle 有 Rank
然后还有一百万个其他东西，如触发器、更改表语法等
There is auto increment or sequence
What is the point of this, that is the least of your worries?
How will you handle SQL itself?
MySQL has Limit,
SQL Server has Top,
Oracle has Rank
Then there are a million other things like triggers, alter table syntax etc etc
是的，原始 SQL 中最明显的方式（按照我的偏好顺序）是 a) 序列 b) 自动增量字段。 更好、更现代、更独立于 DBMS 的方法是根本不接触 SQL，而是使用（好的）ORM。
Yep, the obvious ways in raw SQL (and in my order of preference) are a) sequences b) auto-increment fields. The better, more modern, more DBMS-independent way is to not touch SQL at all, but to use a (good) ORM.
没有通用的方法可以做到这一点。 如果有的话，每个人都会使用它。 从定义上来说，SQL 厌恶这个想法——它是基于集合的逻辑的反模式（尽管在许多现实情况下是有用的）。
尝试从其他地方插入标识值时遇到的最大问题是当 SQL 语句涉及多个记录并且必须同时生成多个值时。
如果您需要它，请将其作为您的应用程序使用的数据库选择要求的一部分。 任何严肃的 DBMS 产品都会提供自己的使用机制，并且很容易围绕 DML 的差异进行编码。 这些变化几乎都在 DDL 中。
There's no universal way to do this. If there were, everyone would use it. SQL by definition abhors the idea - it's an antipattern for set-based logic (although a useful one, in many real-world cases).
The biggest problem you'd have trying to interpose an identity value from elsewhere is when a SQL statement involves several records, and several values must be generated simultaneously.
If you need it, then make it part of your selection requirements for a database to use with your application. Any serious DBMS product will provide its own mechanism to use, and it's easy enough to code around the differences in DML. The variations are pretty much all in the DDL.
我总是会选择数据库特定的解决方案，但如果您真的需要这样做，通常的方法就是实现您自己的序列。 您的 RDBMS 必须支持事务。
您创建一个包含 int 列的序列表，并用第一个数字作为种子，您的事务逻辑看起来像这样
事务强制写入锁定，以便下一个排队的插入在插入记录之前无法更新 tblSeq 值进入tblData。 只要所有插入都经过此事务，那么您生成的 ID 就是按顺序排列的。
I'd always go for the DB specific solution, but if you really have to the usual way of doing this is to implement your own sequence. Your RDBMS has to support transactions.
You create a sequence table which contains an int column and seed this with the first number, your transaction logic then looks something like this
The transaction forces a write lock such that the then next queued insert cannot update the tblSeq value before the record has been inserted into tblData. So long as all inserts go though this transaction then your generated ID is in sequence.
使用自动递增的 id 列。
Use an auto-incrementing id column.
真的有理由让它们必须按顺序排列吗？ 如果您只是将其用作 ID，那么您应该只能使用 UUID 的一部分或 md5(now()) 的前几位数字。
Is there really a reason that they have to be in sequence? If you're just using it as an ID, then you should just be able to use part of a UUID or the first couple digits of md5(now()).
你可以花点时间按摩一下。 它相当于类似的东西
所以它是类似 YYYYMMDDHHMMSSSS 的东西
You could take the time and massage it. It'd be the equivalent of something like
So it be something like YYYYMMDDHHMMSSSS
它可能是一种横向方法，但一个好的 ORM 类型库可能至少能够隐藏差异。 例如，在 Ruby 中，ActiveRecord（通常用于但不完全依赖于 Ruby Rails Web 框架）具有迁移功能。 在与平台无关的代码中声明的表定义中，数据类型、顺序 ID 生成和索引创建等实现细节都被推到了您的愿景之下。
我在 SQLite 上透明地开发了一个模式，然后在 MS SQL Server 上实现它，然后移植到 Oracle。 无需更改生成模式定义的代码。
正如我所说，它可能不是您正在寻找的，但是封装变化的最简单方法是使用已经为您完成封装的库。
It may be of a bit lateral approach, but a good ORM-type library will probably be able to at least hide the differences. For example, in Ruby there is ActiveRecord (commonly used in but not exclusively tied to the Ruby the Rails web framework) which has Migrations. Within a table definition, which is declared in platform-agnostic code, implementation details such as datatypes, sequential id generation and index creation are pushed down below your vision.
I have transparently developed a schema on SQLite, then implemented it on MS SQL Server and later ported to Oracle. Without ever changing the code that generates my schema definition.
As I say, it may not be what you're looking for, but the easiest way to encapsulate what varies is to use a library that has already done the encapsulation for you.
仅使用 SQL，可以采用以下方法：
3.1 将id写入数据库（以线程安全的方式），数据库始终保持更新的值
3.2 不要将其写入数据库，只需在内存中保持递增（线程安全方式）
With only SQL, following could be one to the approaches:
3.1 Write the id to the database (in thread-safe fashion) which always keeps updated value
3.2 Don't write it to the database, just keep incrementing in the memory (thread-safe manner)