tell me the correct syntax in MySQL 8

3145 views mysql
6

CREATE TABLE socialgroupcategory (
 socialgroupcategoryid INT unsigned NOT NULL auto_increment,
 creatoruserid INT unsigned NOT NULL,
 title VARCHAR(250) NOT NULL,
 description TEXT NOT NULL,
 displayorder INT unsigned NOT NULL,
 lastupdate INT unsigned NOT NULL,
 groups INT unsigned DEFAULT '0',
 PRIMARY KEY  (socialgroupcategoryid),
 KEY displayorder (displayorder)
);  

MySQL Error : 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 'groups INT unsigned DEFAULT '0', PRIMARY KEY (socialgroupcategoryid), KEY dis' at line 8 Error Number : 1064

MySQL 5.7 - ok

MySQL 8 - error

answered question

try to enclose all non-keywords with back ticks (``).

1 Answer

11

GROUPS has been added as a reserved keyword in MySQL 8. From official documentation:

GROUPS (R); added in 8.0.2 (reserved)

You should use backticks to name a column/table using reserved keywords. Use the following (notice backticks around groups):

CREATE TABLE socialgroupcategory (
 socialgroupcategoryid INT unsigned NOT NULL auto_increment,
 creatoruserid INT unsigned NOT NULL,
 title VARCHAR(250) NOT NULL,
 description TEXT NOT NULL,
 displayorder INT unsigned NOT NULL,
 lastupdate INT unsigned NOT NULL,
 `groups` INT unsigned DEFAULT '0',
 PRIMARY KEY  (socialgroupcategoryid),
 KEY displayorder (displayorder)
);  

posted this

Have an answer?

JD

Please login first before posting an answer.