ThinkPHP6 migration: 1071 Specified key was too long; 767 bytes解决方案

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

原因与解决

国内百度搜索,大量抄袭文章,一头雾水。随后谷歌搜索得到准确原因:

commented on Dec 4, 2017

Because of mysql 5.6 (includes prior versions) InnoDB max index length is 767 bytes, mysql 5.7.7 is up to 3072 bytes.
If some varchar column’s length is 255, when the character format is utf-8 needs 255*3=765 bytes for index length, It’s OK.
But, an utf8mb needs 255*4=1020 bytes for index length.

Solutions:

  • UPGRADE the mysql to 5.7.7(Mariadb 10.2.2)
  • Change the utf8mb column length to 191 (191*4=764)
  • Change the utf8mb to utf8
  • set innodb_file_format=Barracudainnodb_large_prefix=on and create** table** using ROW_FORMAT=DYNAMIC or COMPRESSED (default for 5.7.7)

I will fix it later.

via: https://github.com/gogs/gogs/issues/4894#issuecomment-348861978

故障重现

在使用ThinkPHP6的think-migrate创建迁移时,命令行报错。本机环境为XAMPP 7.3.1版本(PHP 7.3.1+MariaDB 10.1)

        $users = $this->table('users', ['signed' => false, 'engine' => 'InnoDB', 'collation' => 'utf8mb4_unicode_ci']);
        $users->addColumn('username', 'string', ['limit' => 255])
点赞

发表评论

电子邮件地址不会被公开。 必填项已用*标注