MySQL 如何防止我的表中的重复记录插入忽略在这里不起作用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14028093/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to prevent Duplicate records from my table Insert ignore does not work here
提问by Chella
mysql> select * from emp;
+-----+---------+------+------+------+
| eno | ename | dno | mgr | sal |
+-----+---------+------+------+------+
| 1 | rama | 1 | NULL | 2000 |
| 2 | kri | 1 | 1 | 3000 |
| 4 | kri | 1 | 2 | 3000 |
| 5 | bu | 1 | 2 | 2000 |
| 6 | bu | 1 | 1 | 2500 |
| 7 | raa | 2 | NULL | 2500 |
| 8 | rrr | 2 | 7 | 2500 |
| 9 | sita | 2 | 7 | 1500 |
| 10 | dlksdgj | 2 | 2 | 2000 |
| 11 | dlksdgj | 2 | 2 | 2000 |
| 12 | dlksdgj | 2 | 2 | 2000 |
| 13 | dlksdgj | 2 | 2 | 2000 |
| 14 | dlksdgj | 2 | 2 | 2000 |
+-----+---------+------+------+------+
Here is my table. I want to eliminateor preventinsertion of the duplicate records, as the enofield is auto incrementtotal row never be duplicate, but the records are duplicates. How can I prevent inserting those duplicate records?
这是我的桌子。我想消除或防止插入重复记录，因为该eno字段是auto increment总行永远不会重复，但记录是重复的。如何防止插入那些重复的记录？
I tried using INSERT IGNORE AND ON DUPLICATE KEY UPDATE(I think I have not used them properly).
我尝试使用INSERT IGNORE AND ON DUPLICATE KEY UPDATE（我想我没有正确使用它们）。
The way I used them is,
我使用它们的方式是，
mysql> insert into emp(ename,dno,mgr,sal) values('dlksdgj',2,2,2000);
Query OK, 1 row affected (0.03 sec)
mysql> insert ignore into emp(ename,dno,mgr,sal) values('dlksdgj',2,2,2000);
Query OK, 1 row affected (0.03 sec)
mysql> insert into emp(ename,dno,mgr,sal) values('dlksdgj',2,2,2000) ON DUPLICATE KEY UPDATE eno=eno;
Query OK, 1 row affected (0.03 sec)
mysql> insert into emp(ename,dno,mgr,sal) values('dlksdgj',2,2,2000) ON DUPLICATE KEY UPDATE eno=eno;
Query OK, 1 row affected (0.04 sec
mysql> desc emp;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| eno | int(11) | NO | PRI | NULL | auto_increment |
| ename | varchar(50) | YES | | NULL | |
| dno | int(11) | YES | | NULL | |
| mgr | int(11) | YES | MUL | NULL | |
| sal | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
回答by John Woo
alter the table by adding UNIQUEconstraint
通过添加UNIQUE约束来改变表
ALTER TABLE employee ADD CONSTRAINT emp_unique UNIQUE (ename,dno,mgr,sal)
but you can do this if the table employeeis empty.
但是如果表employee是空的，你可以这样做。
or if records existed, try adding IGNORE
或者如果记录存在，请尝试添加 IGNORE
ALTER IGNORE TABLE employee ADD CONSTRAINT emp_unique UNIQUE (ename,dno,mgr,sal)
UPDATE 1
更新 1
Something went wrong, I guess. You only need to add unique constraint on column enamesince enowill always be unique due to AUTO_INCREMENT.
我猜出了点问题。您只需要在列上添加唯一约束，ename因为eno由于AUTO_INCREMENT.
In order to add unique constraint, you need to do some cleanups on your table.
为了添加唯一约束，您需要对表进行一些清理。
The queries below delete some duplicate records, and alters table by adding unique constraint on column ename.
下面的查询删除一些重复的记录，并通过在 column 上添加唯一约束来更改表ename。
DELETE a
FROM Employee a
LEFT JOIN
(
SELECT ename, MIN(eno) minEno
FROM Employee
GROUP BY ename
) b ON a.eno = b.minEno
WHERE b.minEno IS NULL;
ALTER TABLE employee ADD CONSTRAINT emp_unique UNIQUE (ename);
Here's a full demonstration
这是一个完整的演示
回答by Sashi Kant
Create a UNIQUE CONSTRAINTon which you think the duplicacy exist .
创建一个UNIQUE CONSTRAINT您认为存在重复的 。
like
喜欢
ALTER TABLE MYTABLE ADD CONSTRAINT constraint1 UNIQUE(column1, column2, column3)
回答by Unix One
This will work regardless of whether you clean up your table first (i.e. you can stop inserting duplicates immediately and clean up on separate schedule) and without having to add any unique constraints or altering table in any other way:
无论您是否先清理表（即您可以立即停止插入重复项并按单独的计划清理），并且无需添加任何唯一约束或以任何其他方式更改表，这都将起作用：
INSERT INTO
emp (ename, dno, mgr, sal)
SELECT
e.ename, 2, 2, 2000
FROM
(SELECT 'dlksdgj' AS ename) e
LEFT JOIN emp ON e.ename = emp.ename
WHERE
emp.ename IS NULL
The above query assumes you want to use enameas a "unique" field, but in the same way you could define any other fields or their combinations as unique for the purposes of this INSERT.
上面的查询假设您要ename用作“唯一”字段，但同样，您可以为此目的将任何其他字段或其组合定义为唯一字段INSERT。
It works because it's an INSERT ... SELECTformat where the SELECTpart only produces a row (i.e. something to insert) if its left joined empdoes not already have that value. Naturally, if you wanted to change which field(s) defined this "uniqueness" you would modify the SELECTand the LEFT JOINaccordingly.
它之所以有效，是因为它是一种INSERT ... SELECT格式，SELECT如果它的左连接emp不具有该值，则该部分仅生成一行（即要插入的内容）。自然地，如果您想更改定义此“唯一性”的字段，您将相应地修改SELECT和LEFT JOIN。
