没有自动增量字段的数据库上的自动增量字段 [英] AutoIncrement fields on databases without autoincrement field

问题描述

在MS Sql Server中很容易创建自动增量字段.在我的系统中，我停止使用自动增量字段作为主键，现在我使用的是Guid.太棒了，这种变更让我受益匪浅.但是在另一个非主要的关键领域，我确实需要实现软自动增量".这是因为我的系统与数据库无关，所以我在c#中以编程方式创建了autoinc值.

In MS Sql Server is easy create autoincrement fields. In my systems I stopped to use autoincrement fields for primary keys, and now I use Guid's. It was awesome, I've got a lot of advantages with that change. But in another non-primary key fields, I really was needing implement a "soft autoincrement". It's because my system is DB independent, so I create the autoinc value programatically in c#.

我想了解没有自动增量的数据库上自动增量字段的解决方案，您使用的解决方案是什么?为什么?有一些关于此的Sql Ansi语句吗?并直接从我的C#生成，是否是更好的解决方案?

I would like about solutions for autoincrement fields on databases without autoincrement, what the solution that your use and why? There is some Sql Ansi statement about this? and generating directly from my c#, is a better solution?

PS:我知道从表中选择max(id)+1 并不是真正的并发友好...

PS: I know that select max(id)+1 from table it's not really concurrent friendly...

推荐答案

生成唯一ID值的机制不得受事务隔离的约束.这是数据库为每个客户端生成不同值所必需的，这优于SELECT MAX(id)+1 FROM table的窍门，如果两个客户端尝试同时分配新的id值，则会导致争用情况.

The mechanism to generate unique id values must not be subject to transaction isolation. This is required for the database to generate a distinct value for each client, better than the trick of SELECT MAX(id)+1 FROM table, which results in a race condition if two clients try to allocate new id values concurrently.

您不能使用标准SQL查询来模拟此操作(除非您使用表锁或可序列化事务).它必须是数据库引擎中内置的一种机制.

You can't simulate this operation using standard SQL queries (unless you use table locks or serializable transactions). It has to be a mechanism built into the database engine.

直到SQL:2003，ANSI SQL才描述用于生成代理键唯一值的操作.在此之前，没有自动递增色谱柱的标准，因此几乎每个品牌的RDBMS都提供了一些专有的解决方案.自然，它们相差很大，并且无法以简单的，独立于数据库的方式使用它们.

ANSI SQL did not describe an operation to generate unique values for surrogate keys until SQL:2003. Before that, there was no standard for auto-incrementing columns, so nearly every brand of RDBMS provided some proprietary solution. Naturally they vary a lot, and there's no way to use them in a simple, database-independent manner.

• MySQL具有AUTO_INCREMENT列选项，或SERIAL伪数据类型，其等效于BIGINT UNSIGNED AUTO_INCREMENT;
• Microsoft SQL Server具有IDENTITY列选项和NEWSEQUENTIALID()，介于自动增量和GUID之间；
• Oracle有一个SEQUENCE对象；
• PostgreSQL具有SEQUENCE对象或SERIAL伪数据类型，该伪数据类型根据命名约定隐式创建序列对象；
• InterBase/Firebird有一个GENERATOR对象，该对象非常类似于Oracle中的SEQUENCE. Firebird 2.1也支持SEQUENCE;
• SQLite将声明为您的主键的任何整数视为隐式自动递增；
• DB2 UDB几乎包含所有内容:SEQUENCE对象，或者您可以使用"GEN_ID"选项声明列.

• MySQL has the AUTO_INCREMENT column option, or SERIAL pseudo-datatype which is equivalent to BIGINT UNSIGNED AUTO_INCREMENT;
• Microsoft SQL Server has the IDENTITY column option and NEWSEQUENTIALID() which is something between auto-increment and GUID;
• Oracle has a SEQUENCE object;
• PostgreSQL has a SEQUENCE object, or SERIAL pseudo-datatype which implicitly creates a sequence object according to a naming convention;
• InterBase/Firebird has a GENERATOR object which is pretty much like a SEQUENCE in Oracle; Firebird 2.1 supports SEQUENCE too;
• SQLite treats any integer declared as your primary key as implicitly auto-incrementing;
• DB2 UDB has just about everything: SEQUENCE objects, or you can declare columns with the "GEN_ID" option.

所有这些机制都在事务隔离之外运行，以确保并发客户端获得唯一的值.同样，在所有情况下，都可以查询当前会话的最新生成的值 .必须有，因此您可以使用它在子表中插入行.

All these mechanisms operate outside transaction isolation, ensuring that concurrent clients get unique values. Also in all cases there is a way to query the most recently generated value for your current session. There has to be, so you can use it to insert rows in a child table.

这篇关于没有自动增量字段的数据库上的自动增量字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！