How to extend the length of slug in Tags table?

westwoods

I just try to extend the length of slug in Tags table as I need to store long slug of tags.
However when I create a migration file like this:

    'up' => function (Builder $schema) {
        $schema->table('tags', function (Blueprint $table) {
            $table->string('slug', 200)->change();
        });
    },

It shows an error as below:
Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes
I tried to change
public static $defaultStringLength = 255; to 191 but it still works uncorrect.
I tried to modify color column from 50 to 300 and it works correct.
Anyone can tell me why (string)color can be extended but (string)slug cannot?
Thank you very much and merry chrismas.

clarkwinkelmann

westwoods https://stackoverflow.com/a/1814594/3133038

767 bytes is the stated prefix limitation for InnoDB tables in MySQL version 5.6 (and prior versions). It's 1,000 bytes long for MyISAM tables. In MySQL version 5.7 and upwards this limit has been increased to 3072 bytes.

You also have to be aware that if you set an index on a big char or varchar field which is utf8mb4 encoded, you have to divide the max index prefix length of 767 bytes (or 3072 bytes) by 4 resulting in 191. This is because the maximum length of a utf8mb4 character is four bytes. For a utf8 character it would be three bytes resulting in max index prefix length of 254.

Emphasis is mine.

You either need to use a length of 191, or update your MySQL to a version that supports longer indexes.

The reason there's no issue with color is because it's not indexed. Only slug has a UNIQUE index.

westwoods

clarkwinkelmann
That is correct. I also found this answer. But I use mariadb still need to update 10+?

westwoods

clarkwinkelmann
An alternative way to solve this:
1： set innodb_large_prefix -> ON
2： set nnodb_file_format -> Barracuda
3： ROW_FORMAT -> DYNAMIC / COMPRESSED
But I dont know if modifiy DB setting like this will make any uncorrect affect.