問題描述

我正在尋找一種在 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 用于數(shù)據(jù)導(dǎo)入.我們有一個(gè)中間表，其中包含我們從中生成記錄的 ID，我們稍后在導(dǎo)入時(shí)加入這些 ID.不幸的是，現(xiàn)在其中一些記錄會(huì)在下一個(gè)表中生成幾條記錄，因此我們需要一個(gè)可重現(xiàn)的新 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 的方式對(duì)于 SQL Server 是不正確的，因?yàn)?Guid 是一個(gè)在字節(jié)組中具有不同字節(jié)順序的結(jié)構(gòu)，請(qǐng)查看:http:///sqlblog.com/blogs/alberto_ferrari/archive/2007/08/31/how-are-guids-sorted-by-sql-server.aspx并注意以下幾點(diǎn):

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:

現(xiàn)在，當(dāng)我運(yùn)行修改后的 Alberto 的查詢時(shí)，我得到以下序列: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 的字節(jié) #3 是最不重要的，而 GUID 的字節(jié) #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].

這是一個(gè)簡單的函數(shù)來增加唯一標(biāo)識(shí)符:

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 

請(qǐng)注意，字節(jié) 6 和 7 不會(huì)遞增，因?yàn)樗鼈儼?Guid 版本位.但正如其他人指出的那樣，您確實(shí)不應(yīng)該這樣做.在您的情況下，如果您為這些 Guid 創(chuàng)建一個(gè)臨時(shí)表可能會(huì)更好(有兩列:一個(gè)整數(shù)作為索引，第二個(gè)帶有生成的 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).

這篇關(guān)于在 TSQL 中增加唯一標(biāo)識(shí)符的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！