Why Am I Getting MySQL Error 1064 in XAMPP When Adding an Index?

Troubleshooting MySQL Error 1064 in XAMPP

When executing SQL commands in MySQL Workbench within XAMPP, users may encounter Error 1064: "You have an error in your SQL syntax." Despite server status appearing正常, this error can arise unexpectedly.

In this particular instance, the issue stems from the use of the VISIBLE keyword when adding an index on the animals table:

ALTER TABLE `puppies`.`animals` 
ADD INDEX `Breed_idx` (`BreedID` ASC) VISIBLE;

To resolve this error, simply remove the VISIBLE keyword as it is not recognized by MySQL. Here is the corrected statement:

ALTER TABLE `puppies`.`animals` 
ADD INDEX `Breed_idx` (`BreedID` ASC);

After removing VISIBLE, the execution should proceed without error.

Understanding the Difference Between MySQL and MariaDB

This issue arises due to a misunderstanding regarding the syntax differences between MySQL and MariaDB. XAMPP utilizes MariaDB by default, which has distinct syntax in certain aspects. The VISIBLE keyword is only applicable in MySQL.

In MariaDB, the creation of an index without specifying its visibility implicitly marks it as visible. Therefore, there is no need to explicitly include the VISIBLE keyword.

Reference Materials

For further clarification on this subject, consult the following resources:

• [MariaDB Documentation on Index Visibility](https://jira.mariadb.org/browse/MDEV-7317)

The above is the detailed content of Why Am I Getting MySQL Error 1064 in XAMPP When Adding an Index?. For more information, please follow other related articles on the PHP Chinese website!