Why does MySQL Workbench generate syntax errors when using 'VISIBLE' in CREATE UNIQUE INDEX statements?

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

MySQL Workbench 8.0.12 may auto-generate CREATE UNIQUE INDEX statements that include the 'VISIBLE' option, which is not supported in MySQL Server 5.7. This incompatibility causes syntax errors.

Problem Statement

Errors occur when creating unique indexes with VISIBLE in the CREATE UNIQUE INDEX statement. For example:

CREATE UNIQUE INDEX `name_UNIQUE` ON `setting` (`code` ASC) VISIBLE;

MySQL Server 5.7 will return the following error:

Error in query (1064): Syntax error near 'VISIBLE' at line 1

Cause

The syntax difference stems from the different versions of MySQL. In MySQL 8.0 and higher, the syntax of CREATE UNIQUE INDEX includes the 'VISIBLE' or 'INVISIBLE' option. However, in MySQL 5.7, this option is not supported.

Solution

Disable Auto-Generation of 'VISIBLE' in MySQL Workbench:

1. In MySQL Workbench, navigate to:

   • Edit > Preferences > Modeling > MySQL
2. Set the "Default Target MySQL Version" to 5.7.

This prevents MySQL Workbench from auto-generating 'VISIBLE' in the CREATE UNIQUE INDEX statement.

Alternative:

If upgrading to MySQL 8.0 is not possible, remove the 'VISIBLE' option from the CREATE UNIQUE INDEX statement manually.

The above is the detailed content of Why does MySQL Workbench generate syntax errors when using 'VISIBLE' in CREATE UNIQUE INDEX statements?. For more information, please follow other related articles on the PHP Chinese website!