SQLSTATE[42000]: Syntax Error or Access Violation: 1059 in Laravel 6 Migrations When the Index Identifier Is Too Long

1. When executing a database migration, the following error occurred: SQLSTATE[42000]: Syntax error or access violation: 1059 Identifier name ‘theme_installation_version_preset_theme_store_theme_version_id_index’ is too long. See Figure 1.

   Illuminate\Database\QueryException  : SQLSTATE[42000]: Syntax error or access violation: 1059 Identifier name 'theme_installation_version_preset_theme_store_theme_version_id_index' is too long (SQL: alter table `theme_installation_version_preset` add index `theme_installation_version_preset_theme_store_theme_version_id_index`(`theme_store_theme_version_id`))

   Illuminate\Database\QueryException  : SQLSTATE[42000]: Syntax error or access violation: 1059 Identifier name 'theme_installation_version_preset_theme_store_theme_version_id_index' is too long (SQL: alter table `theme_installation_version_preset` add index `theme_installation_version_preset_theme_store_theme_version_id_index`(`theme_store_theme_version_id`))

2. The index length limit in MySQL 5.7 defaults to 64, while the length of theme_installation_version_preset_theme_store_theme_version_id_index is 68. See Figure 2.

3. In Laravel 6, every index method accepts an optional second parameter to specify the index name. If omitted, the name will be generated based on the table and column names. I ultimately decided to explicitly specify the index name: tivp_theme_store_theme_version_id_index, using only the initials of the table name.

$table->unsignedBigInteger('theme_store_theme_version_id')->comment('主题商店的主题版本ID')->index();

$table->unsignedBigInteger('theme_store_theme_version_id')->comment('主题商店的主题版本ID')->index();

$table->unsignedBigInteger('theme_store_theme_version_id')->comment('主题商店的主题版本ID')->index('tivp_theme_store_theme_version_id_index');

$table->unsignedBigInteger('theme_store_theme_version_id')->comment('主题商店的主题版本ID')->index('tivp_theme_store_theme_version_id_index');

4. The migration executed successfully.