問題描述

我正在尋找一種在 TSQL 中將 uniqueidentifier 增加 1 的方法.例如，如果id是A6BC60AD-A4D9-46F4-A7D3-98B2A7237A9E，我希望能夠選擇A6BC60AD-A4D9-46F4-A7D3-98B2A7237A9F.

I am looking for a way to increment a uniqueidentifier by 1 in TSQL. For example, if the id is A6BC60AD-A4D9-46F4-A7D3-98B2A7237A9E, I'd like to be able to select A6BC60AD-A4D9-46F4-A7D3-98B2A7237A9F.

@rein 用于數據導入.我們有一個中間表，其中包含我們從中生成記錄的 ID，我們稍后在導入時加入這些 ID.不幸的是，現在其中一些記錄會在下一個表中生成幾條記錄，因此我們需要一個可重現的新 ID.

@rein It's for a data import. We have an intermediate table with IDs that we're generating records from, and we join on those IDs later in the import. Unfortunately, now some of those records generate a couple of records in the next table, so we need a new id that is reproducible.

推薦答案

你想要增加 Guid 的方式對于 SQL Server 是不正確的，因為 Guid 是一個在字節組中具有不同字節順序的結構，請查看:http:///sqlblog.com/blogs/alberto_ferrari/archive/2007/08/31/how-are-guids-sorted-by-sql-server.aspx并注意以下幾點:

The way you want to increment Guid is not correct for SQL Server as Guid is a structure with different byte order in the byte groups, please have a look at: http://sqlblog.com/blogs/alberto_ferrari/archive/2007/08/31/how-are-guids-sorted-by-sql-server.aspx and notice the following:

現在，當我運行修改后的 Alberto 的查詢時，我得到以下序列:3, 2, 1, 0, 5, 4, 7, 6, 9, 8, 15, 14, 13, 12, 11, 10

Now, when I run modified Alberto's query, I'm getting the following sequence: 3, 2, 1, 0, 5, 4, 7, 6, 9, 8, 15, 14, 13, 12, 11, 10

這意味著，GUID 的字節 #3 是最不重要的，而 GUID 的字節 #10 是最重要的 [從 SQL Server ORDER BY 子句的角度來看].

That means, that GUID's byte #3 is the least significant and GUID's byte #10 is the most significant [from SQL Server ORDER BY clause perspective].

這是一個簡單的函數來增加唯一標識符:

Here is simple function to increment a uniqueidentifier accounting for this:

create function [dbo].[IncrementGuid](@guid uniqueidentifier) 
returns uniqueidentifier 
as 
begin 
declare @guid_binary binary(16), @b03 binary(4), @b45 binary(2), @b67 binary(2), @b89 binary(2), @bAF binary(6)

select @guid_binary = @guid

select @b03 = convert(binary(4), reverse(substring(@guid_binary,1,4)))
select @b45 = convert(binary(2), reverse(substring(@guid_binary,5,2)))
select @b67 = convert(binary(2), reverse(substring(@guid_binary,7,2)))
select @b89 = convert(binary(2), substring(@guid_binary,9,2))
select @bAF = convert(binary(6), substring(@guid_binary,11,6))

if (@b03 < 'FFFFFFFF')
begin
    select @b03 = convert(binary(4), cast(@b03 as int) + 1)
end
else if (@b45 < 'FFFF')
begin
    select @b45 = convert(binary(2), cast(@b45 as int) + 1)
end
else if (@b89 < 'FFFF')
begin
    select @b89 = convert(binary(2), cast(@b89 as int) + 1)
end
else
begin
    select @bAF = convert(binary(6), cast(@bAF as bigint) + 1)
end

return convert(binary(16), reverse(convert(char(4),@b03)) + reverse(convert(char(2),@b45)) + reverse(convert(char(2),@b67)) + convert(char(2),@b89) + convert(char(6),@bAF))
end 

請注意，字節 6 和 7 不會遞增，因為它們包含 Guid 版本位.但正如其他人指出的那樣，您確實不應該這樣做.在您的情況下，如果您為這些 Guid 創建一個臨時表可能會更好(有兩列:一個整數作為索引，第二個帶有生成的 Guid).

Note that bytes 6 and 7 are not incremented as they contain the Guid version bits. But as others has pointed you really should not be doing this. In your case it might be better if you create a temp table for these Guids (with two columns: one integer as index and second one with generated Guids).

這篇關于在 TSQL 中增加唯一標識符的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！