ICHARM
Mysql使用“key”作为字段名会报错(key为保留关键字)
Mysql使用“key”作为字段名会报错(key为保留关键字)

环境

Spring boot Version : 2

Mysql Version : 5.7

Hibernate dialect : org.hibernate.dialect.MySQL5Dialect

报错信息

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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), userId_id bigint, primary key (id)) engine=MyISAM’ at line 1

新建一个Entity,Hibernate在创建表时报的错误。

报错的Entity:

import lombok.Data;

import javax.persistence.*;
import java.sql.Timestamp;

/**
 * @author mylicharm
 * @email icharm.me@outlook.com
 * @date 2018/8/15 18:52
 */
@Data
@Entity
@Table(name = "com_system_parameter")
public class SystemParameter {

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

    @ManyToOne(targetEntity = User.class)
    private Long userId;

    private String key;
    private String value;
    private String description;
    @Column(insertable = true)
    private Timestamp createdAt;
    @Column(insertable = true, updatable = true)
    private Timestamp updatedAt;
}

 

解决过程

错误信息中提示语法错误,在’varchar(255), userId_id bigint, primary key (id)) engine=MyISAM’附近,通过搜索,基本确定问题为entity中定义有问题,采用一一排除法确定问题,先将其他的字段注释掉,看能不能成功建表,再一个一个的取消注释。

最后发现,巨坑的地方!使用key作为字段名会报错!改为keyword就正常了。如果直接写SQL语句可以使用`符号把key包住(`key`),可以避免提示错误。

最后附上Mqsql保留关键字大全:

Table 2.1. Reserved Words in MySQL 5.0

ADDALLALTER
ANALYZEANDAS
ASCASENSITIVEBEFORE
BETWEENBIGINTBINARY
BLOBBOTHBY
CALLCASCADECASE
CHANGECHARCHARACTER
CHECKCOLLATECOLUMN
CONDITIONCONNECTION[a]CONSTRAINT
CONTINUECONVERTCREATE
CROSSCURRENT_DATECURRENT_TIME
CURRENT_TIMESTAMPCURRENT_USERCURSOR
DATABASEDATABASESDAY_HOUR
DAY_MICROSECONDDAY_MINUTEDAY_SECOND
DECDECIMALDECLARE
DEFAULTDELAYEDDELETE
DESCDESCRIBEDETERMINISTIC
DISTINCTDISTINCTROWDIV
DOUBLEDROPDUAL
EACHELSEELSEIF
ENCLOSEDESCAPEDEXISTS
EXITEXPLAINFALSE
FETCHFLOATFLOAT4
FLOAT8FORFORCE
FOREIGNFROMFULLTEXT
GOTO[b]GRANTGROUP
HAVINGHIGH_PRIORITYHOUR_MICROSECOND
HOUR_MINUTEHOUR_SECONDIF
IGNOREININDEX
INFILEINNERINOUT
INSENSITIVEINSERTINT
INT1INT2INT3
INT4INT8INTEGER
INTERVALINTOIS
ITERATEJOINKEY
KEYSKILLLABEL[c]
LEADINGLEAVELEFT
LIKELIMITLINES
LOADLOCALTIMELOCALTIMESTAMP
LOCKLONGLONGBLOB
LONGTEXTLOOPLOW_PRIORITY
MATCHMEDIUMBLOBMEDIUMINT
MEDIUMTEXTMIDDLEINTMINUTE_MICROSECOND
MINUTE_SECONDMODMODIFIES
NATURALNOTNO_WRITE_TO_BINLOG
NULLNUMERICON
OPTIMIZEOPTIONOPTIONALLY
ORORDEROUT
OUTEROUTFILEPRECISION
PRIMARYPROCEDUREPURGE
READREADSREAL
REFERENCESREGEXPRELEASE
RENAMEREPEATREPLACE
REQUIRERESTRICTRETURN
REVOKERIGHTRLIKE
SCHEMASCHEMASSECOND_MICROSECOND
SELECTSENSITIVESEPARATOR
SETSHOWSMALLINT
SONAMESPATIALSPECIFIC
SQLSQLEXCEPTIONSQLSTATE
SQLWARNINGSQL_BIG_RESULTSQL_CALC_FOUND_ROWS
SQL_SMALL_RESULTSSLSTARTING
STRAIGHT_JOINTABLETERMINATED
THENTINYBLOBTINYINT
TINYTEXTTOTRAILING
TRIGGERTRUEUNDO
UNIONUNIQUEUNLOCK
UNSIGNEDUPDATEUPGRADE[d]
USAGEUSEUSING
UTC_DATEUTC_TIMEUTC_TIMESTAMP
VALUESVARBINARYVARCHAR
VARCHARACTERVARYINGWHEN
WHEREWHILEWITH
WRITEXORYEAR_MONTH
ZEROFILL
首页      博文      Code      Web      Mysql使用“key”作为字段名会报错(key为保留关键字)

发表评论

textsms
account_circle
email

ICHARM

Mysql使用“key”作为字段名会报错(key为保留关键字)
环境 Spring boot Version : 2 Mysql Version : 5.7 Hibernate dialect : org.hibernate.dialect.MySQL5Dialect 报错信息 Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyn…
扫描二维码继续阅读
2018-08-16