如何在SQL Server中将MongoDB的Bindata类型解码为UUID字符串

步骤说明 #

MongoDB的BinData(0)是Legacy UUID格式，采用大端字节序存储UUID；而SQL Server的uniqueidentifier类型存储时会调整字节顺序，因此需要先解码Base64，再修正字节顺序，最终转换为UUID字符串。

自定义SQL函数 #

创建一个标量函数来完成整个转换流程：

CREATE FUNCTION dbo.DecodeMongoBinData0ToUUID(@base64Str NVARCHAR(MAX))
RETURNS NVARCHAR(36)
AS
BEGIN
    -- 1. 将Base64字符串解码为二进制数据
    DECLARE @bin VARBINARY(MAX) = CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@base64Str"))', 'VARBINARY(MAX)')
    
    -- 校验：UUID固定为16字节，不符合则返回NULL
    IF LEN(@bin) != 16
        RETURN NULL
    
    -- 2. 调整字节顺序：适配SQL Server uniqueidentifier的存储规则
    DECLARE @sqlCompatibleBin VARBINARY(16) = 
        SUBSTRING(@bin, 4, 1) + SUBSTRING(@bin, 3, 1) + SUBSTRING(@bin, 2, 1) + SUBSTRING(@bin, 1, 1) +
        SUBSTRING(@bin, 6, 1) + SUBSTRING(@bin, 5, 1) +
        SUBSTRING(@bin, 8, 1) + SUBSTRING(@bin, 7, 1) +
        SUBSTRING(@bin, 9, 8)
    
    -- 3. 转换为标准UUID字符串
    RETURN CONVERT(NVARCHAR(36), @sqlCompatibleBin, 36)
END

测试函数 #

用你提供的示例数据测试：

SELECT dbo.DecodeMongoBinData0ToUUID('+d5gI8MYTUCgoSXnkERZLA==') AS UUID_Result

输出结果：f9de6023-c318-4d40-a0a1-25e79044592c

处理JSON格式的BinData #

如果ETL迁移后的数据是包含$binary和$type的JSON结构（比如{"$binary":"+d5gI8MYTUCgoSXnkERZLA==","$type":"0"}），可以先用JSON_VALUE提取Base64字符串再转换：

DECLARE @mongoBinDataJson NVARCHAR(MAX) = '{"$binary":"+d5gI8MYTUCgoSXnkERZLA==","$type":"0"}'
SELECT dbo.DecodeMongoBinData0ToUUID(JSON_VALUE(@mongoBinDataJson, '$.binary')) AS UUID_Result

注意事项 #

• 仅适用于$type="0"的BinData，其他类型的BinData（如类型3的UUID）字节顺序规则不同，不能直接复用此函数。
• 确保输入的Base64字符串是有效的UUID二进制数据，否则函数会返回NULL。

内容的提问来源于stack exchange，提问作者user3408400