mysql索引小记

今天在做公司项目时有应用场景,分别是某个地址不能重复, 和两个地址不能重复.

聪明的我们一下就想到了唯一索引和联合唯一索引。

sql呢很简单

新增唯一索引:

ALTER TABLE 表名 ADD UNIQUE  KEY ( `字段名` );
或者
ALTER IGNORE TABLE 表名 ADD UNIQUE INDEX (字段名);

注:IGNORE的作用:表中未创建唯一索引前可能会存在重复数据，索引它的作用就是将重复的数据删除的只剩一条

指定索引名字:

ALTER TABLE 表名 ADD UNIQUE  KEY 索引名(`字段名`);
或者
ALTER IGNORE TABLE 表名 ADD UNIQUE INDEX 索引名(字段名);

注：如果创建的时候没有指定索引的名字，默认是字段的名字。如果索引的名字重复系统会在名字后面＋_2

删除唯一索引:

ALTER TABLE `表名` DROP INDEX `索引名`;

创建联合索引:

ALTER IGNORE TABLE 表名 ADD UNIQUE INDEX 索引名(字段名1,字段名2,字段名3...);
或者
ALTER TABLE 表名 ADD UNIQUE  KEY 索引名(字段名1,字段名2,字段名3...);

注:创建联合索引时如果不指定索引名默认是第一个字段名为索引名,如果重复也会默认＋_2;

 

如果还重复应该会_3 我没测~ 懒哈哈

另外在创建唯一索引时可能会出现创建失败的错误. 我这里记录一种希望能帮到人

原文链接

 系统变量innodb_large_prefix开启了，则对于使用DYNAMIC或COMPRESSED行格式的InnoDB表，索引键前缀限制为3072字节。如果禁用innodb_large_prefix，不管是什么表，索引键前缀限制为767字节。

       上述的bug很明显是索引超出了限制的长度767（我司生产上innodb_large_prefix禁用了）：

       我发现报错的那张表建立了一个varchar类型的索引，varchar(255)，觉得没什么问题，其实不然，上述的767是字节，而varchar类型是字符，同时我发现我使用的字符集为（utf8mb4），这个指每个字符最大的字节数为4，所以很明显 4*255 > 767

所以就报上述错了（Specified key was too long; max key length is 767 bytes）。

       解决方法：

       改变varchar的字符数，我改成了64就可以了。varchar(64)

       或者启用innodb_large_prefix，那么限制值会增加到3072