[PDOException]
SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 767 bytes
原因与解决
国内百度搜索,大量抄袭文章,一头雾水。随后谷歌搜索得到准确原因:
m2nlight 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=Barracuda
,innodb_large_prefix=on
and create** table** using ROW_FORMAT=DYNAMIC
orCOMPRESSED
(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])