Spring Boot MySQL执行import.sql报Error executing DDL错误

• Spring Boot集成JPA项目可根据实体类正常自动建表，但通过import.sql加载初始化数据时，仅MySQL环境下导入失败，切换H2数据库运行正常
• 项目启动抛出异常，核心报错为Error executing DDL "xxx" via JDBC Statement，根异常为java.sql.SQLSyntaxErrorException，提示SQL语法错误
• 执行日志显示Hibernate将import.sql中换行编写的单条INSERT语句按换行拆分为3个独立片段分别执行：
  • insert into currency_exchange
  • (id, currency_from, currency_to)
  • values (10001, 'USD', 'INR')
• 拆分后的SQL片段均不符合语法规范，最终触发报错。已确认数据库方言配置为org.hibernate.dialect.MySQL8Dialect，更换其他方言会导致数据表无法正常创建。

JPA实体类 #

package com.gab.microservices.currencyexchangeservice.bean;

import lombok.*;

import javax.persistence.*;
import java.math.BigDecimal;
@Data
@RequiredArgsConstructor
@NoArgsConstructor
@Entity
public class CurrencyExchange {
    @NonNull
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @NonNull
    @Column(name = "currency_from")
    private String from;

    @NonNull
    @Column(name = "currency_to")
    private String to;

    @NonNull
    private BigDecimal conversionMultiple;

    private String environment;
}

application.yml配置 #

spring:
  application:
    name: currency-exchange
  config:
    import: optional:configserver:http://localhost:8888
  cloud:
    config:
      uri: http://localhost:8888
      username: gigi
      password: test1234
  jpa:
    show-sql: true
  datasource:
    url: jdbc:mysql://127.0.0.1:6666/testdb?allowPublicKeyRetrieval=true&useSSL=false
    username: root
    password: test1234

server:
  port: 8000

spring.jpa.defer-datasource-initialization: true
spring.jpa.properties.hibernate.format_sql: true
logging.level.org.hibernate.type: trace
spring.jpa.hibernate.ddl-auto: create
spring.datasource.initialization-mode: always
spring.jpa.properties.hibernate.dialect: org.hibernate.dialect.MySQL8Dialect

import.sql脚本 #

insert into currency_exchange
(id, currency_from, currency_to)
values (10001, 'USD', 'INR');

pom.xml依赖 #

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.7.0</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.gab.microservices</groupId>
    <artifactId>currency-exchange-service</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>currency-exchange-service</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>11</java.version>
        <spring-cloud.version>2021.0.3</spring-cloud.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-actuator</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.cloud</groupId>
            <artifactId>spring-cloud-starter-config</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
    
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.24</version>
            <scope>provided</scope>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>
    <dependencyManagement>
        <dependencies>
            <dependency>
                <groupId>org.springframework.cloud</groupId>
                <artifactId>spring-cloud-dependencies</artifactId>
                <version>${spring-cloud.version}</version>
                <type>pom</type>
                <scope>import</scope>
            </dependency>
        </dependencies>
    </dependencyManagement>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

</project>

Hibernate针对不同数据库方言默认使用不同的SQL语句拆分规则：H2方言默认按分号拆分SQL，支持多行编写单条语句；MySQL方言默认按换行符拆分SQL，因此换行编写的单条INSERT会被拆分为多个不完整的片段执行，触发语法错误。
另外现有INSERT语句未给标记为非空的conversion_multiple字段赋值，即使解决SQL拆分问题，也会因非空字段无有效值触发后续报错。

两种方案可任选其一解决SQL拆分问题，同时必须补全非空字段的插入值：

• 方案1：修改import.sql，将单条SQL写为单行，不做换行拆分，补全非空字段后内容如下：

insert into currency_exchange(id, currency_from, currency_to, conversion_multiple) values (10001, 'USD', 'INR', 75.0);

• 方案2：修改JPA配置，指定Hibernate使用支持多行SQL的语句提取器，按分号而非换行拆分SQL，在application.yml中添加如下配置：

spring:
  jpa:
    properties:
      hibernate:
        hbm2ddl:
          import_files_sql_extractor: org.hibernate.tool.hbm2ddl.MultipleLinesSqlCommandExtractor

配置完成后，原有换行编写的SQL可正常识别，只需补全conversion_multiple字段的赋值即可正常导入数据。

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