mysql5.6建立索引报错1709问题及解决

三秦使者

现象描述

在给varchar字段建立索引时，报错如下：

1. [root@localhost:(test) 13:53:27]> CREATE INDEX b_name_IDX USING BTREE ON test.b(name);ERROR 1709 (HY000): Index column size too large. The maximum column size is 767 bytes.

复制代码

查看表结构：

1. CREATE TABLE `b` (
   
2.   `name` varchar(250) DEFAULT NULL,
   
3.   `standardized_name` varchar(250) DEFAULT NULL,
   
4.   `is_reagent` int(11) NOT NULL DEFAULT '0',
   
5.   `is_solvent` int(11) NOT NULL DEFAULT '0',
   
6.   `is_catalyst` int(11) NOT NULL DEFAULT '0',
   
7.   `is_ligand` int(11) NOT NULL DEFAULT '0',
   
8.   `to_delete` int(11) DEFAULT '0',
   
9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

复制代码

原因分析

索引字段的长度大于767，或者说，使用到的字段的长度和大于767则报错。
MySQL 5.6 中的

1. innodb_large_prefix

复制代码

默认是关闭的。
在MySQL中，

1. innodb_large_prefix

复制代码

参数是一个 InnoDB 存储引擎的配置选项。
这个参数控制是否允许使用超过767字节（或255个字符）的索引前缀。
默认情况下，在MySQL 5.6及以前版本中，InnoDB存储引擎对索引列的最大长度限制为767字节。
对于变长数据类型如VARCHAR，这个限制包括了字符集的每个字符可能占用的字节数，而不是仅仅指字符数。
例如，如果你使用的是UTF-8字符集，每个字符可能占用1到4个字节，所以一个VARCHAR(255)字段的实际最大长度可能会远小于255个字符。
当

1. innodb_large_prefix

复制代码

设置为

1. ON

复制代码

时，InnoDB 支持更大的索引前缀长度，最大可以达到3072字节。
这意味着你可以创建更长的索引，特别是对于包含大量变长数据类型的列。
这对于处理大数据表和需要更复杂查询的情况非常有用。
要启用

1. innodb_large_prefix

复制代码

，你可以在 MySQL 配置文件（如 my.cnf 或 my.ini）中添加以下行，并重启 MySQL 服务以应用更改：

1. [mysqld]
   
2. innodb_large_prefix = ON

复制代码

或者，你可以在运行时通过设置全局变量来开启它：

1. SET GLOBAL innodb_large_prefix = ON;

复制代码

请注意，为了使

1. innodb_large_prefix

复制代码

生效，还需要同时满足以下条件：

• 数据库文件格式必须是 Barracuda。
  
• 表格式必须是 DYNAMIC 或 COMPRESSED。
  
• 对于 ROW_FORMAT=COMPACT 的表，仍然有 767 字节的索引前缀限制。
  

有关这些条件的详细信息，请参阅 MySQL 文档。

问题处理

1. set global innodb_large_prefix=on;
   
2. show variables like 'innodb_large_prefix';
   
3. alter table b Row_format=dynamic;
   
4. set global innodb_file_format=BARRACUDA;

复制代码

再次加索引：

1. [root@localhost:(test) 13:54:18]> CREATE INDEX b_name_IDX USING BTREE ON test.b(name);
   
2. Query OK, 0 rows affected (0.06 sec)
   
3. Records: 0  Duplicates: 0  Warnings: 0

复制代码

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持脚本之家。

来源:https://www.jb51.net/database/318865flk.htm
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！