MSSQL查询varbinary列尾部零被忽略致匹配错误解决方案咨询

这个现象和ANSI_PADDING没有关系。ANSI_PADDING只控制数据插入时尾部值的保留/截断规则，不会影响等值比较的逻辑。
正常情况下SQL Server比较varbinary类型数据时，会逐字节校验内容同时校验长度，0x01020304（长度4）和0x0102030400（长度5）本身不会被判定为相等。你遇到的误匹配，核心原因是Dapper搭配SqlClient传参时没有显式指定参数类型和长度，驱动在部分场景下会把参数推导为固定长度的binary类型，传入的短字节数组会被自动在尾部补0x00到对应长度，最终和你存储的末尾带0的长字节数组命中等值条件。

加DATALENGTH判断（可用，但非最优） #

你考虑的加长度校验的写法确实可以解决问题，修改后的查询语句如下：

SELECT * FROM Token WHERE TokenId = @Bytes AND DATALENGTH(TokenId) = DATALENGTH(@Bytes)

这个写法逻辑完全正确，能100%过滤掉长度不匹配的行，但属于问题出现后的补漏逻辑，没有从参数传递的根源解决问题，虽然多出来的判断对性能几乎没有影响，但完全可以避免。

显式指定Dapper参数类型与长度（最优方案） #

直接从参数传递层面解决问题，不需要修改原有查询语句。传入Dapper参数时，显式指定参数为二进制类型、长度和列定义保持一致（16），从根源避免驱动自动补0的问题：

var param = new DynamicParameters();
param.Add("Bytes", new byte[]{1,2,3,4}, DbType.Binary, size: 16);
connection.Query<TokenRow>(selectSql, param);

显式指定类型和长度后，驱动会将参数作为varbinary(16)类型传递，不会对传入的短字节数组做尾部补0操作，原生的等值比较会自动同时匹配内容和长度，不会再出现误命中的情况。

设计层可选优化 #

你当前用1个字节对应1位PIN码数字的存储方式没有问题，不需要调整表结构。如果后续PIN码规则固定为纯数字，也可以考虑用定长char类型存储，或者在字节数组前加1字节标记PIN长度，但这些都是额外优化，不是解决当前问题的必须操作。

不要尝试通过关闭ANSI_PADDING解决问题：

• 该选项对varbinary类型的尾部0值没有截断效果，仅作用于varchar/nvarchar类型的尾部空格处理
• SQL Server后续版本已经将ANSI_PADDING=OFF标记为废弃特性，生产环境不建议依赖该配置

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