mysql 创建索引失败_Mysql5.7的varchar类型字段建索引竟然失败

在Mysql数据库日常运维过程中，在表上创建索引是很常见的事情，可是一直没有思考过，单列索引到底能有多长呢。如果varchar类型的字段过长，创建单列索引会不会失败呢，下面就来一起探索一下吧。

查阅官方文档，发现在MySQL5.6 版本后引入了参数 innodb_large_prefix，开启情况下，Innodb表的行记录格式是Dynamic或Compressed的前提下，能让单列索引的长度达到3072个字节长度，如果不开启这个参数，那么单列索引的长度就不能超过767个字节长度。

测试环境介绍测试环境的是MySQL5.7版本，默认字符集是utf8。

查看innodb_large_prefix参数innodb_large_prefix参数在mysql5.7版本，默认是开启的。mysql> show variables like 'innodb_large%';

+---------------------+-------+

| Variable_name | Value |

+---------------------+-------+

| innodb_large_prefix | ON |

+---------------------+-------+

1 row in set (0.02 sec)

查看测试表的默认记录格式在mysql5.7版本中，Innodb表的行记录格式为Dynamic的。mysql> show variables like 'innodb_default_row_format';

+---------------------------+---------+

| Variable_name | Value |

+---------------------------+---------+

| innodb_default_row_format | dynamic |

+---------------------------+---------+

1 row in set (0.01 sec)

模拟测试创建测试表和索引mysql> create table t_test2 (id int not null,name varchar(1024) not null default '',primary key(id));

Query OK, 0 rows affected (0.02 sec)

mysql> alter table t_test2 add index idx_t_test2_name(name);

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> create table t_test3 (id int not null,name varchar(1025) not null default '',primary key(id));

Query OK, 0 rows affected (0.03 sec)

mysql> alter table t_test3 add index idx_t_test3_name(name);

ERROR 1071 (42000): Specified key was too long; max key length is 3072 bytes从测试结果上可以看出，当varchar的字段长度超过1024之后，创建单列索引就会报错，为什么呢，因为数据库的字符集是utf8，一个字符长度占用3个字节，那么单列索引的长度是3072，转换成字符 3072 3=1024，这样建表时，如果字段长度超过1024，创建索引就会失败。

关闭innodb_large_prefix参数测试

关闭innodb_large_prefix参数mysql> set global innodb_large_prefix=off;

Query OK, 0 rows affected, 1 warning (0.11 sec)

mysql> show variables like 'innodb_large_prefix';

+---------------------+-------+

| Variable_name | Value |

+---------------------+-------+

| innodb_large_prefix | OFF |

+---------------------+-------+

1 row in set (0.00 sec)

mysql> create table t_test4 (id int not null,name varchar(255) not null default '',primary key(id));

Query OK, 0 rows affected (0.02 sec)

mysql> alter table t_test4 add index idx_t_test4_name(name);

Query OK, 0 rows affected (0.35 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> create table t_test5 (id int not null,name varchar(256) not null default '',primary key(id));

Query OK, 0 rows affected (0.01 sec)

mysql> alter table t_test5 add index idx_t_test5_name(name);

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

可以看到此时，字段长度超过255，创建单列索引就会报错。

可是在生产，谁会在长度超过1024的列上创建索引呢，这不是有病么，所以建议将innodb_large_prefix参数关闭，避免在长字段上创建索引。