Using MySQL utf8mb4 with Laravel 5

Currently Laravel ships with MySQL utf8 and utf8_unicode_ci as the default charset and collation configurations. In order to utilize full UTF-8 support and Emoji support utf8mb4 and utf8mb4_unicode_ci must be used in it's place.

The issue I encountered was when I attempted to run the artisan migration for the included users and password_resets migrations that ship with L5. Laravel's schema builder defaults string length to 255 which is fine when using utf8 but when using utf8mb4 the byte limit is surpassed.

You may encounter the following error:
[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`))

The solution is simple though, when declaring strings with the schema builder do not use the default string length and provide a 2nd parameter of 191 for the string() function.

// wrong
$table->string('name');
$table->string('email')->unique();

// correct
$table->string('name', 191);
$table->string('email', 191)->unique();

The reasoning for this length change is that regular utf8 charset and collation utilize only 3 bytes per character and utf8mb4 utilizes 4 bytes per character to support a larger range of characters. To dive deeper into this please check the resources linked below:

Note (thanks Chris): This error will only occur when attempting to set the string column as a key in the table.

https://mathiasbynens.be/notes/mysql-utf8mb4
http://www.phptherightway.com/#php_and_utf8
https://dev.mysql.com/doc/refman/5.7/en/charset-unicode-utf8mb4.html

RELATED POSTS

2 Responses to “Using MySQL utf8mb4 with Laravel 5”

  1. Chris Loftus says:

    Forgive me if I’m wrong but isn’t this only for columns with keys?

    It’s throwing because the “specified key was too long”. The ‘name’ column doesn’t have a key, whereas the email column has a unique key. So you only need to add the character limit to the email column.

  2. Austin says:

    Yes, I believe you are correct, the length of the field would only affect the keys of the table.

Leave a Reply