执行以下命令时:
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
当前回答
将抱怨索引字段的CHARSET更改为“latin1” 例如:ALTER TABLE tbl CHANGE myfield varchar(600) CHARACTER SET latin1 DEFAULT NULL Latin1为一个字符使用一个字节，而不是四个
其他回答
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.
我对这个相同问题的修复是添加一个选项作为第三个参数:charset
queryInterface.createTable(
tableName,
{ /*... columns*/ },
{ charset: 'utf8' }
)
否则sequelize将创建utf8mb4表。
这解决了我的问题
ALTER DATABASE dbname CHARACTER SET utf8 COLLATE
您使用的是什么字符编码?有些字符集(如UTF-16等等)每个字符使用一个以上的字节。
请检查sql_mode是否为like
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
如果是，则更改为
sql_mode=NO_ENGINE_SUBSTITUTION
OR
重新启动服务器，修改my.cnf文件(如下所示)
innodb_large_prefix=on
