如何在H2数据库中适配MySQL模式？解决SQLGrammarException问题

问题原因 #

H2数据库对CTE（WITH子句）的支持依赖于版本和正确配置，旧版本或配置不当会导致语法解析失败，而MySQL 8.0原生支持CTE所以运行正常。

解决方案 #

1. 升级H2数据库版本

早期版本的H2（如1.4.200之前）对CTE的支持不完善，建议升级到最新稳定版。如果使用Maven，修改依赖：

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <version>2.2.224</version> <!-- 替换为最新稳定版 -->
    <scope>test</scope>
</dependency>

2. 调整H2 JDBC URL参数

在测试环境的application.properties中，修改数据库连接URL，添加必要的兼容参数：

spring.datasource.url=jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;MODE=MYSQL;DATABASE_TO_UPPER=FALSE;NON_KEYWORDS=CTE

• DATABASE_TO_UPPER=FALSE：避免H2自动将表名转为大写，保持与MySQL一致的大小写行为
• NON_KEYWORDS=CTE：将CTE标记为非关键字，防止H2解析时冲突

3. 修改Hibernate方言

将原有的H2方言替换为MySQL 8.0方言，让Hibernate生成更兼容的SQL：

spring.jpa.database-platform=org.hibernate.dialect.MySQL8Dialect

H2在MODE=MYSQL模式下可以很好地兼容MySQL方言的语法规则，包括CTE的解析。

4. 验证SQL语句兼容性

你的SQL语句是标准CTE写法，无需调整，但可以直接在H2控制台（/h2-console/）执行该语句，排查具体语法错误提示。

测试验证 #

修改上述配置后，重新运行测试用例，检查SQLGrammarException是否消失。

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