mysql 利用索引避免重复添加数据

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