Why Am I Getting a Syntax Error with 'VISIBLE' in a CREATE UNIQUE INDEX Statement?

MySQL Workbench: Syntax Error with VISIBLE in CREATE UNIQUE INDEX Statement

The error message "Syntax error near 'VISIBLE' at line 1" occurs when using the MySQL Workbench auto-generated CREATE UNIQUE INDEX statement. This issue stems from the difference in syntax between MySQL server versions.

Cause:

MySQL Workbench 8.0.12 auto-generates the CREATE UNIQUE INDEX statement with the VISIBLE option, which is compatible with MySQL server version 8.0. However, if you are using an earlier version of MySQL (such as 5.7), this option is not recognized, leading to a syntax error.

Syntax Differences:

In MySQL server version 8.0, the syntax for CREATE INDEX includes the VISIBLE index option:

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}

In MySQL server version 5.7, this VISIBLE option is not available:

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'

Solution:

To resolve the issue, you have two options:

1. Disable VISIBLE Option in MySQL Workbench: In MySQL Workbench, go to Edit > Preferences > Modeling > MySQL and set the "Default Target MySQL Version" to 5.7. This will prevent MySQL Workbench from auto-generating the VISIBLE option.
2. Upgrade MySQL Server: If you wish to use the VISIBLE option, consider upgrading your MySQL server to version 8.0 or later.

The above is the detailed content of Why Am I Getting a Syntax Error with 'VISIBLE' in a CREATE UNIQUE INDEX Statement?. For more information, please follow other related articles on the PHP Chinese website!