Failed to parse when creating unique index in creation DDL

[RewardisGem]

Actual Behavior

Parsing creation DDL like:

CREATE TABLE test3 ( NAME varchar(255) NOT NULL DEFAULT '', ID bigint(20) unsigned NOT NULL, PRIMARY KEY (NAME), unique index idx(id) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Throw exception Caused by: net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "unique" "UNIQUE" at line 4, column 25.

The DDL works fine in MySQL 5.7. I change unique index idx(id) ---> unique key idx(id) then jsqlparser works.

Specifications

• Version: jsqlparser V2.1
• Platform: Macos 10.14.5 Java 8
• Subsystem:

[louisliu318]

+1

[xiananliu]

+1

[manticore-projects]

The correct standard compliant syntax would be:

CREATE TABLE test3 (
    name         VARCHAR (255)   NOT NULL DEFAULT ''
    , id         BIGINT (20)     UNSIGNED NOT NULL
    , PRIMARY KEY ( name )
    , UNIQUE KEY idx (id)
) ENGINE = INNODB DEFAULT CHARSET = LATIN1
;

MySQL only seems to allow an INDEX keyword:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    [IGNORE | REPLACE]
    [AS] query_expression

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

create_definition: {
    col_name column_definition
  | {INDEX | KEY} [index_name] [index_type] (key_part,...)
      [index_option] ...
  | {FULLTEXT | SPATIAL} [INDEX | KEY] [index_name] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] PRIMARY KEY
      [index_type] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] UNIQUE [INDEX | KEY]
      [index_name] [index_type] (key_part,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (col_name,...)
      reference_definition
  | check_constraint_definition
}