MySQLのインデックスサイズに767byteまでしかつかえない問題と対策
Laravel5.4でmigrationを実行したところ、エラーになりました。
[Illuminate\Database\QueryException] SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes (SQL: alter table `users` add unique `users_email_unique`(`email`)) [Doctrine\DBAL\Driver\PDOException] SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes [PDOException] SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes
実行したマイグレーションは以下です。
public function up()
{
Schema::create('users', function (Blueprint $table) {
$table->increments('id');
$table->string('name');
$table->string('email')->unique();
$table->string('password');
$table->integer('status')->nullable()->unsigned();
$table->rememberToken();
$table->timestamps();
});
}
MySQLのリファレンスによると、
デフォルトでは、単一カラムインデックスのインデックスキーを最大で 767 バイトにすることができます。ということで、768バイト以上のカラムに対するインデックスキーは設定できないようです。
さて、Laravelの場合、データベースのキャラクターセットは、config/database.phpで定義されています。Laravel5.3までは、以下の通りUTF-8でした。
Laravel5.4からはutf8mb4が標準になりました。
'charset' => 'utf8', 'collation' => 'utf8_unicode_ci',UTF-8は1文字が3バイトのため、varchar(255)は、255文字✕3バイト＝765バイトということで、問題なくインデックスを設定することができていました。
Laravel5.4からはutf8mb4が標準になりました。
'charset' => 'utf8mb4', 'collation' => 'utf8mb4_unicode_ci',utf8mb4は、1文字が4バイトとしてあつかわれるため、制限の767バイトを超過してしまい、MySQLのエラーが出力されるようになりました。
解決方法
その１． データベースのキャラクターセットをUTF-8にする。
絵文字をあきらめます。
その2. varcharの最大長を191バイトにする
767 ÷ 4 = 191.75 の、191バイトにします。
https://laravel-news.com/laravel-5-4-key-too-long-error曰く、AppServiceProviderに以下のように記載すればいいようです。
https://dev.mysql.com/doc/refman/5.6/ja/innodb-restrictions.htmlや、https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_large_prefixを参考に、my.cnfで以下の設定をおこないます。
絵文字をあきらめます。
その2. varcharの最大長を191バイトにする
767 ÷ 4 = 191.75 の、191バイトにします。
https://laravel-news.com/laravel-5-4-key-too-long-error曰く、AppServiceProviderに以下のように記載すればいいようです。
use Illuminate\Support\Facades\Schema;
public function boot()
{
Schema::defaultStringLength(191);
}
その3. MySQLの行フォーマットの変更https://dev.mysql.com/doc/refman/5.6/ja/innodb-restrictions.htmlや、https://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_large_prefixを参考に、my.cnfで以下の設定をおこないます。
[mysqld] innodb_large_prefix innodb_file_per_table innodb_file_format=Barracudaこれで、key長の制限が767バイトから、3072バイトに拡張されます。 ※MySQL5.7からは標準で、innodb_file_formatはBarracudaとなるため、記述が不要になります。
まとめ
なにもなければ、その３.レンタルサーバとかで設定を変更できない場合は、その2.
