在 TSQL 中增加唯一标识符 [英] Increment a uniqueidentifier in TSQL

问题描述

我正在寻找一种在 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 中增加唯一标识符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！