Spring Boot dev环境启动报错：H2执行MySQL风格DDL失败

我使用Spring Boot v3.3.1，项目同时引入MySQL和H2依赖（H2用于集成测试）。执行命令：

mvn spring-boot:run -Dspring-boot.run.profiles=dev

启动dev环境时出现如下错误：

2025-03-13T21:39:17.059+05:30  WARN 80707 --- [spring-boot-rest-testing-project] [           main] o.h.t.s.i.ExceptionHandlerLoggedImpl     : GenerationTarget encountered exception accepting command : Error executing DDL "create table library (aisle integer, id bigint not null auto_increment, author varchar(255), book_name varchar(255), isbn varchar(255), primary key (id)) engine=InnoDB" via JDBC [Syntax error in SQL statement "create table library (aisle integer, id bigint not null auto_increment, author varchar(255), book_name varchar(255), isbn varchar(255), primary key (id)) engine[*]=InnoDB"; expected "identifier"]

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "create table library (aisle integer, id bigint not null auto_increment, author varchar(255), book_name varchar(255), isbn varchar(255), primary key (id)) engine=InnoDB" via JDBC [Syntax error in SQL statement "create table library (aisle integer, id bigint not null auto_increment, author varchar(255), book_name varchar(255), isbn varchar(255), primary key (id)) engine[*]=InnoDB"; expected "identifier"]
        at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:94) ~[hibernate-core-6.5.2.Final.jar:6.5.2.Final]
        ... （省略中间栈帧）
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "create table library (aisle integer, id bigint not null auto_increment, author varchar(255), book_name varchar(255), isbn varchar(255), primary key (id)) engine[*]=InnoDB"; expected "identifier"; SQL statement:
create table library (aisle integer, id bigint not null auto_increment, author varchar(255), book_name varchar(255), isbn varchar(255), primary key (id)) engine=InnoDB [42001-224]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:514) ~[h2-2.2.224.jar:2.2.224]
        ... （省略中间栈帧）

相关配置文件 #

application-dev.properties #

spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.datasource.url=jdbc:h2:mem:testdb
spring.datasource.driverClassName=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=

spring.jpa.hibernate.ddl-auto=create

spring.h2.console.enabled=true
#spring.h2.console.path=/h2-console
#spring.jpa.defer-datasource-initialization=true

application.properties #

spring.application.name=spring-boot-rest-testing-project

spring.datasource.url = jdbc:mysql://localhost:3306/APIDevelopSpringBoot?createDatabaseIfNotExist=true&useSSL=false
spring.datasource.username = root
spring.datasource.password = mysql
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQLDialect
spring.jpa.hibernate.ddl-auto = create
spring.test.database.replace=none
logging.file.name=log/application.log
spring.profiles.active=@activatedProperties@

解决方案 #

问题根源 #

启动dev环境时，Hibernate生成了带MySQL专属语法（engine=InnoDB）的DDL语句，但H2数据库不支持该语法。原因是application.properties中配置的MySQL方言没有被dev环境的配置覆盖，导致Hibernate仍使用MySQL方言生成SQL。

修复步骤 #

1. 显式覆盖dev环境的Hibernate方言
   在application-dev.properties中添加以下配置，确保优先级高于全局配置：

   spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.H2Dialect
   

   注：原配置中的spring.jpa.database-platform虽设置了H2方言，但spring.jpa.properties.hibernate.dialect的优先级更高，必须显式配置才能覆盖全局的MySQL方言。

2. 可选：开启H2的MySQL兼容模式
   如果仍存在SQL语法兼容问题，可以修改H2的连接URL，让它模拟MySQL的语法规则：

   spring.datasource.url=jdbc:h2:mem:testdb;MODE=MySQL;DATABASE_TO_LOWER=TRUE;CASE_INSENSITIVE_IDENTIFIERS=TRUE
   

3. 验证配置生效
   重启dev环境后，Hibernate会生成H2兼容的DDL语句，不再出现engine=InnoDB这类MySQL专属语法，问题即可解决。

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