MySQL语法错误排查：创建players表时rank字段报错

问题场景 #

尝试创建players表，所用SQL语句及对应Java代码如下：

所用SQL语句 #

CREATE TABLE IF NOT EXISTS players (uuid VARCHAR(255) NOT NULL PRIMARY KEY, name VARCHAR(50), rank VARCHAR(50))

对应Java代码行 #

connection.createStatement().executeUpdate("CREATE TABLE IF NOT EXISTS players (uuid VARCHAR(255) NOT NULL PRIMARY KEY, name VARCHAR(50), rank VARCHAR(50))");

注：connection已提前正确初始化。

报错信息 #

18:17:43 [SCHWERWIEGEND] 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 'rank VARCHAR(50))' at line 1
18:17:43 [SCHWERWIEGEND]    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:121)
18:17:43 [SCHWERWIEGEND]    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
18:17:43 [SCHWERWIEGEND]    at com.mysql.cj.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1334)
18:17:43 [SCHWERWIEGEND]    at com.mysql.cj.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2084)
18:17:43 [SCHWERWIEGEND]    at com.mysql.cj.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1245)
18:17:43 [SCHWERWIEGEND]    at de.pc1up.bungeecordsystem.utils.SQLHandler.connect(SQLHandler.java:11)
18:17:43 [SCHWERWIEGEND]    at de.pc1up.bungeecordsystem.BungeeCordSystem.onEnable(BungeeCordSystem.java:26)
18:17:43 [SCHWERWIEGEND]    at net.md_5.bungee.api.plugin.PluginManager.enablePlugins(PluginManager.java:266)
18:17:43 [SCHWERWIEGEND]    at net.md_5.bungee.BungeeCord.start(BungeeCord.java:295)
18:17:43 [SCHWERWIEGEND]    at net.md_5.bungee.BungeeCordLauncher.main(BungeeCordLauncher.java:67)
18:17:43 [SCHWERWIEGEND]    at net.md_5.bungee.Bootstrap.main(Bootstrap.java:15)
18:17:43 [SCHWERWIEGEND]    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
18:17:43 [SCHWERWIEGEND]    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
18:17:43 [SCHWERWIEGEND]    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
18:17:43 [SCHWERWIEGEND]    at java.base/java.lang.reflect.Method.invoke(Method.java:568)
18:17:43 [SCHWERWIEGEND]    at de.dytanic.cloudnet.wrapper.Wrapper.lambda$startApplication$5(Wrapper.java:487)
18:17:43 [SCHWERWIEGEND]    at java.base/java.lang.Thread.run(Thread.java:842)

环境信息 #

• 服务器系统：Ubuntu 22.04
• MySQL版本：8.0.35

注：多个SQL语法检查网站判定语句正确，但运行时仍报错。

问题原因 #

rank是MySQL 8.0的保留关键字，直接用作列名会触发语法错误。部分在线语法检查工具未严格匹配MySQL的保留字规则，因此误判语句合法。

解决办法 #

有两种可行方案：

方案1：重命名列名 #

将rank改为非保留关键字，比如player_rank，修改后代码如下：

CREATE TABLE IF NOT EXISTS players (uuid VARCHAR(255) NOT NULL PRIMARY KEY, name VARCHAR(50), player_rank VARCHAR(50))

connection.createStatement().executeUpdate("CREATE TABLE IF NOT EXISTS players (uuid VARCHAR(255) NOT NULL PRIMARY KEY, name VARCHAR(50), player_rank VARCHAR(50))");

方案2：用反引号包裹保留关键字 #

在MySQL中，可通过反引号（`）将保留关键字作为列名使用，修改后代码如下：

CREATE TABLE IF NOT EXISTS players (uuid VARCHAR(255) NOT NULL PRIMARY KEY, name VARCHAR(50), `rank` VARCHAR(50))

connection.createStatement().executeUpdate("CREATE TABLE IF NOT EXISTS players (uuid VARCHAR(255) NOT NULL PRIMARY KEY, name VARCHAR(50), `rank` VARCHAR(50))");

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