mysql - 不使用 ID 删除 MySQL 5.7.12 中的重复项

Question

我需要将新数据 (TableB) 添加到旧表 (TableA)。然后删除重复项。我试过两个版本。一部作品。另一个没有。有效的方法要求我添加主键。可以跳过这个吗？

/* Append new data to old table */
insert into TableA select * from TableB;

/* First method does not work */
ALTER IGNORE TABLE TableA ADD UNIQUE (stock, date)

/* Second method work but requires ID. My actual table is very large with millions of rows and hundreds of columns. I will need to create index for fast queries. Hence it is better not to add un-necessary columns */

ALTER TABLE TableA
  ADD ID INT auto_increment primary key;

delete TableA
from TableA
join
( select stock, date, min(id) as theMin,count(*) as theCount 
  from TableA 
  group by stock, date 
  having theCount>1
) xxx 
on TableA.stock = xxx.stock and TableA.date = xxx.date and TableA.id>xxx.theMin

有什么想法吗？

非常感谢LL

Answer 1

这是添加主键的方法(无论如何，每个自重的表都应该有)。

create table fish
(   theName varchar(100) not null
);
insert fish(theName) values ('tuna'),('herring');

alter table fish add column `id` int primary KEY AUTO_INCREMENT;

show create table fish;
CREATE TABLE `fish` (
   `theName` varchar(100) NOT NULL,
   `id` int(11) NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

select * from fish;
+---------+----+
| theName | id |
+---------+----+
| tuna    |  1 |
| herring |  2 |
+---------+----+

示例 2:

create table t8
(   stock varchar(100) not null,
    `date` date not null,
    other int not null
);
insert t8(stock,`date`,other) values ('a','2008-01-01',1),('b','2008-01-01',1);

alter table t8 add primary key (stock,`date`);

show create table t8;
CREATE TABLE `t8` (
   `stock` varchar(100) NOT NULL,
   `date` date NOT NULL,
   `other` int(11) NOT NULL,
   PRIMARY KEY (`stock`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

然后对插入执行INSERT IGNORE 策略。请参阅下面的评论。