执行以下命令时:
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
当前回答
对于这个问题，我自己的解决方案比降低表的VARCHAR大小更简单，也更安全。
情况:CentOS 7服务器运行Plesk Obsidian 18.0.37和MariaDB 5.5。我试图从运行MariaDB 10.1的服务器导入MySQL转储。
解决方案:从MariaDB 5.5升级到10.6。
这些步骤大致基于以下指南和以下指南:
mysqldump -u admin -p`cat /etc/psa/.psa.shadow` --all-databases --routines --triggers > /root/all-databases.sql systemctl stop mariadb cp -a /var/lib/mysql/ /var/lib/mysql_backup Configure MariaDB repositories according to the official guide Make sure you meet Plesk's minimum version requirements detailed here yum install MariaDB-client MariaDB-server MariaDB-compat MariaDB-shared systemctl start mariadb In my case, the server failed to start here with an error: "Can't start server: Bind on TCP/IP port. Got error: 22: Invalid argument". The fix was to replace bind-address as follows in /etc/my.cnf and re-run the command: [mysqld] # OLD (broken) #bind-address = ::ffff:127.0.0.1 # NEW bind-address = 127.0.0.1 MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql_upgrade -uadmin plesk sbin packagemng -sdf rm -f /etc/init.d/mysql systemctl daemon-reload
其他回答
我对这个相同问题的修复是添加一个选项作为第三个参数:charset
queryInterface.createTable(
tableName,
{ /*... columns*/ },
{ charset: 'utf8' }
)
否则sequelize将创建utf8mb4表。
5解决方法:
在5.7.7中提高了限制(MariaDB 10.2.2?)。并且可以通过5.6(10.1)中的一些工作来增加它。
如果你因为试图使用字符集utf8mb4而达到极限。然后做以下其中一种(每一种都有缺点)来避免错误:
⚈ Upgrade to 5.7.7 for 3072 byte limit -- your cloud may not provide this;
⚈ Change 255 to 191 on the VARCHAR -- you lose any values longer than 191 characters (unlikely?);
⚈ ALTER .. CONVERT TO utf8 -- you lose Emoji and some of Chinese;
⚈ Use a "prefix" index -- you lose some of the performance benefits.
⚈ Or... Stay with older version but perform 4 steps to raise the limit to 3072 bytes:
SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=1;
SET GLOBAL innodb_large_prefix=1;
logout & login (to get the global values);
ALTER TABLE tbl ROW_FORMAT=DYNAMIC; -- (or COMPRESSED)
——http://mysql.rjweb.org/doc.php/limits 767 _limit_in_innodb_indexes
这解决了我的问题
ALTER DATABASE dbname CHARACTER SET utf8 COLLATE
在我的例子中，我在使用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字节。
如果您最近修改了innodb_log_file_size，请尝试恢复之前的值。
