node-mssql调用SQL Server存储过程大二进制数据换行截断问题

问题根源 #

当传递超过65535字节的二进制数据时，node-mssql若自动推断参数类型，会将Buffer转换为字符串格式传递，SQL Server对这类长字符串会自动换行分割，导致存储过程执行失败。

解决方案 #

1. 修正存储过程的参数类型 #

确保存储过程中接收图片的参数定义为VARBINARY(MAX)，而非固定长度的VARBINARY(n)（n≤65535），这样才能支持超过65535字节的二进制数据：

ALTER PROCEDURE [dbo].[update-images]
    @photo VARBINARY(MAX),
    @signature VARBINARY(MAX),
    @personId UNIQUEIDENTIFIER,
    @captureDate DATETIME,
    @meta1 INT,
    @meta2 INT
AS
BEGIN
    SET NOCOUNT ON;
    -- 你的插入/更新逻辑
    IF EXISTS(SELECT 1 FROM [person-images] WHERE PersonId = @personId)
    BEGIN
        UPDATE [person-images]
        SET photo = @photo, signature = @signature, CaptureDate = @captureDate, Meta1 = @meta1, Meta2 = @meta2
        WHERE PersonId = @personId
    END
    ELSE
    BEGIN
        INSERT INTO [person-images] (PersonId, photo, signature, CaptureDate, Meta1, Meta2)
        VALUES (@personId, @photo, @signature, @captureDate, @meta1, @meta2)
    END
END

2. 在node-mssql调用时明确指定参数类型 #

调用存储过程时，必须显式将二进制参数的类型指定为sql.VarBinary(sql.MAX)，避免库自动推断导致的字符串化问题：

const sql = require('mssql');
const express = require('express');
const app = express();

// 根据请求格式解析body，这里假设前端传递JSON格式的base64数据
app.use(express.json());

app.post('/update-person-images', async (req, res) => {
    try {
        const { photoBase64, signatureBase64, personId, captureDate, meta1, meta2 } = req.body;

        // 将base64字符串转换为Buffer
        const photoBuffer = Buffer.from(photoBase64, 'base64');
        const signatureBuffer = Buffer.from(signatureBase64, 'base64');

        // 建立SQL Server连接
        await sql.connect({
            server: '你的服务器地址',
            database: '你的数据库名',
            user: '用户名',
            password: '密码',
            options: {
                encrypt: true // 如果是Azure SQL需要开启，本地实例可根据情况调整
            }
        });

        // 创建请求对象，显式指定每个参数的类型
        const request = new sql.Request();
        request.input('photo', sql.VarBinary(sql.MAX), photoBuffer);
        request.input('signature', sql.VarBinary(sql.MAX), signatureBuffer);
        request.input('personId', sql.UniqueIdentifier, personId);
        request.input('captureDate', sql.DateTime, new Date(captureDate));
        request.input('meta1', sql.Int, meta1);
        request.input('meta2', sql.Int, meta2);

        // 执行存储过程
        await request.execute('update-images');

        res.status(200).json({ success: true, msg: '图片数据更新成功' });
    } catch (err) {
        console.error('执行错误:', err);
        res.status(500).json({ success: false, error: err.message });
    } finally {
        // 确保连接关闭
        await sql.close();
    }
});

const PORT = process.env.PORT || 3000;
app.listen(PORT, () => {
    console.log(`API服务运行在端口 ${PORT}`);
});

3. 升级node-mssql到最新稳定版 #

旧版本的node-mssql在处理大二进制参数时可能存在bug，执行以下命令升级：

npm update mssql

关键说明 #

显式指定参数类型后，node-mssql会通过SQL Server的二进制协议传递数据，而非将Buffer转换为字符串，从根源上避免了长二进制数据被换行分割的问题，同时VARBINARY(MAX)支持最大2GB的二进制数据，完全满足图片存储需求。

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