1.表结构
CREATE TABLE `employ` (
`id` varchar(255) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`companyid` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `test` (`name`,`companyid`) USING HASH
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2.添加索引
alter table employ add unique (name);
测试添加name一样的数据
[SQL] insert into employ (id,name) values ("1","knife"),("2","knife")
[Err] 1062 - Duplicate entry 'knife' for key 'name'
利用ignore关键字可以避免报错
[SQL] insert ignore into employ (id,name) values ("1","knife"),("2","knife")
受影响的行: 1
时间: 0.053s
3.多个字段做索引
alter table employ add unique (name,companyid);
测试name一样,companyid不一样
[SQL] insert employ (id,name,companyid) values ("2","knife","1"),("3","knife","2")
受影响的行: 2
时间: 0.056s
测试name一样,companyid也一样
[SQL] insert employ (id,name,companyid) values ("4","knife","5"),("5","knife","5")
[Err] 1062 - Duplicate entry 'knife-5' for key 'name_2'
同样可以利用ignore关键字避免报错
[SQL] insert ignore employ (id,name,companyid) values ("4","knife","5"),("5","knife","5")
受影响的行: 1
时间: 0.012s