mysql - 在 mySQL 5.7.9 中删除相等的行？

Question

我在 mysql 中有一张名为 ts1 的表

+----------+-------------+---------------+
| position | email       | date_of_birth |
+----------+-------------+---------------+
|        3 | NULL        | 1987-09-03    |
|        1 | NULL        | 1982-03-26    |
|        2 | Sam@gmail   | 1976-10-03    |
|        2 | Sam@gmail   | 1976-10-03    |
+----------+-------------+---------------+

我想使用 ALTER IGNORE 删除相等的行。

我试过了

ALTER IGNORE TABLE ts1 ADD UNIQUE INDEX inx (position, email, date_of_birth); 

和

ALTER IGNORE TABLE ts1 ADD UNIQUE(position, email, date_of_birth); 

在这两种情况下我都得到了

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IGNORE TABLE ts1 ADD UNIQUE(position, email, date_of_birth)' at line 1

我正在使用 mySQL 5.7.9。有什么建议吗？

Answer 1

要根据表格内联执行此操作，请考虑以下内容，仅给出您显示的列。要按照 Strawberry 的建议在新表中执行此操作，请参阅评论下我的 pastie 链接。

create table thing
(   position int not null,
    email varchar(100) null,
    dob date not null
);
insert thing(position,email,dob) values
(3,null,'1987-09-03'),(1,null,'1982-03-26'),
(2,'SamIAm@gmail.com','1976-10-03'),(2,'SamIAm@gmail.com','1976-10-03');
select * from thing;
+----------+------------------+------------+
| position | email            | dob        |
+----------+------------------+------------+
|        3 | NULL             | 1987-09-03 |
|        1 | NULL             | 1982-03-26 |
|        2 | SamIAm@gmail.com | 1976-10-03 |
|        2 | SamIAm@gmail.com | 1976-10-03 |
+----------+------------------+------------+

alter table thing add id int auto_increment primary key;

使用连接模式删除，删除后续的重复项(具有更大的 ID 号)

delete thing
from thing
join
( select position,email,dob,min(id) as theMin,count(*) as theCount 
  from thing 
  group by position,email,dob 
  having theCount>1
) xxx -- alias
on thing.position=xxx.position and thing.email=xxx.email and thing.dob=xxx.dob and thing.id>xxx.theMin
-- 1 row affected

从事物中选择*；

+----------+------------------+------------+----+
| position | email            | dob        | id |
+----------+------------------+------------+----+
|        3 | NULL             | 1987-09-03 |  1 |
|        1 | NULL             | 1982-03-26 |  2 |
|        2 | SamIAm@gmail.com | 1976-10-03 |  3 |
+----------+------------------+------------+----+

添加唯一索引

CREATE UNIQUE INDEX `thing_my_composite` ON thing (position,email,dob); -- forbid dupes hereafter

查看当前表架构

show create table thing;

CREATE TABLE `thing` (
  `position` int(11) NOT NULL,
  `email` varchar(100) DEFAULT NULL,
  `dob` date NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
  UNIQUE KEY `thing_my_composite` (`position`,`email`,`dob`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;