Access中单个/多个空格与空字符串判定相等的原因咨询

复现场景 #

运行以下VBA代码时，预期查询无匹配记录、rs.EOF返回值为True，实际返回了插入的空字符串记录。实际测试发现Access中任意数量的空格都会被判定为互相相等，空字符串与空格比较也会判定为相等：

Sub tmp()
    Dim rs As DAO.Recordset
    CurrentDb.Execute "create table table1 (id  counter, field1  text(10),primary key (id))"
    CurrentDb.Execute "insert into table1 (field1) values ('')"
    Set rs = CurrentDb.OpenRecordset("select * from table1 where field1 =' '", dbOpenSnapshot)
    Debug.Print rs.EOF
    rs.Close
End Sub

核心原因 #

这个现象是Access默认搭载的Jet/ACE数据库引擎的既定文本比较规则导致，不属于程序bug：

• Access对所有文本类型字段做等值比较时，会遵循ANSI SQL标准中固定长度字符串的空格填充逻辑：比较两个字符串前，会自动给长度更短的字符串末尾补全空格，直到两个字符串长度完全一致，再逐字符对比内容是否相同。
• 注意：该规则和字段设置为固定长度还是可变长度无关，是引擎层面的全局默认比较逻辑。
• 按照这个规则推导：
  • 空字符串''长度为0，和单空格字符串' '比较时，会先被补1个空格，补全后的内容和单空格完全一致，因此判定相等
  • 长度为N的全空格字符串和长度为M的全空格字符串比较时，较短的一方会被补空格到和长字符串相同长度，补全后所有字符均为空格，因此判定任意数量的空格互相相等
• 上述测试场景中，插入的field1值是空字符串，查询条件为field1 = ' '，引擎按规则补全空格后判定两者匹配，因此返回了该条记录，rs.EOF返回False，和预期结果不符。

严格匹配的实现方法 #

如果需要精准区分空串、不同长度的空格字符串，可以通过以下两种方式绕开默认的空格填充规则：

• 给待比较的字符串末尾拼接一个非空格的哨兵字符后再做对比，例如将查询条件写为where field1 & 'x' = ' ' & 'x'，拼接后尾部空格会被纳入比较范围，不会被自动填充逻辑忽略
• 先通过长度函数过滤长度不一致的记录，再做等值比较，例如查询条件写为where Len(field1) = Len(' ') and field1 = ' '，即可得到严格匹配的结果

内容的提问来源于stack exchange，提问作者Jörgen R