MySQL的InnoDB存储引擎的表存在一系列的限制条件,其中比较常见的一种是表的字段索引长度限制,该限制与参数innodb_large_prefix相关。
问题现象
执行如下两个建表SQL操作,都返回了报错信息,导致建表失败:
原因分析
导致上面报错的原因是由于InnoDB表的索引长度限制,在MySQL5.6版本后引入了参数innodb_large_prefix可以解决这个问题。该参数控制是否允许单列的索引长度超过767字节,有ON和OFF两个取值:
解决方案
案例复现
测试环境
MySQL内核版本:5.7 ROW_FORMAT = Dynamic | Compressed innodb_page_size= 16K innodb_file_mat = Barracuda
测试过程
建表语句innodb_large_prefix=OFFinnodb_large_prefix=ONCREATE TABLE tb_01(
id int(11) NOT NULL AUTO_INCREMENT,
column1 varchar(256) NOT NULL DEFAULT '',
PRIMARY KEY (id,column1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;建表失败
报错:Specified key was too long; max key length is 767 bytes建表成功CREATE TABLE tb_02(
id int(11) NOT NULL AUTO_INCREMENT,
column1 varchar(256) NOT NULL DEFAULT '',
PRIMARY KEY (id),
KEY idx01(column1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;建表成功
索引字段被截断为前缀索引:
KEY idx01 (column1(255))建表成功CREATE TABLE tb_03(
id int(11) NOT NULL AUTO_INCREMENT,
column1 varchar(256) NOT NULL DEFAULT '',
column2 varchar(256) NOT NULL DEFAULT '',
column3 varchar(256) NOT NULL DEFAULT '',
column4 varchar(256) NOT NULL DEFAULT '',
column5 varchar(256) NOT NULL DEFAULT '',
PRIMARY KEY (id,column1,column2,column3,column4,column5)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;建表失败
报错信息:Specified key was too long; max key length is 767 bytes建表失败
报错信息:Specified key was too long; max key length is 3072 bytesCREATE TABLE tb_04(
id int(11) NOT NULL AUTO_INCREMENT,
column1 varchar(256) NOT NULL DEFAULT '',
column2 varchar(256) NOT NULL DEFAULT '',
column3 varchar(256) NOT NULL DEFAULT '',
column4 varchar(256) NOT NULL DEFAULT '',
column5 varchar(256) NOT NULL DEFAULT '',
PRIMARY KEY (id),
KEY idx01(column1,column2,column3,column4,column5)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;建表失败
报错信息:Specified key was too long; max key length is 3072 bytes建表失败
报错信息:Specified key was too long; max key length is 3072 bytesCREATE TABLE tb_05(
id int(11) NOT NULL AUTO_INCREMENT,
column1 varchar(256) NOT NULL DEFAULT '',
column2 varchar(256) NOT NULL DEFAULT '',
column3 varchar(256) NOT NULL DEFAULT '',
column4 varchar(256) NOT NULL DEFAULT '',
PRIMARY KEY (id,column1,column2,column3,column4)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;建表失败
报错信息:Specified key was too long; max key length is 767 bytes建表失败
报错信息:Specified key was too long; max key length is 3072 bytesCREATE TABLE tb_06(
id int(11) NOT NULL AUTO_INCREMENT,
column1 varchar(256) NOT NULL DEFAULT '',
column2 varchar(256) NOT NULL DEFAULT '',
column3 varchar(256) NOT NULL DEFAULT '',
column4 varchar(256) NOT NULL DEFAULT '',
PRIMARY KEY (id),
KEY idx01(column1,column2,column3,column4)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;建表成功
索引字段被截断为前缀索引:
KEY idx01(,column1(255),column2(255),column3(255),column4(255))建表成功CREATE TABLE tb_07(
id int(11) NOT NULL AUTO_INCREMENT,
column1 varchar(255) NOT NULL DEFAULT '',
column2 varchar(255) NOT NULL DEFAULT '',
column3 varchar(255) NOT NULL DEFAULT '',
column4 varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (id,column1,column2,column3,column4)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;建表成功建表成功CREATE TABLE tb_08(
id int(11) NOT NULL AUTO_INCREMENT,
column1 varchar(255) NOT NULL DEFAULT '',
column2 varchar(255) NOT NULL DEFAULT '',
column3 varchar(255) NOT NULL DEFAULT '',
column4 varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (id),
KEY idx01(id,column1,column2,column3,column4)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;建表成功建表成功
说明
字段varchar(N)中的N是指字符的长度,不是字节数,需要结合字符集换算得出字节数。例如:utf8字符集一个字符占3个字节,若单列索引限制767,则最大可以支持N=255.
总结建议
总结
建议
本文地址:https://blog.csdn.net/w892824196/article/details/107658252
如对本文有疑问, 点击进行留言回复!!
MySQL-关系代数-并、交、差、等值连接、自然连接、左连接。。。
网友评论