异常

MySQL 8.0版本相比5.7版本新增和删除了一些关键字，如果原来系统中一些表名或者字段名成为8.0中的关键字的话，系统会报如下异常：

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 ', title from test LIMIT 0, 100'

异常解决

通常我们需要修改下面两种场景来解决这个异常

1、实体Model

在报错的实体Model的相应字段上增加@Column注解，@Column 注解用来标识实体类中属性与数据表中字段的对应关系，注解中的name属性定义了被标注字段在数据库表中所对应字段的名称，nullable属性表示该字段是否可以为 null 值，默认为 true。

需要在报错的字段的name属性的值上即字段名上增加反单引号，这样即使字段名跟新增关键字名称一样，也不会报错了，示例：

@Column(name = "`rank`", nullable = false) 
private String rank; 

2、手写SQL

在报错的Sql的相应字段上增加反单引号，示例：

Select `rank`, title from test LIMIT 0, 100 

MySQL8.0新增关键字列表

=== A

ARRAY; added in 8.0.17 (reserved); became nonreserved in 8.0.19

=== C

CUBE (R); became reserved in 8.0.1

CUME_DIST (R); added in 8.0.2 (reserved)

=== D

DENSE_RANK (R); added in 8.0.2 (reserved)

=== E

EMPTY (R); added in 8.0.4 (reserved)

=== F

FIRST_VALUE (R); added in 8.0.2 (reserved)

FUNCTION (R); became reserved in 8.0.1

=== G

GET_MASTER_PUBLIC_KEY; added in 8.0.4 (reserved); became nonreserved in 8.0.11

GROUPING (R); added in 8.0.1 (reserved)

GROUPS (R); added in 8.0.2 (reserved)

=== I

INTERSECT (R); added in 8.0.31 (reserved)

=== J

JSON_TABLE (R); added in 8.0.4 (reserved)

=== L

LAG (R); added in 8.0.2 (reserved)

LAST_VALUE (R); added in 8.0.2 (reserved)

LATERAL (R); added in 8.0.14 (reserved)

LEAD (R); added in 8.0.2 (reserved)

=== M

MEMBER; added in 8.0.17 (reserved); became nonreserved in 8.0.19

=== N

NTH_VALUE (R); added in 8.0.2 (reserved)

NTILE (R); added in 8.0.2 (reserved)

=== O

OF (R); added in 8.0.1 (reserved)

OVER (R); added in 8.0.2 (reserved)

=== P

PERCENT_RANK (R); added in 8.0.2 (reserved)

PERSIST_ONLY; added in 8.0.2 (reserved); became nonreserved in 8.0.16

=== R

RANK (R); added in 8.0.2 (reserved)

RECURSIVE (R); added in 8.0.1 (reserved)

ROW (R); became reserved in 8.0.2

ROWS (R); became reserved in 8.0.2

ROW_NUMBER (R); added in 8.0.2 (reserved)

=== S

SYSTEM (R); added in 8.0.3 (reserved)

=== W

WINDOW (R); added in 8.0.2 (reserved)

批量校验

批量校验项目的数据库实例中是否有MySQL8.0新增关键字

校验表名：

SELECT TABLE_NAME 
FROM information_schema.TABLES 
WHERE TABLE_NAME in ("ARRAY","CUBE","CUME_DIST","DENSE_RANK","EMPTY","FIRST_VALUE","FUNCTION", 
  "GET_MASTER_PUBLIC_KEY","GROUPING","GROUPS","INTERSECT","JSON_TABLE","LAG","LAST_VALUE","LATERAL", 
  "LEAD","MEMBER","NTH_VALUE","NTILE","OF","OVER","PERCENT_RANK","PERSIST_ONLY","RANK","RECURSIVE", 
  "ROW","ROWS","ROW_NUMBER","SYSTEM","WINDOW"); 

校验字段名：

SELECT TABLE_NAME, COLUMN_NAME 
FROM information_schema.COLUMNS 
WHERE COLUMN_NAME in ("ARRAY","CUBE","CUME_DIST","DENSE_RANK","EMPTY","FIRST_VALUE","FUNCTION", 
  "GET_MASTER_PUBLIC_KEY","GROUPING","GROUPS","INTERSECT","JSON_TABLE","LAG","LAST_VALUE","LATERAL", 
  "LEAD","MEMBER","NTH_VALUE","NTILE","OF","OVER","PERCENT_RANK","PERSIST_ONLY","RANK","RECURSIVE", 
  "ROW","ROWS","ROW_NUMBER","SYSTEM","WINDOW"); 

若您想了解更多内容，请关注公众号：图南随笔，vx：tunan66666

若您觉得还可以，请帮忙点个“赞”，谢谢～

关注我，查看更多技术干货文章