Laravel's Eloquent: bypass the 191 characters VARCHAR limitation

Question

As indicated by this person, this person and this person, in order to avoid a

SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes

you have to modify the AppServiceProvider.php of your Laravel project like that:

<?php
namespace App\Providers;
use Illuminate\Support\ServiceProvider;
use Illuminate\Support\Facades\Schema;              //add this

class AppServiceProvider extends ServiceProvider {
    public function boot() {
        Schema::defaultStringLength(191);           //and this
    }

    public function register() { }
}

But the drawback of this method is that the VARCHARs I use are limited to 191 characters, and I want to have a field longer than that. How should I do?

Thank you for your help.

The questions you link to has several answers about how you can configure your mysql server to support this thing (indexes longer than 191 characters). What's wrong with those? — sisve, May 04 '18 at 10:57
Because I will deploy my project onto my client's machine and I don't know which MySQL version will be supported. Besides, I didn't know that setting the VARCHAR's length in the migration allowed to bypass the 255 characters limitation. — JacopoStanchi, May 04 '18 at 12:07
Setting the varchar's length in the migration _does not_ get rid of the limitation. The limitation comes from trying to create an index on a table and the index becomes longer than supported. There will be no limitation if you never create an index on the varchar field. However, Laravel creates an index on the users.email field in the default migration, thus people see this limit in their new project. I think you should just document the mysql configuration as a application requirement, and let their technical team solve it for you. — sisve, May 07 '18 at 08:13

score 4 · Accepted Answer · answered May 04 '18 at 09:39

4

Actually on your migration file you can define the size. Example

$table->string('field_name', 200);

answered May 04 '18 at 09:39

Dharma Saputra

1,524
12
17

How far can I go? If the limit is 250, I don't think that will be enough either. – JacopoStanchi May 04 '18 at 09:41
255 is the maximum, but the only real way to solve this is by upgrading your mysql version. – Quezler May 04 '18 at 09:43
If you want longer size, use `text` type instead. Varchar limitation is maximum 255 character length – Dharma Saputra May 04 '18 at 09:44
Apparently it worked perfectly. I created a field with more than 1000 characters. – JacopoStanchi May 04 '18 at 12:08
@JacopoStanchi how long is the text you are inserting into this field? does it exceed 255, the normal varchar length without the appserviceprovider code? if not it would be a 'huge' performance waste to use a text field for this purpose – Quezler May 04 '18 at 12:10
@Quezler No, I mean that I used VARCHAR and not TEXT. It surprises me as much as you but you can give a greater value than 255 in the `$table->string('field',1000);`. – JacopoStanchi May 04 '18 at 12:12
that is a surprise indeed – Quezler May 04 '18 at 12:15
Seriously? Just knowing that we can set size more than 255. What a surprise. – Dharma Saputra May 04 '18 at 12:18
2

Ahh. There is the explanation https://stackoverflow.com/questions/13506832/what-is-the-mysql-varchar-max-size, we can set the size at maximum `65,535`. – Dharma Saputra May 04 '18 at 12:22

score 1 · Answer 2 · answered May 04 '18 at 09:38

1

You will have to upgrade your MySQL version to v5.7.7 or higher.

answered May 04 '18 at 09:38

Quezler

2,376
14
29

Laravel's Eloquent: bypass the 191 characters VARCHAR limitation

2 Answers2