执行以下命令时:
ALTER TABLE `mytable` ADD UNIQUE (
`column1` ,
`column2`
);
我得到了这个错误信息:
#1071 - Specified key was too long; max key length is 767 bytes
columnn1和column2的信息:
column1 varchar(20) utf8_general_ci
column2 varchar(500) utf8_general_ci
我认为varchar(20)只需要21个字节，而varchar(500)只需要501个字节。所以总字节数是522，小于767。为什么我得到了错误消息?
#1071 - Specified key was too long; max key length is 767 bytes
当前回答
这解决了我的问题
ALTER DATABASE dbname CHARACTER SET utf8 COLLATE
其他回答
Specified key was too long; max key length is 767 bytes
You got that message because 1 byte equals 1 character only if you use the latin-1 character set. If you use utf8, each character will be considered 3 bytes when defining your key column. If you use utf8mb4, each character will be considered to be 4 bytes when defining your key column. Thus, you need to multiply your key field's character limit by, 1, 3, or 4 (in my example) to determine the number of bytes the key field is trying to allow. If you are using uft8mb4, you can only define 191 characters for a native, InnoDB, primary key field. Just don't breach 767 bytes.
在我的例子中，我在使用linux重定向输出/输入字符备份数据库时遇到了这个问题。因此，我按照下面的描述更改语法。PS:使用linux或mac终端。
备份(没有>重定向)
# mysqldump -u root -p databasename -r bkp.sql
恢复(不带< redirect)
# mysql -u root -p --default-character-set=utf8 databasename
mysql> SET names 'utf8'
mysql> SOURCE bkp.sql
错误“指定的键太长;最大密钥长度是767字节。
您使用的是什么字符编码?有些字符集(如UTF-16等等)每个字符使用一个以上的字节。
您可以添加长列的md5列
我对这个相同问题的修复是添加一个选项作为第三个参数:charset
queryInterface.createTable(
tableName,
{ /*... columns*/ },
{ charset: 'utf8' }
)
否则sequelize将创建utf8mb4表。
