c# - 在 Sql Server 2008 中获取 Guid.getHashCode() 的等效值

Question

我试图在 SQL Server 2008 中获取 guid.getHashCode() 的等效 int 值。我已经尝试过 CHECKSUM(uniqueidentifier var) 但它没有返回相同的值。是否可以在 SQL Server 中获取 guid 的 HashCode 值？

例如

Guid guid = new Guid("A0AE0446-3C50-479A-A909-3BA9C711007E"); 
int hash = guid.GetHashCode();, 

返回-1476508766，是否可以在sql server中得到相同的值？

Answer 1

为此，您需要了解 Guid.GetHashCode() 如何计算该值。使用 DotPeek:

public override int GetHashCode()
{
  return this._a ^ ((int) this._b << 16 | (int) (ushort) this._c) ^ ((int) this._f << 24 | (int) this._k);
}

私有(private)变量保存 Guid 的值并声明为:

private int _a;
private short _b;
private short _c;
private byte _d;
private byte _e;
private byte _f;
private byte _g;
private byte _h;
private byte _i;
private byte _j;
private byte _k;

作为 T-SQL 函数实现:

create function dbo.GetDotNetHashCode(@guid uniqueidentifier)
returns int
as
begin

    -- calculates a hash code value equivalent to .NETs Guid.GetHashCode() method

    declare @bytes binary(16) = cast(@guid as binary(16)),
            @hashCode int

    -- when converting to binary, the byte order of the first 3 segments is reversed

    declare @_a int = convert(int, 
                            substring(@bytes, 4, 1) + 
                            substring(@bytes, 3, 1) + 
                            substring(@bytes, 2, 1) + 
                            substring(@bytes, 1, 1))

    declare @_b int = convert(smallint, 
                            substring(@bytes, 6, 1) + 
                            substring(@bytes, 5, 1))

    declare @_c int = convert(int, 
                            substring(@bytes, 8, 1) + 
                            substring(@bytes, 7, 1))

    declare @_f int = convert(int, substring(@bytes, 11, 1))
    declare @_k int = convert(int, substring(@bytes, 16, 1))

    -- shift @_b using a bigint to avoid overflow
    -- left shift 16 is equivalent to multiplying by 2^16

    declare @_b_shift bigint = cast(@_b as bigint) * 65536
    set @_b = convert(int, substring(cast(@_b_shift as varbinary(8)), 5, 4))

    -- shift @_f using a bigin to avoid overflow
    -- left shift 24 is equivalent to multiplying by 2^24

    declare @_f_shift bigint = cast(@_f as bigint) * 16777216
    set @_f = convert(int, substring(cast(@_f_shift as varbinary(8)), 5, 4))

    set @hashCode = @_a ^ (@_b | @_c) ^ (@_f | @_k)

    return @hashCode

end

并使用以下内容进行测试(注释具有调用 Guid.GetHashCode() 的预期值):

/*

aeee939b-d2c8-4411-9cf9-94ab520e3c1c : -400107626
88ed5159-7900-4530-9886-841b7d42665b : 1978471474
2910ba35-ab2f-42a5-aed1-ea710d25a42e : 1749022910
0430b8b0-981b-4f1a-82fb-afe0725e8fd3 :  858519417
ba8cd9e4-fa1d-4ead-9145-b830aea6a641 : -124676344
9d5b5203-a2d4-4b8e-86dd-7eb5e4250960 : 1099897325
28b88e48-acc4-4f74-be28-4fcb92ad8c55 : -881016471
892056e9-dde1-4999-92ba-6c74b4a583c2 :  952180658
f0c3d3b5-bffc-4bda-b45a-70825f33b01f : 1061132400
df218e55-d672-48aa-b105-47578869068d : 1314113138

*/

declare @test table (Guid uniqueidentifier)

insert @test values
    ('aeee939b-d2c8-4411-9cf9-94ab520e3c1c'),
    ('88ed5159-7900-4530-9886-841b7d42665b'),
    ('2910ba35-ab2f-42a5-aed1-ea710d25a42e'),
    ('0430b8b0-981b-4f1a-82fb-afe0725e8fd3'),
    ('ba8cd9e4-fa1d-4ead-9145-b830aea6a641'),
    ('9d5b5203-a2d4-4b8e-86dd-7eb5e4250960'),
    ('28b88e48-acc4-4f74-be28-4fcb92ad8c55'),
    ('892056e9-dde1-4999-92ba-6c74b4a583c2'),
    ('f0c3d3b5-bffc-4bda-b45a-70825f33b01f'),
    ('df218e55-d672-48aa-b105-47578869068d')

select Guid, dbo.GetDotNetHashCode(Guid) as HashCode from @test

注意:这是在 SQL Server 2012 中测试过的，但我相信它在 2008 年也能正常工作。