如何为MySQL中包含记录的现有表创建唯一索引
Here i like to explain my problem,
在这里,我想解释一下我的问题,
I Need to Create Unique Index in my Existing table, and the table contains many records.
我需要在现有表中创建唯一索引,该表包含许多记录。
I tried to execute this code
我试着执行这段代码
CREATE UNIQUE INDEX empid_name ON employee (importcompany_id, employee_id, name, relationship);
but am getting error as
但我得到的错误是
#1062 - Duplicata du champ '0-Emp ID-Member Name-Relationship' pour la clef 'empid_name'
Help me to sort out this problem, i need to make fields unique
帮我解决这个问题,我需要让字段独一无二
Updated :
The reason for setting these fields unique is
设置这些字段唯一的原因是
Actually i have a table like this
其实我有一张这样的桌子
id company_ID Employee_ID Name Relationship Dob Age Gender
1 EMPL 00001 Choodamani Spouse 11-Aug-66 49 Female
2 EMPL 00001 Komala Mother 30-Oct-39 76 Female
3 EMPL 00001 Varshini Daughter 29-Apr-04 11 Female
4 EMPL 00001 Vasudevan Employee 15-Jul-62 53 Male
5 EMPL 00002 Siddharth Son 1-Jun-00 15 Male
6 EMPL 00002 Poongavanam Mother 21-Oct-39 76 Female
7 EMPL 00002 Aruna Spouse 16-Sep-68 47 Female
8 EMPL 00002 Abirami Daughter 7-May-97 18 Female
9 EMPL 00002 Murali Employee 7-Oct-67 48 Male
if have insert a data like this,
如果要插入这样的数据,
id company_ID Employee_ID Name Relationship Dob Age Gender
1 EMPL 00001 Choodamani Spouse 11-Aug-70 45 Female
2 EMPL 00001 Nirmal Son 30-Oct-39 76 Female
this insert or update is done through import using excel sheet
此插入或更新是通过使用Excel工作表导入完成的
3 个解决方案
#1
9
- If you want to have unique index
empid_name
ON employee table with columns(importcompany_id, employee_id, name, relationship)
. Then you must delete existing duplicate data.
如果要在列员工表上使用唯一索引empid_name ON(importcompany_id,employee_id,name,relationship)。然后,您必须删除现有的重复数据。
Easy way to do this is to add a UNIQUE index on the 4 columns. When you write the ALTER statement, include the IGNORE
keyword. Like so:
简单的方法是在4列上添加UNIQUE索引。编写ALTER语句时,请包含IGNORE关键字。像这样:
ALTER IGNORE TABLE `employee` ADD UNIQUE INDEX(importcompany_id, employee_id, name, relationship);
This will drop all the duplicate rows. As an added benefit, future INSERTs that are duplicates will error out. As always, you may want to take a backup before running something like this.
这将删除所有重复的行。作为一个额外的好处,未来的重复INSERT将会出错。与往常一样,您可能希望在运行此类内容之前进行备份。
- Or Add primary key in your table then you can easily remove duplicates from your table. And then add unique index.
或者在表格中添加主键,然后您可以轻松地从表格中删除重复项。然后添加唯一索引。
更多相关文章
- 使用AJAX值的Fileupload不会以其他字段的形式传递
- Mysql 批量修改字段存储过程
- mysql中select列表可以有group列表中没有的字段,大家不觉得奇怪吗
- MySQL索引之B+树
- 如何将空值传递给外键字段?
- MYSQL SET类型字段的SQL查询某个字段保函某个值的查询
- mysql 排序两个字段/列表先根据时间升序排序,时间相同再根据商家I
- 转!mysql 查询 distinct多个字段 注意!!
- 基础概念---mysql 列转行,合并字段