2019年 · MySQL

修改Mysql索引长度限制-ERROR 1709 (HY000) at line 162: Index column size too large. The maximum column size is 767 bytes.

系统环境:
root@host:~/software/zabbix-3.0.27/database/mysql# lsb_release -a
LSB Version: core-2.0-amd64:core-2.0-noarch:core-3.0-amd64:core-3.0-noarch:core-3.1-amd64:core-3.1-noarch:core-3.2-amd64:core-3.2-noarch:core-4.0-amd64:core-4.0-noarch:core-4.1-amd64:core-4.1-noarch:security-4.0-amd64:security-4.0-noarch:security-4.1-amd64:security-4.1-noarch
Distributor ID: Ubuntu
Description: Ubuntu 14.04.4 LTS
Release: 14.04
Codename: trusty

MySQL 环境配置:
Server version: mysqld Ver 5.6.27 for Linux on x86_64 (Source distribution)
MySQL [(none)]> show variables like ‘%character%’\G
*************************** 1. row ***************************
Variable_name: character_set_client
Value: utf8mb4
*************************** 2. row ***************************
Variable_name: character_set_connection
Value: utf8mb4
*************************** 3. row ***************************
Variable_name: character_set_database
Value: utf8mb4
*************************** 4. row ***************************
Variable_name: character_set_filesystem
Value: binary
*************************** 5. row ***************************
Variable_name: character_set_results
Value: utf8mb4
*************************** 6. row ***************************
Variable_name: character_set_server
Value: utf8mb4
*************************** 7. row ***************************
Variable_name: character_set_system
Value: utf8
*************************** 8. row ***************************
Variable_name: character_sets_dir
Value: /usr/local/mysql/share/charsets/
8 rows in set (0.00 sec)

出现问题:
root@host:~/software/zabbix-3.0.27/database/mysql# mysql -uroot -p zabbix30 < schema.sql
Enter password:
ERROR 1709 (HY000) at line 162: Index column size too large. The maximum column size is 767 bytes.
出现问题语句如下:vim schema.sql +162
CREATE TABLE `slideshows` (
`slideshowid` bigint unsigned NOT NULL,
`name` varchar(255) DEFAULT ” NOT NULL,
`delay` integer DEFAULT ‘0’ NOT NULL,
`userid` bigint unsigned NOT NULL,
`private` integer DEFAULT ‘1’ NOT NULL,
PRIMARY KEY (slideshowid)
) ENGINE=InnoDB;
CREATE UNIQUE INDEX `slideshows_1` ON `slideshows` (`name`);

解决办法:
(1)查看相关配置并作出如下设置
innodb_large_prefix = ON
innodb_file_format = Barracuda
innodb_file_per_table = ON
或者
MySQL [(none)]> show variables like ‘%innodb_large_prefix%’;
+———————+——-+
| Variable_name          |   Value |
+———————+——-+
| innodb_large_prefix | OFF    |
+———————+——-+
1 row in set (0.00 sec)
MySQL [(none)]> set global innodb_large_prefix=on;
Query OK, 0 rows affected (0.00 sec)
MySQL [(none)]> show variables like ‘%innodb_file_format%’;
+————————–+———–+
| Variable_name | Value |
+————————–+———–+
| innodb_file_format           |Antelope     |
| innodb_file_format_check |      ON      |
| innodb_file_format_max | Barracuda |
+————————–+———–+
3 rows in set (0.01 sec)
MySQL [(none)]> set global innodb_file_format=Barracuda;
Query OK, 0 rows affected (0.00 sec)
(2)修改建表语句,加入row_format=dynamic
CREATE TABLE `slideshows` (
`slideshowid` bigint unsigned NOT NULL,
`name` varchar(255) DEFAULT ” NOT NULL,
`delay` integer DEFAULT ‘0’ NOT NULL,
`userid` bigint unsigned NOT NULL,
`private` integer DEFAULT ‘1’ NOT NULL,
PRIMARY KEY (slideshowid)
) ENGINE=InnoDB row_format=dynamic;
CREATE UNIQUE INDEX `slideshows_1` ON `slideshows` (`name`);
(3)修改字段长度, varchar(255) 改为 varchar(191)
CREATE TABLE `slideshows` (
`slideshowid` bigint unsigned NOT NULL,
`name` varchar(191) DEFAULT ” NOT NULL,
`delay` integer DEFAULT ‘0’ NOT NULL,
`userid` bigint unsigned NOT NULL,
`private` integer DEFAULT ‘1’ NOT NULL,
PRIMARY KEY (slideshowid)
) ENGINE=InnoDB;
CREATE UNIQUE INDEX `slideshows_1` ON `slideshows` (`name`);

原因:
MySQL 索引只支持767个字节,utf8mb4 每个字符占用4个字节,所以索引最大长度只能为191个字符,即varchar(191),若想要使用更大的字段,mysql需要设置成支持数据压缩,并且修改表属性 row_format ={DYNAMIC|COMPRESSED}

参考:https://blog.51cto.com/13120271/2315189
https://www.cnblogs.com/kerrycode/p/9680881.html
https://dev.mysql.com/doc/refman/5.6/en/innodb-restrictions.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html