可以通过简单的 SQL INSERT 实现手动增量吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/794577/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Possible to implement a manual increment with just simple SQL INSERT?
提问by cdeszaq
I have a primary key that I don't want to auto increment (for various reasons) and so I'm looking for a way to simply increment that field when I INSERT. By simply, I mean without stored procedures and without triggers, so just a series of SQL commands (preferably one command).
我有一个不想自动增加的主键（出于各种原因），所以我正在寻找一种方法来在我插入时简单地增加该字段。简单地说，我的意思是没有存储过程和触发器，所以只有一系列 SQL 命令（最好是一个命令）。
Here is what I have tried thus far:
这是我迄今为止尝试过的：
BEGIN TRAN
INSERT INTO Table1(id, data_field)
VALUES ( (SELECT (MAX(id) + 1) FROM Table1), '[blob of data]');
COMMIT TRAN;
* Data abstracted to use generic names and identifiers
However, when executed, the command errors, saying that
但是，当执行时，命令出错，说
"Subqueries are not allowed in this context. only scalar expressions are allowed"
“在此上下文中不允许使用子查询。只允许使用标量表达式”
So, how can I do this/what am I doing wrong?
那么，我该怎么做/我做错了什么？
EDIT: Since it was pointed out as a consideration, the table to be inserted into is guaranteed to have at least 1 row already.
编辑：由于它被指出作为考虑因素，因此要插入的表保证已经至少有 1 行。
回答by SQLMenace
You understand that you will have collisions right?
你明白你会发生碰撞吗？
you need to do something like this and this might cause deadlocks so be very sure what you are trying to accomplish here
你需要做这样的事情，这可能会导致死锁，所以要非常确定你要在这里完成什么
DECLARE @id int
BEGIN TRAN
SELECT @id = MAX(id) + 1 FROM Table1 WITH (UPDLOCK, HOLDLOCK)
INSERT INTO Table1(id, data_field)
VALUES (@id ,'[blob of data]')
COMMIT TRAN
To explain the collision thing, I have provided some code
为了解释碰撞的事情，我提供了一些代码
first create this table and insert one row
首先创建此表并插入一行
CREATE TABLE Table1(id int primary key not null, data_field char(100))
GO
Insert Table1 values(1,'[blob of data]')
Go
Now open up two query windows and run this at the same time
现在打开两个查询窗口并同时运行
declare @i int
set @i =1
while @i < 10000
begin
BEGIN TRAN
INSERT INTO Table1(id, data_field)
SELECT MAX(id) + 1, '[blob of data]' FROM Table1
COMMIT TRAN;
set @i =@i + 1
end
You will see a bunch of these
你会看到一堆这些
Server: Msg 2627, Level 14, State 1, Line 7 Violation of PRIMARY KEY constraint 'PK__Table1__3213E83F2962141D'. Cannot insert duplicate key in object 'dbo.Table1'. The statement has been terminated.
服务器：消息 2627，级别 14，状态 1，第 7 行违反 PRIMARY KEY 约束“PK__Table1__3213E83F2962141D”。无法在对象“dbo.Table1”中插入重复键。该语句已终止。
回答by Eric Petroelje
Try this instead:
试试这个：
INSERT INTO Table1 (id, data_field)
SELECT id, '[blob of data]' FROM (SELECT MAX(id) + 1 as id FROM Table1) tbl
I wouldn't recommend doing it that way for any number of reasons though (performance, transaction safety, etc)
但出于多种原因（性能、交易安全等），我不建议这样做
回答by Jon
It could be because there are no records so the sub query is returning NULL...try
可能是因为没有记录，所以子查询返回 NULL...try
INSERT INTO tblTest(RecordID, Text)
VALUES ((SELECT ISNULL(MAX(RecordID), 0) + 1 FROM tblTest), 'asdf')
回答by Vladimir
I don't know if somebody is still looking for an answer but here is a solution that seems to work:
我不知道是否有人仍在寻找答案，但这里有一个似乎有效的解决方案：
-- Preparation: execute only once
CREATE TABLE Test (Value int)
CREATE TABLE Lock (LockID uniqueidentifier)
INSERT INTO Lock SELECT NEWID()
-- Real insert
BEGIN TRAN LockTran
-- Lock an object to block simultaneous calls.
UPDATE Lock WITH(TABLOCK)
SET LockID = LockID
INSERT INTO Test
SELECT ISNULL(MAX(T.Value), 0) + 1
FROM Test T
COMMIT TRAN LockTran
回答by Joe
We have a similar situation where we needed to increment and could not have gaps in the numbers. (If you use an identity value and a transaction is rolled back, that number will not be inserted and you will have gaps because the identity value does not roll back.)
我们有一个类似的情况，我们需要递增并且数字不能有差距。（如果您使用身份值并且回滚事务，则不会插入该数字并且您将有间隙，因为身份值不会回滚。）
We created a separate table for last number used and seeded it with 0.
我们为最后使用的数字创建了一个单独的表，并用 0 播种。
Our insert takes a few steps.
我们的插入需要几个步骤。
--increment the number Update dbo.NumberTable set number = number + 1
--增加数字更新dbo.NumberTable set number = number + 1
--find out what the incremented number is select @number = number from dbo.NumberTable
--找出增加的数字是什么 select @number = number from dbo.NumberTable
--use the number insert into dbo.MyTable using the @number
--使用@number将数字插入到dbo.MyTable中
commit or rollback
提交或回滚
This causes simultaneous transactions to process in a single line as each concurrent transaction will wait because the NumberTable is locked. As soon as the waiting transaction gets the lock, it increments the current value and locks it from others. That current value is the last number used and if a transaction is rolled back, the NumberTable update is also rolled back so there are no gaps.
这会导致并发事务在一行中处理，因为每个并发事务都将等待，因为 NumberTable 被锁定。一旦等待的事务获得锁，它就会增加当前值并从其他事务中锁定它。该当前值是最后使用的数字，如果事务回滚，则 NumberTable 更新也会回滚，因此没有间隙。
Hope that helps.
希望有帮助。
Another way to cause single file execution is to use a SQL application lock. We have used that approach for longer running processes like synchronizing data between systems so only one synchronizing process can run at a time.
导致单个文件执行的另一种方法是使用 SQL 应用程序锁。我们已将这种方法用于运行时间较长的进程，例如在系统之间同步数据，因此一次只能运行一个同步进程。
回答by Kipp Woodard
Any critiques of this? Works for me.
对此有何批评？对我来说有效。
DECLARE @m_NewRequestID INT
, @m_IsError BIT = 1
, @m_CatchEndless INT = 0
WHILE @m_IsError = 1
BEGIN TRY
SELECT @m_NewRequestID = (SELECT ISNULL(MAX(RequestID), 0) + 1 FROM Requests)
INSERT INTO Requests ( RequestID
, RequestName
, Customer
, Comment
, CreatedFromApplication)
SELECT RequestID = @m_NewRequestID
, RequestName = dbo.ufGetNextAvailableRequestName(PatternName)
, Customer = @Customer
, Comment = [Description]
, CreatedFromApplication = @CreatedFromApplication
FROM RequestPatterns
WHERE PatternID = @PatternID
SET @m_IsError = 0
END TRY
BEGIN CATCH
SET @m_IsError = 1
SET @m_CatchEndless = @m_CatchEndless + 1
IF @m_CatchEndless > 1000
THROW 51000, '[upCreateRequestFromPattern]: Unable to get new RequestID', 1
END CATCH
回答by somenickname
How about creating a separate table to maintain the counter? It has better performance than MAX(id), as it will be O(1). MAX(id)is at best O(lgn) depending on the implementation.
创建一个单独的表来维护计数器怎么样？它比 具有更好的性能MAX(id)，因为它将是 O(1)。 MAX(id)取决于实现，最多为 O(lgn)。
And then when you need to insert, simply lock the counter table for reading the counter and increment the counter. Then you can release the lock and insert to your table with the incremented counter value.
然后当您需要插入时，只需锁定计数器表以读取计数器并增加计数器。然后您可以释放锁并使用递增的计数器值插入到您的表中。
回答by Jon Masters
declare @nextId int
set @nextId = (select MAX(id)+1 from Table1)
insert into Table1(id, data_field) values (@nextId, '[blob of data]')
commit;
But perhaps a better approach would be using a scalar function getNextId('table1')
但也许更好的方法是使用标量函数 getNextId('table1')
回答by Garrett
It seems very odd to do this sort of thing w/o an IDENTITY (auto-increment) column, making me question the architecture itself. I mean, seriously, this is the perfect situation for an IDENTITY column. It might help us answer your question if you'd explain the reasoning behind this decision. =)
在没有 IDENTITY（自动增量）列的情况下做这种事情似乎很奇怪，这让我质疑架构本身。我的意思是，说真的，这是 IDENTITY 列的完美情况。如果您能解释这个决定背后的原因，它可能会帮助我们回答您的问题。=)
Having said that, some options are:
话虽如此，一些选择是：
- using an INSTEAD OF trigger for this purpose. So, you'd do your INSERT (the INSERT statement would not need to pass in an ID). The trigger code would handle inserting the appropriate ID. You'd need to use the WITH (UPDLOCK, HOLDLOCK) syntax used by another answerer to hold the lock for the duration of the trigger (which is implicitly wrapped in a transaction) & to elevate the lock type from "shared" to "update" lock (IIRC).
- you can use the idea above, but have a table whose purpose is to store the last, max value inserted into the table. So, once the table is set up, you would no longer have to do a SELECT MAX(ID) every time. You'd simply increment the value in the table. This is safe provided that you use appropriate locking (as discussed). Again, that avoids repeated table scans every time you INSERT.
- use GUIDs instead of IDs. It's much easier to merge tables across databases, since the GUIDs will always be unique (whereas records across databases will have conflicting integer IDs). To avoid page splitting, sequential GUIDs can be used. This is only beneficial if you might need to do database merging.
- Use a stored proc in lieu of the trigger approach (since triggers are to be avoided, for some reason). You'd still have the locking issue (and the performance problems that can arise). But sprocs are preferred over dynamic SQL (in the context of applications), and are often much more performant.
- 为此目的使用 INSTEAD OF 触发器。因此，您将执行 INSERT（INSERT 语句不需要传入 ID）。触发代码将处理插入适当的 ID。您需要使用其他应答者使用的 WITH (UPDLOCK, HOLDLOCK) 语法在触发器的持续时间内保持锁定（隐式包装在事务中）& 将锁定类型从“共享”提升到“更新” " 锁 (IIRC)。
- 您可以使用上面的想法，但有一个表，其目的是存储插入表中的最后一个最大值。因此，一旦建立了表，您就不再需要每次都执行 SELECT MAX(ID) 。您只需增加表中的值。如果您使用适当的锁定（如上所述），这是安全的。同样，这避免了每次插入时重复的表扫描。
- 使用 GUID 而不是 ID。跨数据库合并表要容易得多，因为 GUID 将始终是唯一的（而跨数据库的记录将具有冲突的整数 ID）。为避免页面拆分，可以使用顺序 GUID。这仅在您可能需要进行数据库合并时才有用。
- 使用存储过程代替触发器方法（因为出于某种原因要避免触发器）。您仍然会遇到锁定问题（以及可能出现的性能问题）。但是 sproc 优于动态 SQL（在应用程序的上下文中），并且通常性能更高。
Sorry about rambling. Hope that helps.
抱歉跑题了。希望有帮助。
回答by SqlRyan
If you're doing it in a trigger, you could make sure it's an "INSTEAD OF" trigger and do it in a couple of statements:
如果您在触发器中执行此操作，则可以确保它是一个“INSTEAD OF”触发器并在几个语句中执行此操作：
DECLARE @next INT
SET @next = (SELECT (MAX(id) + 1) FROM Table1)
INSERT INTO Table1
VALUES (@next, inserted.datablob)
The only thing you'd have to be careful about is concurrency - if two rows are inserted at the same time, they could attempt to use the same value for @next, causing a conflict.
您唯一需要注意的是并发性 - 如果同时插入两行，它们可能会尝试对 @next 使用相同的值，从而导致冲突。
Does this accomplish what you want?
这能实现你想要的吗？
