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参数关闭,避免在长字段上创建索引。

上一篇:自动安装lnmp


下一篇:Laravel 7 迁移设置自动递增初始值