MySQL错误#1071 - 指定的键太长；最大键长度为767字节。

Question

MySQL错误#1071 - 指定的键太长；最大键长度为767字节。

667

当我执行了以下命令：

ALTER TABLE `mytable` ADD UNIQUE (
`column1` ,
`column2`
);

我看到了这个错误信息：

#1071 - Specified key was too long; max key length is 767 bytes

列1和列2的信息：

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

- Steven

5

因为它不是520字节，而是2080字节，远远超过了767字节，所以你可以将列1设置为varchar（20），将列2设置为varchar（170）。如果您想要字符/字节等效，请使用latin1编码。 - Rahly

3

我认为你的计算有点错误。MySQL会使用额外的1或2个字节来记录值的长度：如果该列的最大长度小于或等于255字节，则使用1个字节，否则使用2个字节。utf8_general_ci编码每个字符需要3个字节，因此varchar(20)使用61个字节，varchar(500)总共使用1502个字节，加起来一共是1563个字节。 - lackovic10

3

mysql> 从information_schema.character_sets中选择maxlen和character_set_name，其中character_set_name为('latin1', 'utf8', 'utf8mb4')。

maxlen	character_set_name
1	latin1
------	-------------------
3	utf8
------	-------------------
4	utf8mb4

- lackovic10

26

如果你需要一个字符/字节的等价物，可以使用Latin1。但是请不要这样做。使用Latin1会带来很多问题，你会后悔的。 - Stijn de Witt

请参考以下链接获取有关该问题的解决方案：https://dev59.com/s1gQ5IYBdhLWcg3w6IEH#52778785 - Pratik

37个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Nids Barthwal · Answer 1

改变你的排序规则。你可以使用支持几乎所有字符的utf8_general_ci。

- MajidJafari · Answer 2

只需在创建表时将 utf8mb4 更改为 utf8 即可解决我的问题。例如：CREATE TABLE ... DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; 更改为 CREATE TABLE ... DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;。

- Shamal Sabah · Answer 3

这解决了我的问题。

修改数据库dbname的字符集为utf8，排序规则为utf8_general_ci：

- Devy · Answer 4

基于下面给出的列，这两个变量字符串列使用了utf8_general_ci校对集（utf8字符集暗示）。

在MySQL中，utf8字符集每个字符最大使用3字节。因此，它需要分配500*3=1500字节，这比MySQL允许的767字节要大得多。这就是为什么您会收到1071错误的原因。

换句话说，您需要根据字符集的字节表示来计算字符数，因为并非每个字符集都是单个字节表示（如您所假定的）。例如，在MySQL中，utf8最多使用3个字节表示每个字符，767/3≈255个字符，而utf8mb4最多使用4个字节表示每个字符，767/4≈191个字符。

另外，MySQL也已经知道了。

column1 varchar(20) utf8_general_ci
column2  varchar(500) utf8_general_ci

- Andrew · Answer 5

我发现这个查询对于检测哪些列违反了最大键长度的索引非常有用：

SELECT
  c.TABLE_NAME As TableName,
  c.COLUMN_NAME AS ColumnName,
  c.DATA_TYPE AS DataType,
  c.CHARACTER_MAXIMUM_LENGTH AS ColumnLength,
  s.INDEX_NAME AS IndexName
FROM information_schema.COLUMNS AS c
INNER JOIN information_schema.statistics AS s
  ON s.table_name = c.TABLE_NAME
 AND s.COLUMN_NAME = c.COLUMN_NAME 
WHERE c.TABLE_SCHEMA = DATABASE()
  AND c.CHARACTER_MAXIMUM_LENGTH > 191 
  AND c.DATA_TYPE IN ('char', 'varchar', 'text')

- Cassio Seffrin · Answer 6

在我的情况下，我在使用Linux重定向输出/输入字符备份数据库时遇到了这个问题。因此，我按照以下描述更改了语法。PS：使用Linux或Mac终端。

备份（不使用>重定向）

# mysqldump -u root -p databasename -r bkp.sql

还原（不带重定向）

# mysql -u root -p --default-character-set=utf8 databasename
mysql> SET names 'utf8'
mysql> SOURCE bkp.sql

错误信息“Specified key was too long; max key length is 767 bytes”已经消失。

- neel · Answer 7

请检查是否sql_mode类似于：

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

如果是，就更改为：

sql_mode=NO_ENGINE_SUBSTITUTION

或者

重新启动服务器并更改您的my.cnf文件（按以下方式进行）

innodb_large_prefix=on

- Rivers · Answer 8

由于前缀限制，将会出现此错误。在MySQL 5.7之前的版本中，InnoDB表的前缀限制为767字节。MyISAM表的长度为1,000字节。在MySQL 5.7及更高版本中，此限制已增加至3072字节。

在出现错误的服务上运行以下命令应该可以解决您的问题。这必须在MYSQL CLI中运行。

Original Answer翻译成"最初的回答"

SET GLOBAL innodb_file_format=Barracuda;
SET GLOBAL innodb_file_per_table=on;
SET GLOBAL innodb_large_prefix=on;

- HoldOffHunger · Answer 9

问题

MySQL中存在最大键长度限制。

InnoDB - 最大键长度为1,536字节（对于8kb页面大小）和768字节（对于4kb页面大小）（参考来源：Dev.MySQL.com）。
MyISAM - 最大键长度为1,000字节（参考来源：Dev.MySQL.com）。

这些长度是以字节为单位计算的！因此，一个UTF-8字符可能需要多个字节才能存储到键中。

因此，您只有两个直接的解决方案：

将文本类型的前n个字符索引化。创建一个“全文搜索”——在文本中，所有内容都可以被搜索，类似于ElasticSearch。

索引化文本类型的前N个字符

如果您正在创建一个表，请使用以下语法来索引某个字段的前255个字符：KEY sometextkey (SomeText(255))。像这样：

CREATE TABLE `MyTable` (
    `id` int(11) NOT NULL auto_increment,
    `SomeText` TEXT NOT NULL,
    PRIMARY KEY  (`id`),
    KEY `sometextkey` (`SomeText`(255))
);

如果您已经有了该表，则可以使用以下方法向字段添加唯一键：ADD UNIQUE(ConfigValue(20));。如下所示：

ALTER TABLE
MyTable
ADD UNIQUE(`ConfigValue`(20));

如果字段的名称不是 MySQL 关键字，则不需要在字段名周围使用反引号 (```)。

创建全文搜索

全文搜索将允许您搜索 TEXT 字段值的全部内容。如果使用 NATURAL LANGUAGE MODE，它将进行整个单词匹配；如果使用其他模式，则进行部分单词匹配。有关 FullText 选项的更多信息，请参见：Dev.MySQL.com

创建带有文本的表，并添加全文索引...

ALTER TABLE
        MyTable
ADD FULLTEXT INDEX
        `SomeTextKey` (`SomeTextField` DESC);

然后像这样搜索您的表格...

SELECT
        MyTable.id, MyTable.Title,
MATCH
        (MyTable.Text)
AGAINST
        ('foobar' IN NATURAL LANGUAGE MODE) AS score
FROM
        MyTable
HAVING
        score > 0
ORDER BY
        score DESC;

- rkok · Answer 10

我的解决方案比降低表的VARCHAR大小更简单、更安全。

情况：运行Plesk Obsidian 18.0.37的CentOS 7服务器，使用MariaDB 5.5。我试图从运行MariaDB 10.1的服务器导入MySQL转储文件。

解决方案：升级MariaDB 5.5到10.6。

步骤大致基于this guide和this one：

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