如何防止我的表中的重复记录插入忽略在此处不起作用

2022-01-09 00:00:00 sql insert mysql 
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 |
    +-----+---------+------+------+------+

这是我的桌子.我想 eliminate 或 prevent 插入重复记录，因为 eno 字段是 auto increment 总行从不是重复的，但记录是重复的.如何防止插入那些重复的记录?

Here is my table. I want to eliminate or prevent insertion of the duplicate records, as the eno field is auto increment total row never be duplicate, but the records are duplicates. How can I prevent inserting those duplicate records?

我尝试使用 INSERT IGNORE AND ON DUPLICATE KEY UPDATE(我认为我没有正确使用它们).

我使用它们的方式是，

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    |                |
+-------+-------------+------+-----+---------+----------------+

推荐答案

通过添加UNIQUE约束来改变表

ALTER TABLE employee ADD CONSTRAINT emp_unique UNIQUE (ename,dno,mgr,sal)

但如果表 employee 为空，您可以这样做.

but you can do this if the table employee is empty.

如果存在记录，请尝试添加 IGNORE

or if records existed, try adding IGNORE

ALTER IGNORE TABLE employee ADD CONSTRAINT emp_unique UNIQUE (ename,dno,mgr,sal)

更新 1

我猜是出了点问题.您只需在 ename 列上添加唯一约束，因为 eno 由于 AUTO_INCREMENT 将始终是唯一的.

Something went wrong, I guess. You only need to add unique constraint on column ename since eno will always be unique due to AUTO_INCREMENT.

为了添加唯一约束，您需要对表进行一些清理.

In order to add unique constraint, you need to do some cleanups on your table.

下面的查询删除了一些重复的记录，并通过在列 ename 上添加唯一约束来更改表.

The queries below delete some duplicate records, and alters table by adding unique constraint on 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);

这是一个完整的演示

  • SQLFiddle 演示

相关文章