Jakarta Persistence创建表报错：MySQL SQL语法错误求助

问题描述 #

定义的JPA实体类如下：

@Entity
@Table(name = "t_message",
        uniqueConstraints = {
                @UniqueConstraint(columnNames = "key"),
                @UniqueConstraint(columnNames = "lang")
        })
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class Message {
    
    @Id
    private String key;
    @Column(length = 10000)
    private String description;
    @Column(length = 2)
    private String lang;
    
}

启动应用时抛出SQL语法错误：

Caused by: java.sql.SQLSyntaxErrorException: 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 'varchar(255) not null, description varchar(10000), lang varchar(2), primary key ' at line 1
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:121)
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
    at com.mysql.cj.jdbc.StatementImpl.executeInternal(StatementImpl.java:763)
    at com.mysql.cj.jdbc.StatementImpl.execute(StatementImpl.java:648)
    at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:94)
    at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
    at org.hibernate.tool.schema.internal.exec.GenerationTargetToDatabase.accept(GenerationTargetToDatabase.java:78)
    ... 132 common frames omitted

错误原因 #

key是MySQL的保留关键字，Hibernate自动生成建表SQL时，直接将实体字段key作为列名使用，导致SQL语法解析失败。MySQL不允许直接用保留字作为标识符（列名、表名等），除非用反引号包裹。

解决方法 #

方法1：给列名添加反引号转义 #

修改实体类中key字段的注解，显式指定列名为带反引号的key，同时更新@UniqueConstraint中的列名：

@Entity
@Table(name = "t_message",
        uniqueConstraints = {
                @UniqueConstraint(columnNames = "`key`"),
                @UniqueConstraint(columnNames = "lang")
        })
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class Message {
    
    @Id
    @Column(name = "`key`")
    private String key;
    @Column(length = 10000)
    private String description;
    @Column(length = 2)
    private String lang;
    
}

方法2：修改字段名（推荐） #

避免使用数据库保留字作为字段名，将key改为更具语义的名称（如messageKey），同步更新所有相关注解：

@Entity
@Table(name = "t_message",
        uniqueConstraints = {
                @UniqueConstraint(columnNames = "message_key"),
                @UniqueConstraint(columnNames = "lang")
        })
@Getter
@Setter
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class Message {
    
    @Id
    @Column(name = "message_key")
    private String messageKey;
    @Column(length = 10000)
    private String description;
    @Column(length = 2)
    private String lang;
    
}

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