mysql创建表格显示1064错误_无法在mysql中创建表-错误1064

I am trying to create a table in MySQL with the query

CREATE TABLE ofRosterGroups (

rosterID BIGINT NOT NULL,

rank TINYINT NOT NULL,

groupName VARCHAR(255) NOT NULL,

PRIMARY KEY (rosterID, rank),

INDEX ofRosterGroup_rosterid_idx (rosterID)

);

but seems like it is throwing error everytime I made updates too. I don't know what is going wrong with it.

Error coming up is

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 TINYINT NOT NULL, groupName

VARCHAR at line 3

解决方案

MySQL 8.0.2 added support for the window rank function, making it a reserverd word.

You could escape it using backticks (`):

CREATE TABLE ofRosterGroups (

rosterID BIGINT NOT NULL,

`rank` TINYINT NOT NULL, -- Here

groupName VARCHAR(255) NOT NULL,

PRIMARY KEY (rosterID, `rank`), -- And here

INDEX ofRosterGroup_rosterid_idx (rosterID)

);

But it may be a better idea to just use a name that isn't a reserved word, such as rosterRank instead of rank:

CREATE TABLE ofRosterGroups (

rosterID BIGINT NOT NULL,

rosterRank TINYINT NOT NULL, -- Here

groupName VARCHAR(255) NOT NULL,

PRIMARY KEY (rosterID, rosterRank), -- And here

INDEX ofRosterGroup_rosterid_idx (rosterID)

);