如何将SQL Server中存储的VarBinary(max)数据转换为整数列表

核心逻辑说明 #

C#中BitConverter.GetBytes(int)默认输出小端序4字节二进制，存入varbinary(max)时是按int顺序拼接的4字节块，SQL端只需要按每4字节拆分后按小端序转int即可。

可直接运行的查询方案（无需自定义函数） #

兼容所有SQL Server版本（递归CTE实现） #

WITH NumberSeries AS (
    SELECT 0 AS Offset
    UNION ALL
    SELECT Offset + 4
    FROM NumberSeries
    WHERE Offset + 4 <= DATALENGTH([Data])
)
SELECT 
    Offset/4 AS IntIndex, -- 对应原int数组的下标，可做Grafana的X轴
    CAST(
        SUBSTRING([Data], Offset + 1, 1) +
        SUBSTRING([Data], Offset + 2, 1) +
        SUBSTRING([Data], Offset + 3, 1) +
        SUBSTRING([Data], Offset + 4, 1)
    AS INT) AS IntValue -- 对应原int数值，可做Grafana的Y轴
FROM RawData
CROSS APPLY NumberSeries
WHERE RawDataId = 1
-- int数组长度超过25的话需要调整递归上限，最大可设为0表示无限制
OPTION (MAXRECURSION 10000);

SQL Server 2022及以上版本简化写法（性能更高） #

SELECT 
    value/4 AS IntIndex,
    CAST(
        SUBSTRING([Data], value + 1, 1) +
        SUBSTRING([Data], value + 2, 1) +
        SUBSTRING([Data], value + 3, 1) +
        SUBSTRING([Data], value + 4, 1)
    AS INT) AS IntValue
FROM RawData
CROSS APPLY GENERATE_SERIES(0, DATALENGTH([Data]) - 1, 4)
WHERE RawDataId = 1;

自定义表值函数方案（可复用） #

如果允许创建自定义函数，可封装为通用转换函数，实现你期望的调用形式：

1. 先创建函数

CREATE FUNCTION dbo.SplitBinaryToInts(@BinaryData VARBINARY(MAX))
RETURNS TABLE AS RETURN
(
    WITH NumberSeries AS (
        SELECT 0 AS Offset
        UNION ALL
        SELECT Offset + 4
        FROM NumberSeries
        WHERE Offset +4 <= DATALENGTH(@BinaryData)
    )
    SELECT 
        Offset/4 AS IntIndex,
        CAST(
            SUBSTRING(@BinaryData, Offset +1,1) +
            SUBSTRING(@BinaryData, Offset +2,1) +
            SUBSTRING(@BinaryData, Offset +3,1) +
            SUBSTRING(@BinaryData, Offset +4,1)
        AS INT) AS IntValue
    FROM NumberSeries
    WHERE DATALENGTH(@BinaryData) %4 = 0 -- 校验输入为4字节整数倍，过滤脏数据
)

2. 调用方式和你期望的逻辑完全一致

SELECT * FROM RawData 
CROSS APPLY dbo.SplitBinaryToInts(RawData.Data) 
WHERE RawDataId = 1;

注意事项 #

• 如果C#代码中手动将字节序转为大端存储，只需将CAST内的SUBSTRING顺序反过来，从第4字节到第1字节拼接即可
• Grafana使用时直接将IntIndex设为X轴序列维度、IntValue设为Y轴数值维度即可生成趋势图

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