MySQL正常执行的SQL在Spring Boot HSQL单元测试中触发BadSqlGrammarException

嗨，我之前也遇到过一模一样的问题！你代码里的doProessing方法用的是MySQL特有的多表DELETE语法，手动测试时用的应该是MySQL或者其他兼容这种语法的数据库，所以运行正常，但HSQLDB（Spring Boot默认的测试内存数据库）并不支持这种写法，这就是抛出异常的原因。

问题根源 #

你写的DELETE语句是MySQL风格的：

String sql = "DELETE BS FROM FOO.TABLE1 BS JOIN FOO.TABLE2 BM on BM.SURR_KEY = BS.SURR_KEY WHERE BM.NAME = ?";

HSQLDB遵循ANSI SQL标准，不支持这种DELETE 别名 FROM 表 别名 JOIN ...的写法，它只认可标准的单表DELETE或者结合子查询的删除方式。

解决方案一：改用HSQL支持的通用SQL语法 #

把原SQL改成用子查询替换JOIN的方式，既兼容HSQL，也能在MySQL等主流数据库上正常运行：

String sql = "DELETE FROM FOO.TABLE1 BS WHERE BS.SURR_KEY IN (SELECT BM.SURR_KEY FROM FOO.TABLE2 BM WHERE BM.NAME = ?)";

这种写法是ANSI SQL标准，几乎所有关系型数据库都支持，通用性更强。

解决方案二：换成H2数据库并开启MySQL兼容模式 #

如果不想修改SQL，可以把单元测试用的数据库换成H2，并配置它模拟MySQL的语法规则：

1. 先在pom.xml（Maven）里引入H2依赖：

<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>test</scope>
</dependency>

2. 在测试环境配置文件（比如src/test/resources/application-test.properties）里添加H2配置：

spring.datasource.url=jdbc:h2:mem:testdb;MODE=MySQL;DB_CLOSE_DELAY=-1
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=

这样H2会完全模拟MySQL的语法，你原来的DELETE JOIN语句就能正常执行了。

补充：测试数据初始化建议 #

不管用哪种方案，记得在测试前初始化对应的测试数据，确保测试场景和生产一致。比如用@BeforeEach注解插入测试记录：

@SpringBootTest
@Transactional
class ProcessingServiceTest {

    @Autowired
    private ProcessingService processingService;

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @BeforeEach
    void setUpTestData() {
        // 先插入TABLE2的关联记录
        jdbcTemplate.update("INSERT INTO FOO.TABLE2 (SURR_KEY, NAME) VALUES (1, 'TEST_NAME')");
        // 插入TABLE1的待删除记录
        jdbcTemplate.update("INSERT INTO FOO.TABLE1 (SURR_KEY) VALUES (1)");
    }

    @Test
    void doProcessing_ShouldDeleteMatchingRecords() {
        int affectedRows = processingService.doProessing("test_name");
        // 验证删除了1条记录
        assertEquals(1, affectedRows);
        
        // 确认TABLE1里的关联记录已被删除
        Integer remainingCount = jdbcTemplate.queryForObject(
            "SELECT COUNT(*) FROM FOO.TABLE1 WHERE SURR_KEY = 1", 
            Integer.class
        );
        assertEquals(0, remainingCount);
    }
}

这样调整后，你的单元测试应该就能正常通过了～

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