同一条R2DBC SQL查询在MSSQL与H2数据库中行为不一致问题咨询

这种跨数据库的行为差异在测试场景里很常见，核心原因是H2和MSSQL在函数行为、语法兼容性以及默认配置上存在不少区别，我帮你拆解几个关键问题和对应的解决方案：

1. CONCAT函数对NULL值的处理差异 #

这大概率是导致空结果的最主要原因：

• 在MSSQL中，CONCAT函数遇到NULL参数时会自动将其视为空字符串处理，比如如果d.name为NULL，CONCAT(d.name, '.', d.extension)会等价于'.' + d.extension。
• 但H2的默认CONCAT函数只要有一个参数是NULL，就会直接返回NULL。这会导致UPPER(CONCAT(...))的结果为NULL，而NULL LIKE 任意值的判断永远不成立，自然返回空结果。

修复方案：用COALESCE处理NULL值 #

修改SQL语句，把可能为NULL的字段用COALESCE转换为空字符串，确保CONCAT不会返回NULL：

@Repository
interface DocumentRepository extends R2dbcRepository<DocumentEntity, Long> {
    @Query( "SELECT d.* FROM document d " +
            "WHERE d.owner = :uuid " +
            "AND UPPER(CONCAT(COALESCE(d.name, ''), '.', COALESCE(d.extension, ''))) LIKE UPPER(CONCAT('%', :documentName, '%')) " +
            "ORDER BY d.id OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY" )
    Flux<DocumentEntity> findAllByOwner(UUID uuid, String documentName, long offset, long limit);
}

2. H2未启用MSSQL兼容模式 #

H2默认行为更贴近标准SQL，和MSSQL的语法、函数细节有不少出入——比如OFFSET/FETCH的语法支持、标识符大小写敏感性、函数逻辑等。如果没开启兼容模式，即使SQL在MSSQL中正常运行，H2也可能无法正确解析或执行。

修复方案：配置H2连接URL为MSSQL兼容模式 #

在你的测试配置中，给H2的R2DBC连接URL添加以下参数：

r2dbc:h2:mem:///testdb;DB_CLOSE_DELAY=-1;MODE=MSSQLServer;DATABASE_TO_LOWER=FALSE;CASE_INSENSITIVE_IDENTIFIERS=TRUE

• MODE=MSSQLServer：让H2模拟MSSQL的语法和函数行为
• DATABASE_TO_LOWER=FALSE：防止H2自动将表名、字段名转为小写（MSSQL默认不区分标识符大小写）
• CASE_INSENSITIVE_IDENTIFIERS=TRUE：确保字段名的大小写不影响查询匹配

3. OFFSET/FETCH语法的兼容性问题 #

部分旧版本的H2对OFFSET ... ROWS FETCH NEXT ... ONLY的支持不完善，即使开启兼容模式也可能出问题。如果上面的方案仍未解决，可以尝试将分页语法改为H2更兼容的写法：

ORDER BY d.id LIMIT :limit OFFSET :offset

替换原SQL中的OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY部分。

4. 额外调试技巧 #

如果问题依然存在，可以开启H2的SQL日志，查看实际执行的SQL语句和参数绑定情况，确认是否存在参数传递错误或表/字段名不匹配：

• 在H2连接URL中添加TRACE_LEVEL_SYSTEM_OUT=2，就能在控制台看到H2执行的所有SQL和参数细节。

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