使用VARBINARY类型创建表时出现Hibernate DDL执行错误

问题描述 #

User实体类 #

@Entity
@Table(name = "users",
        uniqueConstraints = {
                @UniqueConstraint(columnNames = "username"),
                @UniqueConstraint(columnNames = "email")
        })
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@Builder
@ToString
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @NotBlank
    @Size(max = 50)
    private String username;

    @NotBlank
    @Size(max = 50)
    @Email
    private String email;

    @NotBlank
    @Size(max = 120)
    private String password;

    BigDecimal lat;

    BigDecimal lon;

    @Column(columnDefinition="VARBINARY")
    byte[] natalChart;

...
    
}

配置文件 #

spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQLDialect
spring.jpa.hibernate.ddl-auto=update

启动错误日志 #

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "create table users (id bigint not null auto_increment, ascendant varchar(255), creation_date datetime(6), day integer not null, email varchar(50), hour integer not null, language varchar(255), last_login_date datetime(6), last_password_reset_date datetime(6), lat decimal(38,2), lon decimal(38,2), minute integer not null, mobile_os varchar(255), month integer not null, natal_char_desc bit, natal_char_titles bit, natal_chart VARBINARY, password varchar(120), platform varchar(255), solar_return_desc bit, solar_return_titles bit, subscription_expiration_date datetime(6), time_zone float(53) not null, token varchar(255), username varchar(50), year integer not null, primary key (id)) engine=InnoDB" via JDBC [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ', password varchar(120), platform varchar(255),  sol' at line 1]
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:92)
    at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlString(AbstractSchemaMigrator.java:574)
    at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.applySqlStrings(AbstractSchemaMigrator.java:514)
    at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.createTable(AbstractSchemaMigrator.java:315)
    at org.hibernate.tool.schema.internal.GroupedSchemaMigratorImpl.performTablesMigration(GroupedSchemaMigratorImpl.java:80)
    at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.performMigration(AbstractSchemaMigrator.java:232)
    at org.hibernate.tool.schema.internal.AbstractSchemaMigrator.doMigration(AbstractSchemaMigrator.java:117)
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.performDatabaseAction(SchemaManagementToolCoordinator.java:284)
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.lambda$process$5(SchemaManagementToolCoordinator.java:143)
    at java.base/java.util.HashMap.forEach(HashMap.java:1429)
    at org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator.process(SchemaManagementToolCoordinator.java:140)
    at org.hibernate.boot.internal.SessionFactoryObserverForSchemaExport.sessionFactoryCreated(SessionFactoryObserverForSchemaExport.java:37)
    at org.hibernate.internal.SessionFactoryObserverChain.sessionFactoryCreated(SessionFactoryObserverChain.java:35)
    at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:292)
    at org.hibernate.boot.internal.SessionFactoryBuilderImpl.build(SessionFactoryBuilderImpl.java:431)
    at org.hibernate.jpa.boot.internal.EntityManagerFactoryBuilderImpl.build(EntityManagerFactoryBuilderImpl.java:1455)

错误原因分析 #

从生成的DDL语句可以定位到核心问题：

• MySQL的VARBINARY类型必须指定长度（例如VARBINARY(1024)），不能直接写VARBINARY
• 实体类中natalChart字段的columnDefinition未指定长度，导致Hibernate生成的SQL违反MySQL语法，最终触发解析报错

解决方案 #

方案1：手动指定VARBINARY长度 #

修改实体类中natalChart字段的注解，明确添加长度参数：

@Column(columnDefinition="VARBINARY(1024)")
byte[] natalChart;

长度值可根据业务存储需求调整（如255、512、2048等）。

方案2：让Hibernate自动适配类型 #

去掉columnDefinition，改用length属性让Hibernate自动生成符合MySQL规范的DDL：

@Column(length = 1024)
byte[] natalChart;

这种方式兼容性更强，可适配不同数据库 dialect。

方案3：更新Hibernate dialect版本 #

如果使用MySQL 8.x版本，建议将配置文件中的dialect替换为对应版本：

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

新版本dialect会更精准地生成符合当前MySQL版本的SQL语法。

内容的提问来源于stack exchange，提问作者Nunyet Calçada